--先创建Blog转换为Clob的function
CREATE OR REPLACE FUNCTION BlobToClob(blob_in IN BLOB) RETURN CLOB AS
v_clobCLOB;
v_varchar VARCHAR2(32767);
v_startPLS_INTEGER := 1;
v_bufferPLS_INTEGER := 32767;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_clob,TRUE);
FOR i IN 1 .. CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer) LOOP
v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in,v_buffer,v_start));
DBMS_LOB.WRITEAPPEND(v_clob,LENGTH(v_varchar),v_varchar);
DBMS_OUTPUT.PUT_LINE(v_varchar);
v_start := v_start + v_buffer;
END LOOP;
RETURN v_clob;
END BlobToClob;

create table tab2 AS SELECT * FROM tab;

-- 删除c_xml列
alter table tab drop column c_xml;
-- 添加c_xml列,类型为clob
alter table tab add c_xml clob;

--在命令模式下执行如下代码,将备份到tab2中的c_xml中的数据恢复到tab中来
DECLARE
task_id NUMBER;
sour_blob BLOB;
dest_clob CLOB;
userRow tab2%rowtype;
cursor userRows is select * from tab2;
BEGIN
for userRow in userRows loop
task_id := userRow.n_task_id;
sour_blob := userRow.c_xml;
dest_clob := blobtoclob(sour_blob);

UPDATE tab SET c_xml = dest_clob WHERE n_task_id = task_id;
COMMIT;
end loop;
END;