Convert BLOB TO CLOB column – METHOD 2

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

 

converting BLOB to CLOB datatype in oracle

Converting BLOB column data type to CLOB is a very tedious task, since you cannot do it directly.

 

First, you need to create an “Oracle Function” under an oracle schema you have access to, the following is the function:

 

CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB)

RETURN CLOB

AS

v_clob CLOB;

v_varchar VARCHAR2(32767);

v_start PLS_INTEGER := 1;

v_buffer PLS_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);

v_start := v_start + v_buffer;

END LOOP;

RETURN v_clob;

END blob_to_clob;

/

 

Second, alter the table by adding a temporary column with a name similar to the BLOB column name.

For example, let’s say the BLOB column name is STRING_NAMES, the new CLOB column will be STRING_NAMES2

 

Sql command:

 

Alter table DUMMY.TABLE add (STRING_NAMES2 CLOB);

 

Then, use the “update” statement to populate the column with the converted data:

 

update DUMMY.TABLE  set STRING_NAMES2=blob_to_clob(STRING_NAMES);

 

Once finished drop the original column, then rename the new one to the original name.

 

If your table has huge number of records, you may need to develop a script and schedule it to perform this intensive information.

*** UPDATE:

another way of converting BLOB to CLOB can be found here:

https://geodatamaster.wordpress.com/2014/01/01/convert-blob-to-clob-column-method-2/