![Migrating Oracle Reports - JDAPI style 20188367001]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "Migrating Oracle Reports - JDAPI style 1")
Presenting at ODTUG can lead to unexpected things to happen. For instance people actually reading my paper and seeking advice with their migration issues. Whaw! In my paper – titled Practical JDAPI – I discussed how I the Java Development API (JDAPI) helped in migrating a forms 6i client-server application to 10g webforms. During that migration project I developed a number of tools that help me with the boringsome tasks that acompany any migration: reattaching libraries to modules, correcting lower/upper case in file names, mass-creating and mass-modifiing objects (ie. add scrollbar to each and every document window) etc. In my ODTUG-paper I did not describe migrating Oracle Reports, as it was beyond the scope of the migration project. Further, you can’t use JDAPI on Reports, so an alternative approach must be taken.
Triggered by a question of a reader who asked me how to migrate Oracle Reports the following apporach could be helpfull. Attached libraries in Reports offer the same issues as attached libraries in forms modules: case-sensitivity and full path inclusion resulting in errors after deployment (like, ‘can not find library being called’). Fortunately, in the xml representation of the report it is clearly visible how the libraries are attached: if you see a full path and file name including the PLL extension, it means that the library is attached with full path. If you see only the library file name, without path and without extension, the library is attached without it’s path: this is good!. The file name also shows the exact case in which the library is attached. Knowing this, reattaching the library in the proper, desired way is easy.
The first hurdle to take is to convert any Reports module to a readeable file. There is an executable called rwconverter.exe that is helpfull. With this executable a Reports module in any format/extension can be converted to another format. In fact it allows you to convert an rdf file to an xml file. Once you have the xml file, you can transform it with a XSL-T stylesheet. This would be the second hurdle. In case you can’t get the problem solved using a stylesheet, you could write a Java program that parses your report.xml, does difficult things with it and writes the resulting xml back to a file.
The final hurdle is to convert the transformed stylesheet back to an rdf or rep file and you’re done. Migration of a whole lot of Report modules involves putting the separate calls to rwconverter and the transformation in a batch-script.
Here is an example how to invoke the rwconverter (see also Reports help, search for rwconverter):
rwconverer scott/tiger@orcl stype=rdffile source=myreport.rdf dtype=xmlfile dest=myreport.xml batch=yes overwrite=yes
After the report.xml is generated, you can translate the xml file against a xsl-stylesheet, like shown below. Here is an example of a stylesheet that lists all attached libraries in a reports module and renders the result in a html page. Such stysheets are quite conveniant, because the result is far more readable than browsing through the reports.xml generated.
<?xml version='1.0' encoding='UTF-8'?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:strip-space elements="*"/>
<xsl:output indent="yes" method="html"/>
<xsl:template match="/">
<html><head><title>List of attached libraries</title></head>
<body>
<xsl:apply-templates select="report"/>
</body></html>
</xsl:template>
<xsl:template match="report">
<xsl:text>Report name: </xsl:text>
<xsl:value-of select="@name"/>
<xsl:text>.rdf</xsl:text>
<hr width="50%"/>
<ul>
<xsl:for-each select="attachedLibrary">
<xsl:apply-templates select="."/>
</xsl:for-each>
</ul>
</xsl:template>
<xsl:template match="attachedLibrary">
<li>
<xsl:value-of select="@name"/>
</li>
</xsl:template>
</xsl:stylesheet>
The next example shows an xsl-stylesheet that transforms the file names of the attached libraries to lowercase, truncates the path names and extension. Also important: all other elements, attributes, namespaces and cdata-sections are copied to the result unchanged! Copiing the namespaces and cdata-sections unchanged is important because otherwise the report.xml can’t be translated back into a rdf file. The identity transformation does exactly this.
<?xml version='1.0' encoding='UTF-8'?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output indent="yes" method="xml"
cdata-section-elements="xmlSettings textSource webSource"/>
<xsl:variable name="upperCase" select="ABCDEFGHIJKLMNOPQRSTUVWXYZ"/>
<xsl:variable name="lowerCase" select="abcdefghijklmnopqrstuvwxyz"/>
<!-- identity transformation, copy everything 1-on-1 out of source to destination -->
<xsl:template match="@*|node()">
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
</xsl:copy>
</xsl:template>
<!-- create a new attribute for the filename of the attached library -->
<xsl:template match="attachedLibrary">
<xsl:element name="attachedLibrary">
<xsl:attribute name="name">
<xsl:choose>
<!-- on windows use a backslash instead of forward slash in the next 2 lines -->
<xsl:when test="contains(translate(@name, $upperCase, $lowerCase),'/')">
<xsl:value-of select="substring-before(substring-after(translate(@name, $upperCase, $lowerCase), 'forms/'), '.PLL')"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="translate(@name, $upperCase, $lowerCase)"/>
</xsl:otherwise>
</xsl:choose>
</xsl:attribute>
</xsl:element>
</xsl:template>
</xsl:stylesheet>
Besides changing the file name of attached libraries you can change almost everything in the xml file. As long as you conform to the document type definition (DTD) of the reports module. A DTD file (reports.dtd) can be found in your Development Suite (9i and 10g), under directory $ORACLE_HOME/reports/dtd.
And if XSL is not going to do the job for you, you could try to write a Java program that parses the xml file, do some intelligent search and replace action – either using XPath or using plain text find-replace’s or a combination of the two – and finally write the result to an xml file so it can be converted back to an rdf file.
Oracle Reports – the JDAPI way, sort of.
Hi Harm
Only links I am aware of are at http://odtug2.fatcity.com/desktop2005/
Mark
Mark, I’d like to read your paper, can you please add a hyperlink where
to download it?
Regarding the CDATA sections, my stylesheet handles the cdata sections
listed in the cdata-section-elements attribute correctly because of the cdata-section-elements attribute in the xsl:output.
Hoi Harm,
Mooi uitgediept. Het gebruik van XSL stylesheets voor zoeken en vervangen is natuurlijk heel leuk, maar met Unix tools (grep, sed, perl) kun je volgens mij sneller tot het gewenste resultaat komen. Nog een leuke utility is textrepl (zie http://cvs.sourceforge.net/viewcvs.py/transferware/tw/common/src/perl/textrepl.pl). Hiermee kun je interactief zoeken en vervangen in tekstbestanden met behulp van Perl.
A few points to note
1) You can apply your xml customisations as a single command e.g.
rwconverter SOURCE=myreport.rdf STYPE=RDFFILE DEST=..\new\myreport.rdf DTYPE=RDFFILE CUSTOMIZE=(coverpage.xml,watermark.xml) OVERWRITE=YES BATCH=YES
2) There are a number of bugs with associated with the rwconverter including:
Bug No.2871411 – Group filter code lost
Bug No.2989188 – Custom tags lost for 2nd level groups
Worked in 6i, fails in 9i, fixed in 10g
Bug No.3418893 – cannot modify program unit using rwconverter
3) I suspect your xsl stylesheet will have issues with CDATA sections such as those enclosing select statements, so your stylesheet will need to be far more sophisticated than the one you have shown. I would recommend you change it to a customisation file which only modifies the attached library section.
I did a paper at the ODTUG Desktop Conference 2005 which goes into further details (assume members can download).
Hope this helps
Mark