ORA-00997: illegal use of LONG datatype

ORA-00997: illegal use of LONG datatype

Cause: A value of datatype LONG was used in a function or in a DISTINCT, WHERE, CONNECT BY, GROUP BY, or ORDER BY clause. A LONG value can only be used in a SELECT clause.

Action: Remove the LONG value from the function or clause

ORA-00997: "illegal use of LONG datatype":

When trying to reorganize a database table that includes a LONG column, there is likely to be an Oracle error message such as:

ORA-00997: illegal use of LONG datatype

This is because it is not possible to use a LONG column in any SQL statement. It can only be used where the application is retrieving part of the column - by specifying the start byte and the number of bytes.

The following will cause the above error:

ALTER TABLE tablename MOVE TABLESPACE tablespacename;

or

CREATE TABLE newtablename AS SELECT * FROM oldtablename;