ORA-00997 without use copy command

Posted on 2012-08-31
Last Modified: 2012-09-03
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?

Question by:ralph_rea
    LVL 14

    Accepted 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])
    LVL 28

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Article by: Swadhin
    From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now