Tuesday, January 24, 2017

Oracle Report to XML Publisher Report Migration

In Oracle Report the Data Model (data extraction logic) and Layout (presentation), both are embedded together as an rdf file. In XML Publisher both of these layers are separated; the migration is two step process. Data Model migration and Layout migration. These can be combined into a single shell script to convert an Oracle Report. The shell script can be modified to convert all Oracle Reports in a given directory.
The overall flow is as follows:

2.0 Pre-Requisites

The migration APIs accept an Oracle Report in XML Format, this format is only supported in Oracle Reports 9i and above. Therefore you need to have a 9i+ version of Oracle Reports available. For R12 the Oracle Reports version in the techstack is 10.1.3 so the conversion to RDF XML is straightforward. For Release 11i the version is 6i, so you will need a higher version of Oracle Reports available somewhere on your system.
To get the Oracle Report in XML format from rdf format, either use Oracle Report Designer or Oracle Report rwconverter.exe utility under $ORACLE_HOME/bin

D:\Oracle_home\BIN>rwconverter batch=yes  source= h:\reports\raxinv.rdf  dest= h:\reports\raxinv.xml  dtype=xmlfile  overwrite=yes

This will convert the binary RDF file into an RDF-XML format that can then be consumed by the conversion APIs.

3.0 Process

3.1 Data Model Migration.

Use DataTemplateGenerator API to migrate the Oracle Reports Data Model to a DataTemplate and associated PL/SQL logic to PL/SQL Package (specification and body).

The API can be called through the command line or through a shell script. This will generate following output files.
  • DataTemplate (REPORT.xml)
  • Default PL/SQL package specification (REPORTS.pls)
  • Default PL/SQL package body (REPORTB.pls).

Example :
javaw.exe  oracle.apps.xdo.rdfparser.DataTemplateGenerator H:\report\raxinv.xml

Output files
PL/SQL Package:H:\ report\raxinvS.pls
PL/SQL Body:H:\report\raxinvB.pls

3.2 Layout Migration.

Use RTFTemplateGenerator API to migrate the Oracle Reports layout to an XML Publisher RTF template.
Since there is no support of PL/SQL in RTF Template, the process does not migrate any format trigger logic present in the report. Instead the generator writes all the format trigger code to log file. You will need to implement any corresponding PL/SQL logic as XSL code. The majority of Oracle Reports use simple ‘if’ formatting logic that can be converted relatively easily. To aid in this process, the resulting RTF template will contain formfields that hold the format trigger names that are called, these fields will be highlighted in red. You can then refer to the log to find the actual PL/SQL code used in the original Oracle Report.

The API can be called through the command line or through a shell script. This will generate following output files.
  • RTF Template
  • Log file

Example :
javaw.exe  oracle.apps.xdo.rdfparser.RTFTemplateGenerator  H:\report\raxinv.xml

Output files
RTF Template: H:\ report\ raxinv.rtf
Log File : H:\report\raxinv.log

4.0 Known Issues:

  1. Some times, because of complexity of Oracle Report the, Data Template or PL/SQL has some minor errors and requires manual correction.
  2. Format triggers are not supported. The format trigger logic should be implemented separately though XSLT

  1. If formula column reference the summary column as parameter and the summary column belongs to same Data Source/Data Query, this implementation is not supported in Data Template. This is because of all the formula columns moved to select statement and the summary column value is not available while executing the formula.


5.0 Batch Conversion

The three components to the migration can be combined into a single shell script to completely automate the process. Furthermore, rather than just have the script run on a single report file, the script can be modified to run on all the reports in a given directory.
This script will convert all RDFs in a directory. You will need a few java libraries:
  1. Collections.zip – available from Sun
  2. xmlparserv2-904 – available from the JAVA_TOP directories
  3. A pointer to the Apps JAVA_TOP – under here are the necessary XML Publisher libraries

#  This script will generate a report for each template presnet in the current directory
# Create a variable to hold the classpath
classpath="DIR/collections.zip:DIR/xmlparserv2-904.zip:JAVA_TOP directory"
if [ $# -eq 0 ]
  for file in  *.rdf
echo "Processing ... $file"
    if test -f $file
# Convert the rdf to xml
       echo yes | $ORACLE_HOME/bin/rwconverter.sh batch=yes  source=$file dest=$file  dtype=xmlfile overwrite=yes; \
# Create a variable to hold the new xml file name, this is just a simple replace
# statement

# Generate the data template plus plsql
        echo yes | /local/java/jdk1.5.0_06/bin/java -classpath $classpath oracle.apps.xdo.rdfparser.DataTemplateGenerator $xfile;
# Generate the RTF template
        echo yes | /local/java/jdk1.5.0_06/bin/java –classpath $classpath  oracle.apps.xdo.rdfparser.RTFTemplateGenerator $xfile;


  echo usage: $0
  echo this script will generate a data template and supporting plsql and an RTF template in the current directory


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect