Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1129
  • Last Modified:

ORA-00997 without use copy command

I've this problem:

create table t1 ( x long );
Table created.
create table t2 ( x long );
Table created.
insert into t1 values ( 'test long type' );
1 row created.
insert into t2 select * from t1;
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

How can I issue this error, I need use also dblink with long type.

Please, I do not want to use the copy command.

I know that can solve this problem with a stored procedure or anonymous block.

Have someone any idea?

1 Solution
How To Workaround Error: Ora-00997: Illegal Use Of Long Datatype [ID 361716.1]

Restrictions on LONG Datatype [ID 463115.1]

From the 2nd link:
In addition, LONG columns cannot appear in these parts of SQL statements:

* SELECT lists in subqueries in INSERT statements

Your options are limited:

1) the copy command (which you do not want to use)
2) converting those LONG columns to LOB
3) using a C program (Inserting LONG RAW Datatype [ID 33564.1])
3b) here is another C program example (How to INSERT Data Into and FETCH Data From a LONG Datatype Column Using the Oracle Call Interface (OCI) [ID 1015863.6])
Naveen KumarProduction Manager / Application Support ManagerCommented:
The best option to go for is to convert/migrate from LONG to LOB/CLOB/BLOB accordingly as these are supported in the new oracle versions and its tools/utilities which is not the case with LONG datatype. I believe it is just existing for the sake of backward compatibility.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now