- Create temp table
- Prepare conversion script
- Execute script to store converted CLOB to temp table
Table creation:
CREATE TABLE APPS.XX_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_PRC
IS
CURSOR c_data IS
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');
v_clob CLOB;
v_varchar VARCHAR2 (32767);
v_start PLS_INTEGER := 1;
v_buffer PLS_INTEGER := 32767;
BEGIN
DBMS_OUTPUT.put_line ('start: blob to clob conversion');
EXECUTE IMMEDIATE 'alter session set nls_language = ''AMERICAN''';
FOR c_data_rec IN c_data
LOOP
BEGIN
DBMS_OUTPUT.put_line ('Processing DATA_SOURCE_CODE ' || c_data_rec.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 (c_data_rec.file_data) / v_buffer)
LOOP
v_varchar :=
UTL_RAW.CAST_TO_VARCHAR2 (
DBMS_LOB.SUBSTR (c_data_rec.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 XX_XML_DATA_TEMPLATE_TEMP
VALUES (c_data_rec.TEMPLATE_CODE,
c_data_rec.DATA_SOURCE_CODe,
c_data_rec.FILE_NAME,
v_clob);
COMMIT;
DBMS_OUTPUT.put_line (
'DATA_SOURCE_CODE: '
|| c_data_rec.DATA_SOURCE_CODE
|| ' processed successfully.');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
END LOOP;
END XX_BLOB_PRC;
Execute the script:
BEGIN
XX_BLOB_PRC;
END;
Verify the custom table : XX_XML_DATA_TEMPLATE_TEMP to see results.
