SQL LONG

What is the SQL LONG data type?

Oracle proprietary data type that stores variable-length character data up to 2 gigabytes.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL LONG: Oracle (fully supported) MySQL: not supported as a standalone type; use LONGTEXT instead PostgreSQL: not supported; use TEXT SQL Server: not supported; use VARCHAR(MAX) or NTEXT (deprecated) SQLite: not supported; use TEXT

SQL LONG Full Explanation

LONG is an Oracle-specific legacy data type used to hold very large character strings (up to 2,147,483,647 bytes). It predates the ANSI SQL LOB types and Oracle’s own CLOB/BLOB family. Because LONG was introduced when hardware and SQL capabilities were limited, it comes with severe restrictions: only one LONG column is allowed per table, the column cannot be indexed, and it cannot appear in WHERE, ORDER BY, GROUP BY, JOIN, or subquery predicates. LONG values can be selected and inserted, but functions (e.g., SUBSTR) and operators generally do not work. Oracle added CLOB and NCLOB in later versions to overcome these limitations, and Oracle recommends migrating any remaining LONG columns to CLOB. Despite its age, LONG still exists for backward compatibility, so engineers maintaining legacy schemas must understand its behavior and pitfalls.

SQL LONG Syntax

-- define a LONG column
datatype ::= LONG

-- create table example
CREATE TABLE legacy_docs (
  id NUMBER PRIMARY KEY,
  content LONG
);

SQL LONG Parameters

Example Queries Using SQL LONG

-- 1. Create a table with a LONG column
CREATE TABLE legacy_docs (
  id NUMBER PRIMARY KEY,
  content LONG
);

-- 2. Insert a large text value (up to 2 GB)
INSERT INTO legacy_docs (id, content)
VALUES (1, 'Very long document text …');

-- 3. Retrieve the LONG data
SELECT id, content
FROM legacy_docs
WHERE id = 1;

-- 4. Migrate LONG to CLOB in place
ALTER TABLE legacy_docs ADD (content_clob CLOB);
UPDATE legacy_docs SET content_clob = TO_CLOB(content);
ALTER TABLE legacy_docs DROP COLUMN content;
ALTER TABLE legacy_docs RENAME COLUMN content_clob TO content;

Expected Output Using SQL LONG

Oracle returns "Table created."
Oracle returns "1 row created."
Query returns the stored document text as a single large string.
After the migration block, the table contains a CLOB column with the same data and no LONG column.

Use Cases with SQL LONG

Maintaining or querying legacy Oracle schemas that still expose LONG columns.
Reading historical audit tables or application logs designed before LOB support.
One-off data migrations that copy LONG data into modern LOB types (CLOB or BLOB).

Common Mistakes with SQL LONG

Adding more than one LONG column to a table (Oracle raises ORA-00957).
Attempting to index a LONG column.
Using LONG in predicates such as WHERE or ORDER BY.
Assuming LONG behaves like CLOB for string functions (most do not work).
Expecting cross-database tools (ODBC, ETL, JDBC drivers) to handle LONG seamlessly—many truncate or error.

First Introduced In

Oracle Database 7

Frequently Asked Questions

What is the maximum size of a LONG column?

A single LONG column can hold up to 2,147,483,647 bytes (about 2 GB) of character data.

Can I convert LONG to CLOB without data loss?

Yes. Use ALTER TABLE to add a CLOB column, copy the data with TO_CLOB, then drop the LONG column.

Why does Oracle forbid LONG in WHERE clauses?

Internally, LONG was implemented as an unstructured large object with stream semantics, making predicate evaluation impractical. Modern LOBs fix this.

Are LONG and LONG RAW the same?

No. LONG stores character data, while LONG RAW stores binary data. Both share the same restrictions.