Step1: Create a temporary table
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
);
Step2: Prepare conversion script
CREATE OR REPLACE PROCEDURE XX_BLOB_PROC
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_PROC ;
Step3: Execute script to store converted CLOB to temporary table
BEGIN
XX_BLOB_PROC ;
END;
Check the result in temporary table: XXNR_XML_DATA_TEMPLATE_TEMP
