Converting RDF Reports to XM Publisher

One of my friends was working on converting 11.5.10 RDF reports into XML Publisher reports. Not a very strange requirement, considering everyone now wants to move to XML Publisher. What however, was strange was the approach followed. Instead of following Approach 1 they opted for Approach 2, listed below.

Approach 1 : Convert the output format of Standard report to XML, create a new RTF template and register a Data Definition and Template with XML Publisher.

Approach 2 : Convert the RDF report into XML Report and use this XML as a Data Template. Create a new XML Publisher report Data Definition with this XML as Data Template and create a new Template.

The problem with Approach 2 is that Oracle provides no tool to convert Reports 6i into XML Report and any other supporting APIs to handle associated PL/SQL logic embedded into RDF.

Enter Reports 9i (or higher) and XML Publisher 5.6.3. These two combinations of Oracle products provide tools to first convert RDF report into XML report and associated XML Publisher API to handle PL/SQL logic.

This is how it works.
-----------------------------
Oracle Reports contains both Data Model (logic) and Layout (presentation) in a single file. In XML Publisher they are stored separately. Therefore migration involves a 2 step process.

Step 1 RDF to XML format report : Oracle Reports 9i (or higher) comes with utility "rwconverter.exe". Use either Reports Designer or "rwconverter.exe" command line to convert RDF into XML format report.

From the Designer :


The following command executes “rwconverter.exe” utility, taking the source report,
XXTEST.rdf and converting it to an RDF-XML format that can be consumed by the
XML Publisher conversion utility.

Note the dtype must be specified as "xmlfile".
\BIN>rwconverter batch=yes source= c:\XXTEST.rdf
dest= c:\XXTEST.xml dtype=xmlfile overwrite=yes


This would create an RDF-XML file, with all the PL/SQL logic and presentation logic.

Step 2.1 Data Model Migration :
XML Publisher 5.6.3 provides a Java API Package "oracle.apps.xdo.rdfparser". This package has "DataTemplateGenerator" class that creates a DataTemplate from the XML report file. If report has triggers this will generate '.pks' and '.pkb' file for that trigger which needs to be compiled into the Database

Example
javaw.exe oracle.apps.xdo.rdfparser.DataTemplateGenerator c:\XXTEST.xml

Output Files:
PL/SQL Package : XXTESTS.pls
PL/SQL Body : XXTESTB.pls
DataTemplate : XXTEST_template.xml

Here is a sample XML output file. Notice the "defaultPackage", "dataTrigger" etc that contain reference to PL/SQL API.


<?xml version="1.0" encoding="UTF-8" ?>
<dataTemplate name="XXTEST" defaultPackage="XXTEST" version="1.0">
<properties>
<property name="xml_tag_case" value="upper" />
</properties>
<parameters>
<parameter name="P_CONC_REQ_ID" dataType = "number"></parameter>
</parameters>
<lexicals>
</lexicals>
<dataQuery>
<sqlStatement name="Q_Main">
<![CDATA[
SELECT user_name, user_id
FROM xxuser
]]>
</sqlStatement></dataQuery>
<dataTrigger name="beforeReportTrigger" source="XXTEST.beforereport"/>
<dataStructure>
<group name="G_1" dataType="varchar2" source="Q_Main">
<element name="user_name" dataType="varchar2" value="SEGMENT_VALUE"/>
<element name="user_id" dataType="number" value="VALUE_SET"/>
</group>
</group>
<element name="CP_segment_name" dataType="varchar2" value="XXTEST.CP_segment_name_p"/>
<element name="CF_RUN_DATE" dataType="USDate" value="XXTEST.cf_run_dateformula()"/>
<element name="CF_NAME" dataType="varchar2" value="XXTEST.cf_nameformula()"/>
</dataStructure>
<dataTrigger name="afterReportTrigger" source="XXTEST.afterreport()"/>
</dataTemplate>


Step 2.2 Data Model Migration :
XML Publisher 5.6.3 API Package "oracle.apps.xdo.rdfparser" has "RTFTemplateGenerator" class to migrate the Oracle Reports layout to an XML Publisher RTF template. Because there is no support for PL/SQL in an RTF Template, the generator writes all Oracle Report format trigger code to the log file. This must later be implemented as XSL code

Example
javaw.exe oracle.apps.xdo.rdfparser.RTFTemplateGenerator c:\XXTEST.xml

Output Files:
RTF Template : C:\XXTEST.rtf
Log File : C:\XXTEST.log

That’s it we are done with the conversion.
Now that we have converted the RDF into XML publisher report, we simply need to use the Data Template and RTF to register with the XML Publisher.

Step 3 : Register a new Data Definition with XML Publisher Responsibility, with XML file generated as part of Step 2.2 as Data Template. Register the RTF template for the DD.

Step 4 : Create a Concurrent Report Program with Report output as XML and executable as "XDODTEXE". XDODTEXE is XML Publisher Data Template Executable Engine, which is a Java Concurrent Program.



That’s it. We have traced the steps involved to convert an existing RDF into an XML Publisher Report for 11.5.10.

I will write more on XML Publisher in my subsequent blogs.

Till then keep reading my blog and feel free to write your comments.

Cheers
~Neeraj