Oracle BI Publisher provides a utility for converting reports from RDF reports to BI Publisher reports using RTF templates. This utility can come really handy in migration projects involving large number of oracle reports. Though this process greatly reduces time and effort, all the migrated reports have to be code reviewed to ensure there is no buggy generated code. I will discuss at the end of the article of the scenarios where that can happen.
In Oracle Reports, the data model (SQL query or extraction logic) and layout are contained in a single file. In Oracle BI Publisher the data model and the layout are separate objects, which is a major feature of XML Publisher architecture. It gives a flexible to use the same data model and generate multiple presentations using templates.
The conversion utility generates several files that will make up your report in Oracle BI Publisher. In most cases this will include a PL/SQL specification and body that you will need to create in the database that contains the report trigger code. The utility will also generate a report definition file and a layout template file that you will upload to the Oracle BI Publisher repository.
Once uploaded, test the report to ensure that the output is as expected and make any changes to the report as needed. Some reports will contain structures that the utility cannot convert. These must be manually implemented in the converted reports.
The overall flow for the conversion process is as follows:
1. Run the conversion utility.
2. Load the PL/SQL package into the database.
3. Upload the report to the Oracle BI Publisher repository.
4. Test the report and check the conversion log files to identify any manual modifications needed to complete the conversion.
Pre-requisites:
This section describes the steps required before running the conversion utility.
1. Oracle Reports in XML format:
During the conversion process, the source reports must eventually be in Oracle Reports XML format, which is supported by Reports 9i and above. If your reports are not in XML format, you can use “rwconverter” executable which comes with Reports Installation. rwconverter is available in bin folder in the report installation folder.
I will be using Oracle Developer 10g for this article.
- Mandatory jar/zip files:
To run the conversion utilities you need the following jar files in your CLASSPATH:
| Library | Function |
| xdocore.jar | Core BIP/XMLP library |
| aolj.jar | Oracle EBS library |
| xdoparser.jar | XML parser and XSLT 2.0 engine |
| xmlparserv2-904.jar | Main XML parser/XSLT engine. Available from the JAVA_TOP directory |
| versioninfo.jar |
|
| collections.jar | Java Collections |
| j5472959_xdo.zip | XDO/ORACLE XML Publisher OA Rollup Patch 5.6.3 |
Though Oracle documentation mentions only few of the below jar files, you require the comprehensive list to use the conversion utility. You can get these libraries from JAVA_TOP if your EBS has patch 5472959 installed. Alternatively you can also download the patch from metalink, unzip it in your local machine and put these files in your CLASSPATH individually.
I also got the above files from installing BI Suite Enterprise Edition 10.1.3.3.2 (biee_windows_x86_101332_disk1.zip).The OTN downloads link for BI Suite Enterprise Edition is here. These files are available under $BIPUB_HOME\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib folder.
Include these jar files in CLASSPATH environment variable.
Conversion Process:
This report conversion can be done using two approaches; rather I would say two different APIs.
- Using combination of DataTemplateGenerator and RTFTemplateGenerator APIs.
- Using BIPBatchConversion API.
Actually BIPBatchConversion is a wrapper around DataTemplateGenerator and RTFTemplateGenerator APIs.
I will use a simple sales report XXONT_SO with just one field in the layout, for conversion of BI Publisher report.
I will convert the report using the first approach. DataTemplateGenerator API converts the Oracle Reports data model to an XML Publisher data template. RTFTemplateGenerator API converts the Oracle Reports layout to an XML Publisher RTF template.
Approach 1: Using DataTemplateGenerator and RTFTemplateGenerator Combo
Following is step-by-step approach for the conversion:
- Convert RDF report to XML report.
I’ve created a folder ‘output’ to place all the files from the conversion of oracle report.
Running rwconverter from command prompt, to create xm l version of oracle report.
rwconverter batch=yes source=D:\bip\rmu\XXONT_SO.rdf dest=D:\bip\rmu\output\XXONT_SO.xml dtype=xmlfile overwrite=yes
This creates XXONT_SO.xml in output folder.
- Creating Data template from Oracle Report using DataTemplateGenerator
Use the below command at command prompt.
java.exe -verbose -cp aolj.jar;xdoparser.jar;collections.jar;j5472959_xdo.zip;versioninfo.jar;xmlparserv2-904.jar oracle.apps.xdo.rdfparser.DataTemplateGenerator D:\bip\rmu\output\XXONT_SO.xml
The output lists the data template name and any PL/SQL packaged code created.
Common errors here would be missing of the required jar files. This step generated the Data Template (XXONT_SO_template.xml) and PL/SQL package (XXONT_SOS.pls and XXONT_SOB.pls) for any report trigger code.
Data template XXONT_SO_template.xml generated looks like below:
- Creating RTF template from Oracle Report using RTFTemplateGenerator.
Use the below command at the command prompt.
java.exe -verbose -cp aolj.jar;xdoparser.jar;collections.jar;j5472959_xdo.zip;versioninfo.jar;xmlparserv2-904.jar oracle.apps.xdo.rdfparser.RTFTemplateGenerator D:\bip\rmu\output\XXONT_SO.xml
The output lists the rtf template name and log created.
This step generated the RTF Template (XXONT_SO.rtf)
XXONT_SO.rtf with the form fields, in concurrence with oracle report layout.
BIPBatchConversion is a wrapper around rwconverter, DataTemplateGenerator and RTFTemplateGenerator APIs. We can use this single utility instead of the above approach.
If your source reports are not in Oracle Reports XML format, this utility will do this conversion automatically. However, this requires Oracle Reports Designer 9i or later on the same machine where you will do the conversion so that the Oracle BI Publisher conversion utility can call the rwconverter executable to get the reports into Oracle Reports XML format.
To get Oracle Reports Designer, download Oracle Developer Suite 10g (10.1.2.0.2) from: http://www.oracle.com/technology/software/products/ids/index.html and install Reports Designer.
If your reports are already in Oracle Reports XML format, there is no need to install Oracle Reports.
It takes the following parameters:
· -source — (required) Source directory for Oracle Reports files. All reports must be in the same format - either RDF or XML.
· -target — (required) Target directory to create Oracle BI Publisher report objects. This includes the Oracle BI Publisher Report file (.xdo), the layout template file (.rtf), the PL/SQL package, and log file.
· -oraclehome — (optional) If your reports are in Oracle Reports XML format do not specify this parameter. If your reports are not in Oracle Reports XML format, specify the Oracle home path where Oracle Report Designer (9i or later version) is installed. BIPBatchConversion assumes that rwconverter is contained in the bin directory beneath the Oracle Home path.
BIPBatchConversion requires rwconverter from Oracle Reports to convert the report from RDF format to XML format.
· -debug — (optional) To run the utility in debug mode and write debug statements to the log file.
Command Line Usage:
java ... BIPBatchConversion [-debug] -source SourceDirectory -target TargetDirectory [-oraclehome OracleHomePath]
For our example, I issue the below command at command prompt and generate the same set of files as in the first approach. Note that I didn’t specify the report name, this utility will convert all reports under the source folder.
java.exe -cp aolj.jar;xdocore.jar;collections.zip;j5472959_xdo.zip;versioninfo.jar;xmlparserv2-904.jar oracle.apps.xdo.rdfparser.BIPBatchConversion -source D:\bip\rmu -target D:\bip\rmu\bipoutput -oraclehome D:\oracle\Ora10g –debug
Tweaking/Reviewing the converted report:
When converting a more complex Oracle Reports report, the Data Template or PL/SQL may contain minor errors and require manual correction.
- The conversion utility will move all formula columns to the select clause of the SQL query in the data model. In most cases this will not cause a problem. If any of the arguments to the formula column is a summary column that belongs to same Data Source/Data Query, this will not work because the summary column will not be calculated at the time the query is executed.
- The conversion utility does not convert any PL/SQL format trigger logic present in the report. Instead the conversion utility writes all the format trigger code to a log file. You will need to implement any corresponding PL/SQL logic as XSL code. As the majority of formatting use simple ‘if’ logic, they can be implemented separately in RTF template using IF/ELSE.
- Minor errors in PL/SQL generated code, if unnoticed can turn into a major mishap. For Example: If a function exists in oracle report which fetches Item Description from a given item_id and organization_id report parameters.
Assume the function looks like below
The code is using organization_id and item_id report parameters, to fetch the item description. Now when this report is converted into BI Publisher report, the pl/sql package has a similar function defined with parameters for item_id and organization_id. Also it uses the name identifiers for the function parameters, which might cause problem if unnoticed.
The generated packaged code for above function, may looks like below
For untrained eye, the above code looks perfectly fine. But the code fails due to the highlighted condition which will always return TRUE and the code goes into exception block because of multiple rows fetched from the select query. So care should be taken for code reviewing the generated code and compare the output of BI Publisher with the oracle report output.
Once the output files are generated, follow the below steps to register and run the BI Publisher report:
- Upload PL/SQL package to the database.
- In XML Publisher Administrator responsibility, create data definition and assign the data template file.
- Create RTF template and assign rtf template file.
- Create concurrent program with short name same as above data definition name and assign XDODTEXE as executable.
- Run the concurrent program and test the output with the original oracle report output.
Subscribe to this comment's feedwritten by ramakrishna a , September 10, 2008
written by Akash Jain , September 11, 2008
When i tried to relpcate the above steps ,i got an errror as
exception in thread "main" java.lang.NoClassDefFoundError: oracle/apps/xdo/rdfparser/DataTemplateGenerator
As such i am not able to generate the pl/sql code .
Can you please help.
written by kishore Ryali , September 11, 2008
Have you included all the mandatory jar/zip files? DataTemplateGenerator is found in j5472959_xdo.zip, you can download this patch 5472959 from metalink and include in classpath.
Thanks
Kishore
written by ellya , September 16, 2008
Exception in thread "main" java.lang.NoClassDefFoundError: oracle/apps/xdo/rdfpa
rser/BIPBatchConversion..
can u please tell me where to place these files
xdocore.jar
Core BIP/XMLP library
aolj.jar
Oracle EBS library
xdoparser.jar
XML parser and XSLT 2.0 engine
xmlparserv2-904.jar
Main XML parser/XSLT engine. Available from the JAVA_TOP directory
versioninfo.jar
collections.jar
Java Collections
j5472959_xdo.zip
XDO/ORACLE XML Publisher OA Rollup Patch 5.6.3
thanks
ellya
written by kishore Ryali , September 16, 2008
You can place required jar/zip files in a folder and add the folder to CLASSPATH env variable.
Kisore
written by Shrawani , September 21, 2008
While searching thorugh forums for information on BI Publisher on Siebel 8.1, I came across your blog.This article is very good,the way you have provided detailed information,I liked it very much. I was wondering if you have some information or blog dedicated to BI Publisher on Siebel environment or Migration of ACtuate Reports to BI publisher. I would be glad to get your reply.
Thanks.
written by lavanya y , September 29, 2008
written by venky , November 03, 2008
When i run oracle.apps.xdo.rdfparser.RTFTemplateGenerator API, i am not getting the output files .rtf and .log files. i got below errors. I don;t know how to fix them
Any help is appreciated.
[Loaded java.text.NumberFormat$Field from D:OraHome_1jre1.4.2lib
t.jar]
[110308_072136396][][ERROR] Table structure is not correct. start/end cell does
not match.
[110308_072136396][][ERROR] Table structure is not correct. Cannot start a table
in a row.
[110308_072136396][][ERROR] Table structure is not correct. start/end row does n
ot match.
java.lang.StringIndexOutOfBounds*xception: String index out of range: 133
[Loaded java.lang.StackTraceElement from D:OraHome_1jre1.4.2lib
t.jar]
at java.lang.String.substring(Unknown Source)
Venkat
written by ap , March 12, 2009
Thanks in advance.
written by Ike Wiggins, Minneapolis, MN , June 11, 2009
http://bipublisher.blogspot.com/2009/05/bi-publisher-reports6i-to-bip.html
written by SandeepReddy , November 29, 2009
Please help me ,Iam very to new Oracle Apps.I did simple BackorderReport(RDF)..I'm migrating RDF to BIP..I was followed the two approaches .Iam almost close to last step .Which above mention in this article.I have Mandatory jar/zip file in my local system...
First Approach:
Please see my command prompt>>C:Documents and Settingssamull>java.exe -verbose -cp aolj.jar;xdoparser.jar;co
llections.jar;j5472959_xdo.zip;versioninfo.jar;xmlparserv2-904.jar oracle.apps.x
do.rdfparser.DataTemplateGenerator C:oracleBIoutputBackorder_Final.xml
Error>>Could not find the main class: oracle.apps.xdo.rdfparser.DataTemplateGenerator.
Program will exit.
Second Approach>>>>>
C:Documents and Settingssamull>java.exe -cp aolj.jar;xdocore.jar;collections.z
ip;j5472959_xdo.zip;versioninfo.jar;xmlparserv2-904.jar oracle.apps.xdo.rdfparse
r.BIPBatchConversion -source C:OracleBIoutput -target C:OracleBIoutput inal
Error>>Could not find the main class: oracle.apps.xdo.rdfparser.DataTemplateGenerator.
Program will exit.
I very happy some body could help me.But one thing I can say even zero knowledge in Oracle Apps by seeing this website apps2fusion we can learn easily like me ..
Thanks,
Sandeep
Email: This e-mail address is being protected from spambots. You need JavaScript enabled to view it
written by kishorenine , December 07, 2009
This is the command I am using for DATA Template generatation..
java.exe -verbose -cp C:OracleBIoc4j_bij2eehomeapplicationsxmlpserverx mlpserverWEB-INFlibaolj. jar;C:OracleBIoc4j_bij2eehomeapplicationsxmlpserv erxmlpserverWEB-INFlibxdocore. jar;C:OracleBIoc4j_bij2eehomeapplicationsxmlpserv erxmlpserverWEB-INFlibxdoparser. jar;C:OracleBIoc4j_bij2eehomeapplicationsxmlpserv erxmlpserverWEB-INFlibcollections. jar;C:OracleBIoc4j_bij2eehomeapplicationsxmlpserv erxmlpserverWEB-INFlibversioninfo. jar;C:OracleBIoc4j_bij2eehomeapplicationsxmlpserv erxmlpserverWEB-INFlibxmlparserv2.jar;C:Documents and Settings201927Desktopp5472959_11i_WINNT p5472959_11i_WINNT5472959 j5472959_xdo.zip oracle.apps.xdo.rdfparser.DataTemplateGenerator C:NarayananB2B_ReportsBOM ReportsCodesEMEA11i CodeMOTBOMBSR.XML
But this is showing following error...after including all the jar/zip file
Exception in thread "main" java.lang.NoClassDefFoundError: and
Caused by: java.lang.ClassNotFoundException: and
at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:18
at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:276)
at java.lang.ClassLoader.loadClass(ClassLoader.java:251)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)
[Loaded java.util.AbstractList$Itr from shared objects file]
[Loaded java.util.IdentityHashMap$KeySet from shared objects file]
[Loaded java.util.IdentityHashMap$IdentityHashMapIterator from shared objects fi
le]
[Loaded java.util.IdentityHashMap$KeyIterator from shared objects file]
[Loaded java.io.DeleteOnExitHook from shared objects file]
[Loaded java.util.LinkedHashSet from shared objects file]
[Loaded java.util.HashMap$KeySet from shared objects file]
[Loaded java.util.LinkedHashMap$LinkedHashIterator from shared objects file]
[Loaded java.util.LinkedHashMap$KeyIterator from shared objects file]
thanks
Kishore
written by kishore s , December 08, 2009
I am trying to convert oracle apps standard RDF Report(huge report)... this seems to very difficult for manual conversion...
written by Vijay1836 , February 03, 2010
I have successfully used the rwconverter utility to get the xml file. but after that when using this command i am getting error
java.exe -verbose -cp xdoparser.jar;collections.jar;j5472959_xdo.zip;versioninfo.jar;xmlparserv2-904.jar oracle.apps.xdo.rdfparser.DataTemplateGenerator C:TempoutputPOXRQSIN.xml
i have added following to my classpath
C:migrationlibaolj.jar;C:migrationlibcollections.jar;C:migrationlibj5472959_xdo.zip;C:migrationlibversioninfo.jar;C:migrationlibxdocore.jar;C:migrationlibxdoparser.jar;C:migrationlibxmlparserv2-904.jar;
still i am getting error C:migrationlibaolj.jar;C:migrationlibcollections.jar;C:migrationlibj5472959_xdo.zip;C:migrationlibversioninfo.jar;C:migrationlibxdocore.jar;C:migrationlibxdoparser.jar;C:migrationlibxmlparserv2-904.jar;
written by Rajeev Agrawal , May 07, 2010
Any help would be highly appriciated.
Cheers,
Rajeev
written by Ayodhya , October 05, 2010
[100510_052141612][][ERROR] Variable 'p_donor;' is missing....
[100510_052141615][][EXCEPTION] java.sql.SQLException: ORA-06550: line 2, column 39:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
written by Kawal , March 28, 2011
I have suceeded in converting .rdf file to .rtf,.xdo,.pls and other required files for BI Publisher migration.
In the oracle reports, we have used many in-built report packages like SRW, EXEC_SQL.
When I try to compile .pls files in the DB, I get errors because of the function which are availabe in the above mentioned in-built packages.
Need to ask u guys, Can we use these report packages in BIP. If so, then how can we deploy these packages.
Thanks & Regards,
KP.
written by RT , January 20, 2012
