[SOLVED] SQL Error: ORA-00997: illegal use of LONG datatype

ISSUE

SQL Error: ORA-00997: illegal use of LONG datatype

SOLUTION

Consider I have a table with values as below and need to copy the values to another table.

create table test (name varchar2(30), id long); insert into test values('ranjith', 24); insert into test values('sekar', 25);

I was facing this error when I was trying to copy data from one table to another like below.

create table test_temp as select * from test;

Got the error

SQL Error: ORA-00997: illegal use of LONG datatype

00997. 00000 -  "illegal use of LONG datatype"

Follow below steps to resolve this error.

STEP 1

Create another temp table with CLOB column as below.

create table test_temp (name varchar2(30), id clob);

STEP 2

Copy the values to temp table from actual table.

insert into test_temp select name, to_lob(id) from test;

STEP 3

Now, truncate the actual table since we have copied all the data into temp table safely. Please double check if required.

truncate table test;

STEP 4

Copy the values back to the actual table from temp table.

insert into test select * from test_temp;

STEP 5

Drop the temp table.

drop table test_temp;