Workaround for ORA-00997: illegal use of LONG datatype

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 datatypeSQL>

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                                       CLOBSQL>

See more examples of workarounds here.

You'll need to create your target table explicitly, not from select *:

create table demo_copy( table_name varchar2(30), column_name varchar2(30), data_type varchar2(106), data_type_mod varchar2(3), data_type_owner varchar2(30), data_length number, data_precision number, data_scale number, nullable varchar2(1), column_id number, default_length number, data_default clob, num_distinct number, low_value raw(32), high_value raw(32), density number, num_nulls number, num_buckets number, last_analyzed date, sample_size number, character_set_name varchar2(44), char_col_decl_length number, global_stats varchar2(3), user_stats varchar2(3), avg_col_len number, char_length number, char_used varchar2(1), v80_fmt_image varchar2(3), data_upgraded varchar2(3), hidden_column varchar2(3), virtual_column varchar2(3), segment_column_id number, internal_column_id number, histogram varchar2(15), qualified_col_name varchar2(4000) );

(I've made data_default a clob for more convenient querying.)

Then you can insert rows in a PL/SQL loop:

begin    for r in (        select * from user_tab_cols c        where  rownum <= 2  -- your filter condition here    )    loop        insert into demo_copy values r;    end loop;end;

There are some limitations in principle with this approach, as a long column can hold more than the varchar2(32760) that PL/SQL will use in the loop. However, I expect 32K will be enough for most column default expressions.