由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