Script to Converting BLOB to CLOB

BLOB to CLOB

step-1) create temp table
step-2) prepare conversion script
step-3) execute script to store converted CLOB to temp table


Table creation


CREATE TABLE
APPS . XXNR_XML_DATA_TEMPLATE_TEMP (
TEMPLATE_CODE VARCHAR2 ( 200 BYTE),
DATA_SOURCE_CODE VARCHAR2 ( 200 BYTE),
FILE_NAME VARCHAR2 ( 200 BYTE),
FILE_DATA CLOB
);


Conversion Script


CREATE OR REPLACE PROCEDURE XX_BLOB_RAMA
IS
v_clob CLOB ;
v_varchar VARCHAR2 ( 32767 );
v_start PLS_INTEGER := 1 ;
v_buffer PLS_INTEGER := 32767 ;

BEGIN
DBMS_OUTPUT.put_line ( 'starting blob to clob conversion' );
alter session set nls_language= 'AMERICAN' ;

FOR x
IN (SELECT xtv . template_code ,
xdd . DATA_SOURCE_CODE ,
xl . LOB_CODE ,
xl . file_data ,
xl . FILE_NAME
FROM XDO_TEMPLATES_VL xtv , XDO_DS_DEFINITIONS_VL xdd , XDO_LOBS xl
WHERE xdd . DATA_SOURCE_CODE = xtv . DATA_SOURCE_CODE
AND xl . LOB_CODE = xtv . DATA_SOURCE_CODE
AND lob_type = 'DATA_TEMPLATE'
AND xl . APPLICATION_SHORT_NAME IN ( 'XXPEP' , 'XXABC' ))
LOOP
BEGIN

DBMS_OUTPUT.put_line ( 'prccessing DATA_SOURCE_CODE ' || x . DATA_SOURCE_CODE );
v_start := 1 ;
v_clob := NULL;
v_varchar := NULL;
DBMS_LOB.CREATETEMPORARY ( v_clob , TRUE);
FOR i IN 1 .. CEIL ( DBMS_LOB.GETLENGTH ( x . file_data ) / v_buffer )

LOOP
v_varchar :=
UTL_RAW . CAST_TO_VARCHAR2 (
DBMS_LOB.SUBSTR ( x . file_data , v_buffer , v_start ) );
DBMS_LOB.WRITEAPPEND ( v_clob , LENGTH ( v_varchar ), v_varchar );
v_start := v_start + v_buffer ;
END LOOP;

INSERT INTO XXNR_XML_DATA_TEMPLATE_TEMP
VALUES ( x . TEMPLATE_CODE ,
x . DATA_SOURCE_CODe ,
x . FILE_NAME ,
v_clob );
COMMIT;

DBMS_OUTPUT.put_line ( 'DATA_SOURCE_CODE ' || x . DATA_SOURCE_CODE || ' processed.' );
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
END LOOP;
END XX_BLOB_RAMA ;


Finally execute the script


BEGIN
XX_BLOB_RAMA ;
END;


see the below script result in custom table  XXNR_XML_DATA_TEMPLATE_TEMP