in my previous post, i have provided a way to convert BLOB to CLOB.

There is another way to do the conversion, based on Oracle Metalink DocID: 9876

*** Let us assume that the table name is Z.EMPLOYEES . And, let us assume that the column name that has a ‘BLOB’ data type is BIN_EMP. and the Temp column with ‘CLOB’ data type has the name WTF.

1. take export backup (expdp) of the ORIGINAL table.

 

2. add new temp column:

alter table Z.EMPLOYEES add (WTF CLOB);

3. start initialization:

UPDATE Z.EMPLOYEES SET WTF = empty_clob() WHERE BIN_EMP IS NOT NULL;

4. create the procedure for conversion, the following procedure will perform the conversion:

create or replace procedure Z.blob_to_clob_PROC as
a number;
b number;
c number := 1000;
d number;
offset number := 1;
v_clob clob;
amount binary_integer;
buffer1 raw(1000);
buffer2 varchar2(1000);
cursor c1 is select BIN_EMP, rowid from Z.EMPLOYEES where BIN_EMP is not null;
begin
for i in c1 loop
select WTF into v_clob from Z.EMPLOYEES where rowid = i.rowid for update;
a := dbms_lob.getlength(i.BIN_EMP);
b := ceil(a/c);
for j in 1..b loop
if (c*j <= a) then
d:= c;
else
d:= a – c*(j-1);
end if;
DBMS_LOB.READ ( i.BIN_EMP, d, offset, buffer1);
buffer2 := utl_raw.cast_to_varchar2(buffer1);
DBMS_LOB.WRITEAPPEND(v_clob, d, buffer2);
offset := offset + c;
end loop;
offset := 1;
update Z.EMPLOYEES set WTF = v_clob where rowid=i.rowid;
end loop;
commit;
end;
/

5. execute the following statements:

EXECUTE Z.blob_to_clob_PROC;
commit;

Remark: you can develop Unix Shell script for automating the execution of the procedure to be scheduled.

6. drop the original column with BLOB data type (BIN_EMP)

7.  rename the column WTF to BIN_EMP