sql - Workaround for ORA-00997: illegal use of LONG datatype

Question

I want to save some data from a system table user_tab_cols, to a temp table so I can take a dump from it.

There are 100,000 rows in it , I have select from user_tab_cols about 1,000 records and d save them into a temp table with this query:

create table temp table as 
select * from user_tab_cols where condition...

I had error 'illegal use of longtype' , because of the column DATA_DEFAULT that contain a type of long.

Is there an alterantive way where I can store a long type into another table?

Answer 1

ORA-00997: illegal use of LONG datatype

It is a restriction on usage of LONG data type. You cannot create an object type with a LONG attribute.

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


SQL>

Alternatively, you could use TO_LOB as a workaround. Which would convert it into CLOB data type.

For example,

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

Table created.

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

SQL>

See more examples of workarounds here.