Reasons and Workarounds to Fix ORA-00997: illegal use of LONG datatype

advertisements

_____________________________________________________________________________________________________________________

Error Description:

CREATE TABLE AS SELECT (CTAS) and Insert into command fails with ORA-00997: illegal use of LONG datatype

SQL>   insert into test  (select note from siebel.EIM_SR_DTL where rownum<10);

  insert into test  (select note from siebel.EIM_SR_DTL where rownum<10)

                            *

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

SQL> create table test1 as select * from siebel.EIM_SR_DTL where rownum<10;

create table test1 as select * from siebel.EIM_SR_DTL where rownum<10

                             *

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

Solution Description:

The tables with LONG data type cannot be moved or copied from one tablespace to another tablespace. You cannot simply copy the table to another table name using CTAS. Even select and Insert also will fail. There are two options to perform this move or copy activity.

1. Use export and import method: export the table into dump file and import into new name or tablespace
2. USE SQL*Plus copy command. Visit SQL*Plus Copy for more details and examples on COPY command.

_____________________________________________________________________________________________________________________

Website Stats