How to migrate Oracle Reports to Oracle BI Publisher ?

According to the Oracle Lifetime Support Policy , Oracle is not planning any functional enhancements for Oracle Reports in its terminal release, or any subsequent releases. Oracle Fusion Middleware 12cR2 (12.2.1.3.0) will be Oracle Reports terminal release.

Oracle Report customers can migrate to Oracle BI Publisher (Oracle official tool for oracle reporting) at their own place while continuing to receive extended support as specified by the Oracle Reports Statement of Direction.

Oracle BI Publisher provides Oracle Reports to BI Publisher Conversion Assistant (official documentation) as a tool to convert reports from the Oracle Reports format to the Oracle BI Publisher format.

From a license point of view:

• For the moment, Oracle BI Publisher is free for all customers having a valid license for Oracle Forms and Oracle Reports

Source:https://docs.oracle.com/en/middleware/fusion-middleware/fmwlc/application-server-products-new-structure.html#GUID-A4194A90-9FDD-4C55-BCBB-D59B217BEC44

Let’s test this conversion tool:

Report Conversion Tools Installation

Before to install OR2BIPConvAssist tool, Oracle BI Publisher (BIP) must be installed. Check the Oracle BI Publisher documentation to install it. If a Middleware Home already exists for a fusion products like Oracle Forms/Reports, BIP must be installed into a new Middleware Home.

Go to oracle download website and download the Report Conversion Tools OR2BIPConvAssist.zip:

Unzip the file:

D:\oracle\software>unzip OR2BIPConvAssist.zip
Archive:  OR2BIPConvAssist.zip
creating: bin/
creating: lib/
inflating: bin/OR2BIPConvAssist.bat
inflating: bin/OR2BIPConvAssist.sh
inflating: lib/aolj.jar
inflating: lib/axis.jar
inflating: lib/biputil.jar
inflating: lib/collections.jar
inflating: lib/commons-discovery.jar
inflating: lib/commons-lang.jar
inflating: lib/commons-logging.jar
inflating: lib/help-share.jar
inflating: lib/i18nAPI_v3.jar
inflating: lib/jaxrpc.jar
inflating: lib/jewt4.jar
inflating: lib/mail.jar
inflating: lib/ohj.jar
inflating: lib/ojdbc6.jar
inflating: lib/ojdl.jar
inflating: lib/or2bipconvassist.jar
inflating: lib/oracle_ice.jar
inflating: lib/orai18n-collation.jar
inflating: lib/orai18n-mapping.jar
inflating: lib/orai18n.jar
inflating: lib/orawsdl.jar
inflating: lib/share.jar
inflating: lib/xdo-core.jar
inflating: lib/xdo-server.jar
inflating: lib/xmlparserv2.jar

Include the ORACLE_HOME path as PATH in the report conversion batch file and add ORACLE_HOME in environment variable:

Open Oracle Reports Builder and save the report in format xml:

Execute the report with Oracle Report before the conversion:

I expect to have the same report layout after conversion to BIP.

Start DOS or PowerShell in Administrator Mode and launch the Report Conversion Tool:

Click Next

Choose “RDF XML (.xml)” and click Next

Click Next

Choose Source, Target directory and Debug Mode and click Next

Click Next

Click Next

Choose the option to upload the converted reports to Oracle BI Publisher previously installed

Click Next

Click Convert

Click Next

Check we have no errors into Report Conversion Log File and Report Upload to Catalog file.

Oracle Bi Publisher and database configuration

Next step is to configure BI Publisher and create the report objects (Program Units / Formulas Columns) into the database not converted by the conversion tool because not existing into BI Publisher.

The conversion tool creates:

• Data Model (.xdm file)

• Report File (.xdo) + a template file (.rtf)

• One PL/SQL Package with procedures and functions which contains the code of Oracle Reports Program Units and Formula Columns

This PL/SQL package must be compiled in the target database :

All function and procedures start with the character “CF*” like “Column Formulas”, the term used into Oracle Reports which contains PL/SQL code related to columns data model.

The package specification does not compile due to bad syntax in the declaration of some functions “cf_****”

Modify with the correct syntax and retry the compilation:

The package body does not compile because:

• One of t he function contains some variables not declared

Let’s declared this variable into the package specification and compile it :

And recompile the package body:

Now let’s do the BI Publisher configuration:

Connect to BI Publisher:

Go to Converted Reports location:

The Oracle Report Parameter has been converted successfully:

The Oracle Report Parameter looks like:

The BI Publisher Report parameter looks like after the conversion:

The DataModels must be modified because the one converted is not correct:

Edit the DataModel:

“Edit Data Set” under menu option:

Modify the SQL statement, the formulas columns coming from oracle reports must be rewritten with CASE sql command:

SELECT ALL ABRECHNUNG_VORGANG.ID, 
 ABRECHNUNG_VORGANG.TYP, 
 . . .
 
CASE 
        WHEN UPPER(VERTRAG.VERTRAG_TYP) = 'AGENTUR' AND rechnung.ist_rechnung_lsv = 'J' AND ABRECHNUNG_VORGANG.TOT_NETTO_PRAEMIE >= 0
        THEN replace(report.get_text_report('*******', 'AGENTUR_LSV_BETRAG_POSITIV', 'BRIEF_TEXT', ADRESSE.SPRACHE), '%%RG_BETRAG%%', report.money_to_string(ABRECHNUNG_VORGANG.TOT_NETTO_PRAEMIE))
        WHEN UPPER(VERTRAG.VERTRAG_TYP) = 'AGENTUR' AND rechnung.ist_rechnung_lsv = 'J' AND ABRECHNUNG_VORGANG.TOT_NETTO_PRAEMIE < 0
        THEN replace(report.get_text_report('*******E', 'AGENTUR_LSV_BETRAG_NEGATIV', 'BRIEF_TEXT', ADRESSE.SPRACHE), '%%RG_BETRAG%%', report.money_to_string(abs(ABRECHNUNG_VORGANG.TOT_NETTO_PRAEMIE)))
        
        WHEN UPPER(VERTRAG.VERTRAG_TYP) = 'AGENTUR' AND rechnung.ist_rechnung_lsv != 'J' AND ABRECHNUNG_VORGANG.TOT_NETTO_PRAEMIE >= 0
        THEN replace(report.get_text_report('*******', 'AGENTUR_ESR_BETRAG_POSITIV', 'BRIEF_TEXT', ADRESSE.SPRACHE), '%%RG_BETRAG%%', report.money_to_string(ABRECHNUNG_VORGANG.TOT_NETTO_PRAEMIE))
        WHEN UPPER(VERTRAG.VERTRAG_TYP) = 'AGENTUR' AND rechnung.ist_rechnung_lsv != 'J' AND ABRECHNUNG_VORGANG.TOT_NETTO_PRAEMIE < 0
        THEN replace(report.get_text_report('******', 'AGENTUR_ESR_BETRAG_POSITIV', 'BRIEF_TEXT', ADRESSE.SPRACHE), '%%RG_BETRAG%%', report.money_to_string(abs(ABRECHNUNG_VORGANG.TOT_NETTO_PRAEMIE)))
     END) CF_BRIEFE_TEXT,
 	. . .
 FROM . . 
 WHERE . . .

Click OK

Do the same for all Data Models

Now let’s focus on the report layout:

First “bad” suprise, The Report Conversion tool does not convert completely the layout, we have to recreate the layout from the beginning.

Oracle BI Publisher Desktop Installation

In BI Publisher, the report layout must be created with Microsoft Office tools.

The first step is to download and install BI Publisher Desktop which adds a AddIns for Microsoft Office products (Word, Excel,etc.) which will be used to create the report layout.

If we use Microsoft Office 32 bits on a Microsoft 64 bits, we have to install last version of Java 32 bits and mix it with Java 64 bits (needed for BI Publisher Server).

Download and Install Download and Install BIP Desktop 11.1.1.9.0 – 32 bits, the only version compatible with Office 2016, 32 bits.

Run As Administrator:

Click OK

Click Next

Click Next

Click Finish

Check you have a new menu “BI Publisher” into Microsft Word and logon to BI Publisher from Microsoft Word:

Now you can either Create a report either open a report template  (the .rtf file generated by the Conversion Report Tool).

Start to edit the report.

Oracle BI Publisher configuration – report configuration

Go to BI Publisher console administration and upload the .xdo file (Click “Upload Resource” into Task menu):

Execute the report (click on View Report link):

Click Apply

If we compare with the current Oracle Report Layout, there is a big difference in the layout.

Conclusion:

The Report Conversion Tool does not convert successfully Oracle Reports rdf file:

• The Parameter screen is the only part converted with success
• The Data Model must be rewritten, mainly to integrate oracle reports formula columns
• The layout must me completely rebuild, all fields are in a bad position, some fileds are missing.
• The PL/SQL package which contains oracle reports program units must be modified since some errors syntax exists. Program Units in Oracle Reports are used in general to calculate oracle reports formulas columns (column where the results comes from a PL/SQL procedures or packages).
• The oracle report triggers must be refactored since the report conversion tool does not migrate them. This oracle report triggers are responsible to display or hide some fields. Recreate this trigger into BI Publisher is possible with the BI Publisher option “Event Triggers”.