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
c number := 1000;
offset number := 1;
cursor c1 is select BIN_EMP, rowid from Z.EMPLOYEES where BIN_EMP is not null;
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:= a – c*(j-1);
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;
offset := 1;
update Z.EMPLOYEES set WTF = v_clob where rowid=i.rowid;
5. execute the following statements:
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