Convert blobs in Oracle into CLOB

If the C_xml field in the tab table turns out to be a blob type, we will convert it to the CLOB type, and if there is data in the table, it cannot be modified directly through the alert statement. You can change the field of a BLOB type to the CLOB type by using the following method.

First create a function in Oracle with the following code:

--Create the function of the blog converted to CLOB first

CREATE OR REPLACE FUNCTIONBlobtoclob (blob_ininchBLOB)RETURNClob asV_clob Clob; V_varcharVARCHAR2(32767); V_start Pls_integer:= 1; V_buffer Pls_integer:= 32767;BEGINDbms_lob.    Createtemporary (V_clob, TRUE);  forIinch 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; ENDLOOP; RETURNV_clob;ENDBlobtoclob;

Then create a backup table to prevent data loss. The code is as follows:

Create Table  as SELECT *  from Tab;

Delete the BLOB column in the table and add the CLOB column. The code is as follows:

--Delete c_xml column

Alter Table Drop column c_xml;

--Add C_xml column, type CLOB

Alter Table add c_xml clob;

Finally restore the backed up data back. The code is as follows:

--Execute the following code in command mode to restore data from the C_xml backed up to tab2 into tab

DECLAREtask_id Number; Sour_blob Blob;dest_clob clob;userrow tab2%RowType;cursorUserrows is Select *  fromtab2;BEGIN  forUserrowinchuserrows loop task_id:=userrow.n_task_id; Sour_blob:=Userrow.c_xml; Dest_clob:=Blobtoclob (SOUR_BLOB); UPDATEtabSETC_xml=Dest_clobWHEREn_task_id=task_id; COMMIT; EndLoop;END;

Convert blobs in Oracle into CLOB