由ORA-00997: illegal use of long datatype引發的血案之long轉換為lob

wisdomone1發表於2015-10-30

背景:

  在測試某個技術問題時,發現long列的表不能複製到另一個表中,報ora-00997錯誤
SQL> create table cdef$_bak as select * from cdef$ where con#=11923;
create table cdef$_bak as select * from cdef$ where con#=11923
                                 *
ERROR at line 1:
ORA-00997: illegal use of
LONG datatype


結論

1,oracle11g基本不建議使用long,儲存期僅為了相容,建議使用lob
2,insert可以
  但update及delete(where條件為long)則會報錯,不能執行
3,long可以遷移到lob,有幾種方式
   a,dbms_redefination線上重定義
   b,create table t_long_bak as select to_lob(a) as a,b from t_long;--a列為long
   c,直接alter table modify a clob;--直接把c列由long變成clob
4,long raw轉換為blob,而long轉換為clob


5,暫時只測這些,關於long到lob轉換還有諸多知識,將在下文進行繼續測試     


測試

SQL> create table t_long(a long,b int);


Table created.


SQL> insert into t_long values(1,3);


1 row created.


SQL> commit;


Commit complete.


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


SQL> desc t_long;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  LONG
 B                                                  NUMBER(38)


查閱11g官方手冊,未找到,但卻找到long raw資料型別
SQL> create table t_long_raw(a long raw);


Table created.


SQL> desc t_long_raw;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  LONG RAW


可見long及long raw是2種不同的資料型別


既然11g官方手冊,肯定是在oracle 10g,果然在oracle 10g concepts找到了




根據官方手冊說法,不建議使用long,出現它也是為了向前相容,所以肯定是9i或之前引入的資料型別了,建議用lob代替


SQL> set long 99999
SQL> select * from t_long;


A                                                                                         B
-------------------------------------------------------------------------------- ----------
1                                                                                         3


可見update不行
SQL> update t_long set a=2 where a=1;
update t_long set a=2 where a=1
                            *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype


經過to_lob只適用於insert select子查詢是中的select部分
SQL> select a,to_lob(a),b from t_long;
select a,to_lob(a),b from t_long
         *
ERROR at line 1:
ORA-00932: inconsistent
datatypes: expected - got
LONG




delete不可以
SQL> delete from t_long where a=1;
delete from t_long where a=1
                         *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype






回過來頭再看上述這個報錯
SQL> create table t_long_bak as select * from t_long;
create table t_long_bak as select * from t_long
                                  *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype


只有在遷移或複製LONG列到另一個表中,表新目標表對應列資料型別應為lob,也就是說long列無法遷移
SQL> create table t_long_bak as select to_lob(a) a,b from t_long;


Table created.


SQL> set linesize 30
SQL> desc t_long_bak;
 Name              Null?    Type
 ----------------- -------- ------------
 A                          CLOB
 B                          NUMBER(38)




另外,也可以直接透過alter table modify clob轉換long列為clob列
 SQL> desc t_long;
 Name              Null?    Type
 ----------------- -------- ------------
 A                          LONG
 B                          NUMBER(38)


SQL> alter table t_long modify a clob;


Table altered.


SQL> desc t_long;
 Name              Null?    Type
 ----------------- -------- ------------
 A                          CLOB
 B                          NUMBER(38)


個人簡介:


8年oracle從業經驗,具備豐富的oracle技能,目前在國內北京某專業oracle服務公司從事高階技術顧問。
   
   服務過的客戶:
          中國電信
          中國移動
          中國聯通
          中國電通
          國家電網
          四川達州商業銀行
          湖南老百姓大藥房
          山西省公安廳
          中國郵政
          北京302醫院     
          河北廊坊新奧集團公司
  
 專案經驗:
           中國電信3G專案AAA系統資料庫部署及最佳化
           中國聯通CRM資料庫效能最佳化
           中國移動10086電商平臺資料庫部署及最佳化
           湖南老百姓大藥房ERR資料庫sql最佳化專案
           四川達州商業銀行TCBS核心業務系統資料庫模型設計和RAC部署及最佳化
           四川達州商業銀行TCBS核心業務系統後端批處理儲存過程功能模組編寫及最佳化
           北京高鐵訊號監控系統RAC資料庫部署及最佳化
           河南宇通客車資料庫效能最佳化
           中國電信電商平臺核心採購模組表模型設計及最佳化
           中國郵政儲蓄系統資料庫效能最佳化及sql最佳化
           北京302醫院資料庫遷移實施
           河北廊坊新奧data guard部署及最佳化
           山西公安廳身份證審計資料庫系統故障評估
         
 聯絡方式:
          手機:18201115468
          qq   :   305076427
          qq微博: wisdomone1
          新浪微博:wisdomone9
          qq群:275813900    
          itpub部落格名稱:wisdomone1    http://blog.itpub.net/9240380/

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9240380/viewspace-1818736/,如需轉載,請註明出處,否則將追究法律責任。

相關文章