ORA-997 非法使用LONG字段类型-CSDN博客

Oracle在对表记录做插入时，如果使用insert … select …类型的语句，且操作中含有LONG/LONG RAW字段类型，那么会遇到报错ORA-00997: illegal use of LONG datatype。

SQL> create table t1 (num number,num2 long);

Table created.

SQL> insert into t1 values(1,1);

1 row created.

SQL> commit;

SQL> select * from t1;

NUM NUM2

---------- --------------------------------------------------------------------------------

1 1

SQL> create table t2 as select * from t1;

create table t2 as select * from t1

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

SQL> create table t2 (num number,num2 long);

Table created.

SQL> insert into t2 select * from t1;

insert into t2 select * from t1

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

在这里，出现了ORA-997报错。其实我们可以使用copy命令将t1表中的LONG字段类型插入到t2表中。

SQL> drop table t2;

Table droped.

SQL> copy from test/test@HT to test/test@HT create t2 using select * from t1;

Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)

Maximum long size is 80. (long is 80)

Table T2 created.

1 rows selected from test@HT.

1 rows inserted into T2.

1 rows committed into T2 at test@HT.

SQL> select * from t2;

NUM NUM2

---------- --------------------------------------------------------------------------------

1 1

SQL> copy from test/test@HT to test/test@HT insert t2 using select * from t1;

Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)

Maximum long size is 80. (long is 80)

1 rows selected from test@HT.

1 rows inserted into T2.

1 rows committed into T2 at test@HT.

SQL> select * from t2;

NUM NUM2

---------- --------------------------------------------------------------------------------

1 1

注意copy命令执行时有如下提示：

Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)

换句话说，一旦copy命令执行完成，事务就会提交。接上面insert操作：

SQL> rollback;

Rollback complete.

SQL> select * from t2;

NUM NUM2

---------- --------------------------------------------------------------------------------

1 1

可以看到insert的数据无法回滚。

附metalink说明：

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

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.6 to 9.2.0.8 - Release: 9.2 to 9.2
Information in this document applies to any platform.
***Checked for relevance on 25-Jul-2010***

ORA-997

Goal

How to work around the restriction of inserting into a table containing a LONG / LONG RAW field using a SELECT.

SQL Being used
-----------------
SQL> insert into LONG_TABLE1 select * from LONG_TABLE2 where FIELD1 > 100;

Error:
------------
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

Solution

The work around would be to use the COPY command.

Example:

Creating a small test table named LONG_TABLE1
------------------------------------------------------------
create table Long_table1 (Field1 number, Field2 varchar2(100), Field3 LONG);

Inserting 3 small records as sample data
---------------------------------------------
insert into Long_table1 values (1, 'Record 1', 'Long field for record 1');
insert into Long_table1 values (2, 'Record 2', 'Long field for record 2');
insert into Long_table1 values (3, 'Record 3', 'Long field for record 3');
commit;

Recreating the error:
-----------------------------------
SQL> create table long_table2 as select * from long_table1;
create table long_table2 as select * from long_table1
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

Testing the COPY command
---------------------------------------
SQL> COPY FROM TESTCASE/TIGER@G92 TO TESTCASE/TIGER@G92 -
> REPLACE LONG_TABLE2 USING SELECT * FROM LONG_TABLE1;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table LONG_TABLE2 created.

3 rows selected from TESTCASE@G92.
3 rows inserted into LONG_TABLE2.
3 rows committed into LONG_TABLE2 at TESTCASE@G92.

Formating the fields
-------------------------------------
SQL> column field1 format 999
SQL> column field2 format a15
SQL> column field3 format a30

Displaying the values from the newly created table
----------------------------------------------------------

SQL> SELECT * FROM LONG_TABLE2;
FIELD1 FIELD2 FIELD3
------ --------------- ------------------------------
1 Record 1 Long field for record 1
2 Record 2 Long field for record 2
3 Record 3 Long field for record 3