LONG Datatype and its restriction in Oracle

•Before going into detail oracle strongly recommend not to use LONG datatype in oracle. LONG datatype is remained for backward compatibility. If you have LONG datatype in your database then convert it to LOB data type using TO_LOB function which is discussed on How to Convert LOB .

•LONG datatype store variable-length character strings containing up to 2 gigabytes -1, or power(2,31)-1 bytes.

The use of LONG datatype is subject to the following restriction.

•A table can contain only one LONG column.

•You cannot create an object type with a LONG attribute.

•LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).

•Index can't be created on LONG columns.

•In regular expressions LONG datatype can't be specified.

•Stored function can't return a LONG value.

•You can declare a variable or argument of a PL/SQL program unit using the LONG datatype. However, you cannot then call the program unit from SQL.

•LONG and LONG RAW columns can't be replicated.

•All LONG columns, updated tables, and locked tables must be located on the same database within an SQL statement.

•LONG column can't appear in GROUP BY, ORDER BY clause, UNIQUE / DISTINCT operator or CONNECT BY clause in SELECT statements.

•LONG columns cannot appear in these parts of SQL statements
ALTER TABLE ... MOVE statement.
SELECT lists in subqueries in INSERT statements
SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators
SQL built-in functions, expressions, or conditions

Example:
----------------------
Create table with_long (long_dt LONG);
Table created.

SQL> insert into with_long values('This is a long datatype');
1 row created.

SQL> select * from with_long;
LONG_DT
--------------------------------------------------------------------------------
This is a long datatype