Basically, the data templates are stored in XDO_LOBS. This applies to both custom and seeded reports – of course the later can be ignored for the checks performed here. We managed to identify all the custom lobs with an SQL similar to the following one:
SELECT
‘XDOEXP_’|| lob_code|| ‘.xml’ file_name,
file_data blob
FROM
xdo_lobs
WHERE
lob_type = ‘DATA_TEMPLATE’
AND ( application_short_name LIKE ‘XX%’
OR lob_code LIKE ‘XX%’
OR file_name LIKE ‘XX%’ )
Of course, the exact one depends on your naming schema.
With this and the use of UTL_FILE a backup of all XMLs is in a database directory (check dba_directories for something suitable that is ideally accessible both from the DB and the Apps tier):
BEGIN
FOR r IN (
SELECT
‘XDOEXP_’
|| lob_code
|| ‘.xml’ file_name,
file_data blob
FROM
xdo_lobs
WHERE
lob_type = ‘DATA_TEMPLATE’
AND ( application_short_name LIKE ‘XX%’
OR lob_code LIKE ‘XX%’
OR file_name LIKE ‘XX%’
OR lob_code LIKE ‘ISD%’ )
) LOOP
l_amount := 32767;
l_pos := 1;
l_buffer := NULL;
l_blob_len := dbms_lob.getlength(r.blob);
l_file := utl_file.fopen(l_dir, r.file_name, ‘wb’, 32767);
— Read chunks of the BLOB and write them to the file until complete.
WHILE l_pos <= l_blob_len LOOP
dbms_lob.read(r.blob, l_amount, l_pos, l_buffer);
utl_file.put_raw(l_file, l_buffer, true);
l_pos := l_pos + l_amount;
END LOOP;
utl_file.fclose(l_file);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
IF utl_file.is_open(l_file) THEN
utl_file.fclose(l_file);
END IF;
RAISE;
END blob_to_file;
/
