Oracle 修改blob为clob，clob为blob

应用条件 1. blob 包含文本, 内容并不是很多 2. 备份blob文本到clob 3. oracle v11 blob to clob

--Add new clob column        
ALTER TABLE TABLENAME ADD (CLOB_COLUMN CLOB);
--copy blob value to clob
UPDATE TABLENAME SET CLOB_COLUMN = UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOB_COLUMN,2000));
--drop blob
ALTER TABLE TABLENAME DROP COLUMN BLOB_COLUMN;
--rename clob column, name same as blob's
ALTER TABLE TABLENAME RENAME COLUMN CLOB_COLUMN TO BLOB_COLUMN_NEW_NAME;

clob to blob

--Add new blob column        
ALTER TABLE TABLENAME  ADD (BLOB_COLUMN BLOB);
--copy clob to blob
UPDATE TABLENAME SET BLOB_COLUMN = UTL_RAW.CAST_TO_RAW(CLOB_COLUMN);
-- drop clob
ALTER TABLE TABLENAME DROP COLUMN CLOB_COLUMN;
--rename blob column
ALTER TABLE TABLENAME RENAME COLUMN BLOB_COLUMN TO BLOB_COLUMN_NEW_NAME;