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
Example
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
DataTemplate:H:\report\raxinv_template.xml
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:
- Some
times, because of complexity of Oracle Report the, Data Template or PL/SQL
has some minor errors and requires manual correction.
- Format
triggers are not supported. The format trigger logic should be implemented
separately though XSLT
- 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:
- Collections.zip
– available from Sun
- xmlparserv2-904
– available from the JAVA_TOP directories
- A
pointer to the Apps JAVA_TOP – under here are the necessary XML Publisher
libraries
#!/bin/sh
# 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 ]
then
for file in *.rdf
do
echo "Processing ...
$file"
if test -f $file
then
# 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
xfile="${file//rdf/xml}";
# 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;
fi
done
else
echo usage: $0
echo this script will generate a
data template and supporting plsql and an RTF template in the current directory
fi
No comments:
Post a Comment