VARCHAR2轉換為CLOB碰到ORA-22858錯誤

今天開發人員問我如何將一個VARCHAR2型別的欄位轉化為CLOB型別。

 

 

記得在9i及以前版本是不能直接轉換的，不過10g以上版本還真沒有注意過這個問題，測試了一下直接轉換，果然碰到了ORA-22858錯誤。

SQL> CREATE TABLE T_VAR (C VARCHAR2(4000));

Table created.

SQL> INSERT INTO T_VAR
  2  VALUES (LPAD('A', 4000, 'A'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> ALTER TABLE T_VAR MODIFY (C CLOB);
ALTER TABLE T_VAR MODIFY (C CLOB)
                          *
ERROR at line 1:
ORA-22858: invalid alteration of datatype

Oracle的文件對這個錯誤的描述是：

ORA-22858: invalid alteration of datatype
Cause: An attempt was made to modify the column type to object, REF, nested table, VARRAY or LOB type.
Action: Create a new column of the desired type and copy the current column data to the new type using the appropriate type constructor.

顯然是CLOB欄位的特殊性，限制了直接修改資料型別。

雖然不能直接修改為CLOB，但是如果記錄為空，可以直接修改為LONG型別：

SQL> ALTER TABLE T_VAR MODIFY (C LONG);
ALTER TABLE T_VAR MODIFY (C LONG)
                          *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

SQL> DELETE T_VAR;

1 row deleted.

SQL> ALTER TABLE T_VAR MODIFY (C LONG);

Table altered.

不久前的一篇文章介紹過，對於LONG型別，不管有沒有資料存在，可以直接修改為CLOB型別：http://yangtingkun.itpub.net/post/468/501094

SQL> INSERT INTO T_VAR
  2  VALUES (LPAD('A', 4000, 'A'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> ALTER TABLE T_VAR MODIFY (C CLOB);

Table altered.

對於LONG型別的轉換，Oracle並不是簡單的將列的定義換成CLOB，而是生成了一個臨時列，將資料儲存，然後刪除原LONG列。

Oracle可以對LONG型別的轉換操作進行封裝，不知道為什麼沒有對VARCHAR2型別轉換為CLOB進行封裝，使得一個簡單的ALTER TABLE命令必須透過多個命令才能完成。

至於VARCHAR2到CLOB的轉換就沒有必要詳細說明了，採用線上重定義就能實現。如果有足夠的維護時間，也可以直接新增CLOB列，對新增CLOB列賦值、刪除原VARCHAR2型別列，最後對新增CLOB列改名。