Checking for E-Business Suite 12.2 Online Patching Readiness in BI Publisher Data Templates

When upgrading Oracle E-Business Suite from Release 12.1 (or earlier) to 12.2.X one of the first steps to perform is to make sure that all custom development does not violate Online Patching prerequisites. To achieve this, Oracle provides a range of tools to check both the database objects (Schema registrations and Schema-Objects, especially Packages, (Materialized) Views, Function, …) and for file objects (forms, Reports, BI Publisher data templates, Java, …).

Details on this can be found here: https://docs.oracle.com/cd/E26401_01/doc.122/e22961/T302934T608605.htm and in the Support Note Using the Online Patching Readiness Report in Oracle E-Business Suite Release 12.2 (Doc ID 1531121.1).

The BI Publisher Challenge

Although the BI Publisher Data Templates are technically stored in the database, the compliance of those object can unfortunately only be checked by transferring XML files with the data templates to the Global Standards and Compliance Checker (GSCC).

For one of our customers who is in the process of upgrading from 12.1.3 to 12.2.9, there was unfortunately no “central” location containing all the latest Data Definition XML Files. Instead of searching through these objects throughout the source code management system, we decided to back up the XML files from the database using a small script similar to the one described here: https://oracle-base.com/articles/9i/export-blob-9i

Exporting all Data Templates

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;
/

Running gscc.pl

After the export (that ran for maybe 30 seconds on our instance to export 250 BI Publisher Reports) just run the gscc.pl tool from the apps tier:

$FND_TOP/bin/gscc.pl -f ‘/mnt/CloudRepo/backup_OCI/ENTW/XDOE*’ -pp

I prefer to pass the “-pp” Parameter so that the tool not only shows me files with violations, but all files. However, if you have more reports, you can omit that parameter.

For us, the above procedure brought up appx. 20 violations out of the 250 BI Publisher reports, and doing so in the manner described above was way more convenient than finding all the 250 XML files in the customer source code repository.