ORA-00997: illegal use of LONG datatype

I got ” ORA-00997: illegal use of LONG datatype ” error in Oracle database during create table as select * from ( CTAS )

 

ORA-00997: illegal use of LONG datatype

 

Details of error are as follows.

SQL> CREATE TABLE test_table AS SELECT data_default FROM user_tab_cols;
CREATE TABLE test_table  AS SELECT data_default FROM user_tab_cols
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype


SQL>

 

 

illegal use of LONG datatype

This ORA-00997: errors are related with the LOB Objects conversion.

 

To solve this error, you can use the TO_LOB function to convert long data to LOB as follows.

 

SQL> CREATE TABLE test_table AS SELECT TO_LOB(data_default) data_default FROM user_tab_cols;

Table created.

SQL> desc test_table;
Name Null? Type
----------------------------------------- -------- ----------------------------
DATA_DEFAULT CLOB

SQL>

 

Or you can use expdp impdp of related table if you want to move table.

Or you can remove the LONG value from the function or clause in a DISTINCT, WHERE, CONNECT BY, GROUP BY, or ORDER BY clause.

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )