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
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
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
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
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