Oracle Long類型轉換為Clob類型

INSERT INTO sammy_test_clob
   SELECT TO_LOB (report_xml)
     FROM qm_s_report
    WHERE report_name = 'Sammy';

TO_LOB函數和LONG類型一樣，限制有很多。簡單的說，TO_LOB一般只用在CREATE TABLE或INSERT TABLE語句後面的子查詢中。在其他地方使用會報錯，比如UPDATE語句。

這還不是最大的問題，最大的問題在於，TO_LOB函數似乎並沒有真正的將LONG類型轉化為LOB資料類型。個人感覺，Oracle只是對LONG類型做了一些處理，使之可以存放到一個LOB類型中去。

轉自：http://blog.csdn.net/gbnew/archive/2007/07/20/1700596.aspx

TO_LOB函數是一個很特殊的函數，特殊之處在於，這個函數可以處理LONG類型資料，而且這個函數和LONG類型一樣，擁有很多的限制。不過，這些還不是很特殊的地方，下面簡單看一下TO_LOB這個函數。

 

Oracle的LONG類型可謂“臭名昭著”，由於LONG類型的限制太多，以至於Oracle很少去提LONG類型有哪些限制條件，而一般都是通過說明在哪些情況下，可以使用LONG類型。

正是這些限制阻止了LONG的使用，Oracle也在推出了大物件類型——LOB之後，強烈建議使用者不要在使用LONG類型。

但是，具有諷刺意味的是，Oracle建議使用者不要再使用LONG類型，可是資料字典中，隨處可以看到LONG的身影。而且，即使是目前使用的最高版本10R2，LONG類型仍然在資料字典中隨處可見。不知道Oracle是考慮相容性的問題還是其他什麼原因，反正Oracle仍然沒有把LONG類型從資料字典中移出去。不知道11g中是否有所改觀。

雖然Oracle自己沒有做到，但是仍然建議使用者不要在使用LONG，並使用BLOB、CLOB來替換現有系統中的LONG欄位。而且LONG類型的限制也確實使人頭疼，將LONG類型轉化為LOB類型的工具，就是TO_LOB函數。

TO_LOB函數和LONG類型一樣，限制有很多。簡單的說，TO_LOB一般只用在CREATE TABLE或INSERT TABLE語句後面的子查詢中。在其他地方使用會報錯，比如UPDATE語句。

這還不是最大的問題，最大的問題在於，TO_LOB函數似乎並沒有真正的將LONG類型轉化為LOB資料類型。個人感覺，Oracle只是對LONG類型做了一些處理，使之可以存放到一個LOB類型中去。

SQL> CREATE TABLE T1 (ID NUMBER, TEXT CLOB);

表已建立。

SQL> CREATE TABLE T2 (ID NUMBER, TEXT VARCHAR2(4000));

表已建立。

SQL> INSERT INTO T1 SELECT ROWNUM, TEXT FROM DBA_VIEWS;
INSERT INTO T1 SELECT ROWNUM, TEXT FROM DBA_VIEWS
*第 1 行出現錯誤:
ORA-00997: illegal use of LONG datatype

SQL> INSERT INTO T1 SELECT ROWNUM, TO_LOB(TEXT) FROM DBA_VIEWS;

已建立2268行。

SQL> COMMIT;

提交完成。

使用TO_LOB可以將LONG資料插入到CLOB欄位中，但是如果想要將LONG資料插入到VARCHAR2中：

SQL> INSERT INTO T2 SELECT ROWNUM, TEXT FROM DBA_VIEWS;
INSERT INTO T2 SELECT ROWNUM, TEXT FROM DBA_VIEWS
*第 1 行出現錯誤:
ORA-00997: illegal use of LONG datatype

SQL> INSERT INTO T2 SELECT ROWNUM, DBMS_LOB.SUBSTR(TO_LOB(TEXT), 4000, 1) FROM DBA_VIEWS;
INSERT INTO T2 SELECT ROWNUM, DBMS_LOB.SUBSTR(TO_LOB(TEXT), 4000, 1) FROM DBA_VIEWS
*第 1 行出現錯誤:
ORA-00932: inconsistent datatypes: expected - got LONG

直接插入肯定不行，但是剛才已經得到了CLOB類型，那麼將CLOB轉化為VARCHAR2不就可以了？但是結果確出人意料。觀察錯誤資訊，Oracle認為返回的資料類型是LONG。似乎TO_LOB並沒有進行資料類型的轉化。下面再驗證一下：

SQL> SELECT DUMP(TO_LOB(TEXT)) FROM DBA_VIEWS;
SELECT DUMP(TO_LOB(TEXT)) FROM DBA_VIEWS
*第 1 行出現錯誤:
ORA-00932: inconsistent datatypes: expected - got LONG

SQL> SELECT DUMP(TEXT) FROM T1;
SELECT DUMP(TEXT) FROM T1
*第 1 行出現錯誤:
ORA-00932: inconsistent datatypes: expected - got CLOB

從這個對比中已經可以清楚的看到，TO_LOB函數並不像想象中的那樣返回CLOB類型，而實際上返回的仍然是LONG類型。

SQL> INSERT INTO T2 SELECT ROWNUM, TO_LOB(TEXT) FROM DBA_VIEWS;

已建立2268行。

直接使用TO_LOB似乎可以插入，但是仔細對比一下結果就會發現，LONG類型資料沒有真正的插入到表中：

SQL> COL TEXT FORMAT A50
SQL> SET LONG 50
SQL> SELECT * FROM T2 WHERE ROWNUM < 3;

ID TEXT
---------- --------------------------------------------------
1
2

SQL> SELECT * FROM T1 WHERE ROWNUM < 3;

ID TEXT
---------- --------------------------------------------------
1 select OWNER, TABLE_NAME, TABLESPACE_NAME, CLUSTER
2 select a.apply_name, a.queue_name, a.queue_owner,