LONG类型的使用的一点总结
作者 ：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2008/11/long-type-usage.html
链接：http://www.dbaroad.me/archives/2008/11/long-type-usage.html
今天在做insert时，报了个
ORA-00997: illegal use of LONG datatype
原来是表上有个LONG类型的字段。oracle在使用long类型时，还是有比较多限制的。
简单做了个实验来实现数据间的迁移：
方法一：使用to_lob
优点：不受SQLPLUS参数LONG的影响
缺点：字段类型需发生改变，若表字段仍为LONG，则插进去的数据为NULL值
sys@TESTDBA>create table t1 (a long); Table created. sys@TESTDBA>insert into t1 values('abcd'); 1 row created. sys@TESTDBA>commit; Commit complete. sys@TESTDBA>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 sys@TESTDBA>create table t2 as select to_lob(a) from t1; create table t2 as select to_lob(a) from t1 * ERROR at line 1: ORA-00998: must name this expression with a column alias sys@TESTDBA>create table t2 as select to_lob(a) a from t1; Table created. sys@TESTDBA>desc t1; Name Null? Type ------------------- -------- --------- A LONG sys@TESTDBA>desc t2; Name Null? Type ------------------- -------- --------- A CLOB sys@TESTDBA>select * from t1; A ---------- abcd 1 row selected. sys@TESTDBA>select * from t2; A ---------- abcd 1 row selected. sys@TESTDBA>insert into t2 select to_lob(a) a from t1; 1 row created. sys@TESTDBA>commit; Commit complete. sys@TESTDBA>select * from t2; A ---------- abcd abcd 2 rows selected. |
这里我们要发现通过这种方法，表T1、T2字段类型已不同。
另外有一点我们需要注意，如果表T2已存在，且字段类型为LONG，
通过TO_LOB的方法，insert进去的值是存在问题的。
sys@TESTDBA>drop table t2; Table dropped. sys@TESTDBA>create table t2(a long); Table created. sys@TESTDBA>insert into t2 select to_lob(a) a from t1; 1 row created. sys@TESTDBA>commit; Commit complete. sys@TESTDBA>select * from t2; A - 1 row selected. sys@TESTDBA>insert into t2 select to_lob(a) a from t1; 1 row created. sys@TESTDBA>r 1* insert into t2 select to_lob(a) a from t1 1 row created. sys@TESTDBA>r 1* insert into t2 select to_lob(a) a from t1 1 row created. sys@TESTDBA>select * from t2; A - 4 rows selected. sys@TESTDBA>desc t2 Name Null? Type ------------------ -------- ------- A LONG sys@TESTDBA>alter table t2 modify a not null; alter table t2 modify a not null * ERROR at line 1: ORA-02296: cannot enable (SYS.) - null values found sys@TESTDBA>drop table t2; Table dropped. sys@TESTDBA>create table t2(a long not null); Table created. sys@TESTDBA>desc t2 Name Null? Type ------------------ -------- ---------- A NOT NULL LONG sys@TESTDBA>insert into t2 select to_lob(a) a from t1; insert into t2 select to_lob(a) a from t1 * ERROR at line 1: ORA-01400: cannot insert NULL into ("SYS"."T2"."A") |
这里我们发现，表T2已存在，如果使用to_lob进行insert，虽没报错，但字段里内容却为NULL值。
如果表已存在，且字段为LONG类型，则可以用COPY FROM的方法。
方法二：使用COPY FROM
优点：类型未发生改变
缺点：受SQLPLUS参数LONG的影响
sys@TESTDBA>copy from sys/oracle@test to sys/oracle@test create t3 using select * from t1; Array fetch/bind size is 15. (arraysize is 15) Will commit when done. (copycommit is 0) Maximum long size is 10. (long is 10) Table T3 created. 1 rows selected from sys@test. 1 rows inserted into T3. 1 rows committed into T3 at sys@test. sys@TESTDBA>select * from t3; A ---------- abcd 1 row selected. sys@TESTDBA>desc t3 Name Null? Type ------------------ -------- --------- A LONG sys@TESTDBA>copy from sys/oracle@test to sys/oracle@test insert t3 using select to_lob(a) a from t1; Array fetch/bind size is 15. (arraysize is 15) Will commit when done. (copycommit is 0) Maximum long size is 10. (long is 10) select to_lob(a) a from t1 * Error in SELECT statement: ORA-00932: inconsistent datatypes: expected - got LONG sys@TESTDBA>copy from sys/oracle@test to sys/oracle@test insert t3 using select * from t1; Array fetch/bind size is 15. (arraysize is 15) Will commit when done. (copycommit is 0) Maximum long size is 10. (long is 10) 1 rows selected from sys@test. 1 rows inserted into T3. 1 rows committed into T3 at sys@test. sys@TESTDBA>select * from t3; A ---------- abcd abcd 2 rows selected. sys@TESTDBA> |
最后，我们再来看SQLPLUS参数LONG对数据的截取影响。
sys@TESTDBA>set long 1 sys@TESTDBA>copy from sys/oracle@test to sys/oracle@test insert t3 using select * from t1; Array fetch/bind size is 15. (arraysize is 15) Will commit when done. (copycommit is 0) Maximum long size is 1. (long is 1) 1 rows selected from sys@test. 1 rows inserted into T3. 1 rows committed into T3 at sys@test. sys@TESTDBA>set long 10 sys@TESTDBA>select * from t3; A ---------- abcd abcd a 3 rows selected. |
可以看出，COPY FROM 受到LONG参数的影响，因而在使用过程中，需要多加注意。
再来看看TO_LOB，不受ONG参数的影响。其实也很好理解，因为COPY FROM 只能在SQLPLUS中使用。
sys@TESTDBA>create table t4 (a clob); Table created. sys@TESTDBA>set long 1 sys@TESTDBA>insert into t4 select to_lob(a) a from t1; 1 row created. sys@TESTDBA>set long 10 sys@TESTDBA>insert into t4 select to_lob(a) a from t1; 1 row created. sys@TESTDBA>commit; Commit complete. sys@TESTDBA>select * from t4; A ---------- abcd abcd 2 rows selected. |
— The End —
关键字: 基础知识
站内搜索