由ORA-00997: illegal use of long datatype引发的血案之long转换为lob之二

结论

1,long列不能直接迁移到另一个包含long列的表，否则会报错：ORA-00997: illegal use of LONG datatype
2，可采用间接方式：
    构建一个中间表，对应源表的long列为clob
    把源表数据插入到中间表
    然后再把中间表的数据插入到包含long列的目标表
 3，即long-->clob--->long
 4,可知long不能到long
    clob相当牛比，可以与long非常灵活转换数据，所以请以后大量采用lob列吧   

测试

SQL> select * from v$version where rownum=1;


BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production




SQL> create table t_long1(a long);


Table created.


SQL> insert into t_long1 values(1);


1 row created.


SQL> commit;


Commit complete.


SQL> create table t_long1_bak as select * from t_long1;
create table t_long1_bak as select * from t_long1
                                   *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype


SQL> create table t_long1_bak(a clob);


Table created.




SQL> insert into t_long1_bak select to_lob(a) from t_long1;


1 row created.


SQL> commit;


Commit complete.


SQL> truncate table t_long1;


Table truncated.


SQL> insert into t_long1 select a from t_long1_bak;


1 row created.


SQL> commit;


Commit complete.


SQL> select * from t_long1;


A
--------------------------------------------------------------------------------
1

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1823844/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-1823844/