Release 1 (9.0.1)
Part Number A88894-01
 Home |
 Solution Area |
 Contents |
 Index |
| Oracle9i Application Developer's Guide - XML Release 1 (9.0.1) Part Number A88894-01 |
|
This chapter contains the following sections:
Oracle9iAS (Application Server) Reports Services provides the following XML support:
Figure 14-1 shows the sharing of information with partners and how XML can be used to help send data in a more timely manner. With Oracle9iAS Reports Server, you can automatically generate XML files. A URL is all that is required to invoke an Oracle Report on the Web. By defining a Report module to query the data, suppliers can stream information back to the calling eCommerce application in real time.
Oracle Reports, or Oracle9iAS Reports Service as it is called now, is part of Oracle9i Application Server. It seamlessly integrates in the environment and the other services provided by the product.
The data model drives the structure of XML output for a report. XML output is not dependent on the visual layout.
DESFORMAT=XML to generate XML output.
You may need to send a report to a B2B partner in XML. Figure 14-2 shows an example of a report in XML. This is obtained by changing one parameter that tells Reports Server to output the report in XML instead of HTML.
Figure 14-3 shows the results of applying an XSL stylesheet to the same XML report shown in Figure 14-2. In this case, the example report of data is needed in HTML.
Oracle9iAS Reports Services allows modifications to be externalized into a separate 'customization' file, rather than having to create unique versions of each report. Report outputs can be customized for specific users or groups without changing the original report definition.
A customization file is a report definition that is applied to an existing report (.RDF or .XML). It can change certain characteristics of existing report objects, such as the field's date format mask or background color. A customization file can also be used to add entirely new objects to another report.
To apply an XML report definition to an .RDF or .XML file at runtime, use either of the following:
RWCLI60, RWRUN60, RWBLD60, RWCON60, and URL report requests.
SRW.APPLY_DEFINITION built-in (a supplied PL/SQL package)
The following command line sends a job request to Oracle9iAS Reports Services that applies an XML report definition, emp.xml, to an .RDF file, emp.rdf:
rwcli60 report=emp.rdf customize=e:\myreports\emp.xml userid=username/password@mydb destype=file desname=emp.pdf desformat=PDF server=repserver
If you were using Oracle9iAS Reports Services Runtime, then the equivalent command line would be:
rwrun60 userid=username/password@mydb report=emp.rdf customize=e:\myreports\emp.xml destype=file desname=emp.pdf desformat=PDF
When testing your XML report definition, it is sometimes useful to run your report requests with additional arguments to create a trace file. For example:
tracefile=emp.log tracemode=trace_replace traceopt=trace_app
The trace file provides a detailed listing of the creation and formatting of the report objects.
You can apply multiple XML report definitions to a report at runtime by providing a list with the CUSTOMIZE command line argument. The following command line sends a job request to Oracle9iAS Reports Services that applies two XML report definitions, emp0.xml and emp1.xml, to an .RDF file, emp.rdf:
rwcli60 report=emp.rdf customize="(e:\corp\myreports\emp0.xml, e:\corp\myreports\emp1.xml)" userid=username/password@mydb destype=file desname=emp.pdf desformat=PDF server=repserver
If you are using Oracle9iAS Reports Services Runtime, then the equivalent command line is:
rwrun60 report=emp.rdf customize="(e:\corp\myreports\emp0.xml, e:\corp\myreports\emp1.xml)" userid=username/password@mydb destype=file desname=emp.pdf desformat=PDF
To apply an XML report definition to an .RDF file in PL/SQL, use the SRW.ADD_DEFINITION and SRW.APPLY_DEFINITION PL/SQL supplied packages (built-ins) in the Before Form or After Form trigger.
SRW.APPLY_DEFINITION PL/SQL supplied package in the Before Form or After Form triggers of the report:
SRW.APPLY_DEFINITION ('d:\orant\tools\doc60\us\rbbr60\cond.xml');
When the report is run, the trigger executes and the specified XML file is applied to the report.
SRW.ADD_DEFINITION before applying it using SRW.APPLY_DEFINITION.
Using Oracle Reports Developer, in Reports6i and higher, you can change the appearance and content of a report at runtime. To do this, merge the report definition files (RDFs), built from XML tags, with existing .RDF files at runtime, and then execute the combination. You can use XML report definitions for other tasks such as:
RWCON60). RWCON60 is a PL/SQL supplied package (Built-in function).
The following examples show you how to modify reports using XML at runtime, including:
Before you can use the following examples 1 through 5, you must create a default report (a tabular report - "select * from emp", select all columns, "Corporate 1" template) that the XML customizations are applied to. You can call it anything you want, but in the following examples, we refer to it as emp_report.
To activate these modifications, it is best to create a file called modify.xml, and then copy the XML code of the current example into it. This way, you can always use the following same command line to run the report and modifications:
rwrun60 report=emp_report userid=scott/tiger customize=modify.xml
If you modify the scripts listed below, keep all XML specific restrictions in mind, especially the case-sensitivity of XML.
The following example modifies the field F_EMPNO and sets its color to red.
<report name="emp_report" DTDVersion="1.0"> <layout> <section name="main"> <field name="F_EMPNO" source="EMPNO" textColor="red"/> </section> </layout> </report>
Example 2 changes the text-color of field F_EMPNO to red and sets the date-format of field F_HIREDATE to german notation.
<report name="emp_report" DTDVersion="1.0"> <layout> <section name="main"> <field name="F_EMPNO" source="EMPNO" textColor="red"/> <field name="F_HIREDATE" source="HIREDATE" formatMask="dd.mm.yyyy"/> </section> </layout> </report>
This example shows you how to modify boilerplate text objects. Normal report layout elements are enclosed by the <layout>...</layout> tags, while the boilerplate and logic elements are enclosed by the <customize>...</customize> tags.
<report name="emp_report" DTDVersion="1.0"> <layout> <section name="main"> <field name="F_EMPNO" source="EMPNO" textColor="red"/> <field name="F_HIREDATE" source="HIREDATE" formatMask="dd.mm.yyyy"/> </section> </layout> <customize> <object name="B_HIREDATE" type="REP_GRAPHIC_TEXT"> <properties> <property name="textSegment"> Anst.Dat. </property> </properties> </object> </customize> </report>
As any other element of a report definition, the query is an element that can be customized by the XML file. In this example, the query used when creating the report (select * from emp) is replaced by one using a WHERE-clause (select * from emp where deptno = 10).
Note that you have to use the same datasource name as originally used in the report definition done by the wizard. Otherwise, there would be another datasource created but not used, as there is no association to any repeating frame.
<report name="emp_report" DTDVersion="1.0"> <data> <dataSource name="Q_1"> <select> select * from emp where deptno = 10 </select> </dataSource> </data> <layout> <section name="main"> <field name="F_EMPNO" source="EMPNO" textColor="red"/> <field name="F_HIREDATE" source="HIREDATE" formatMask="dd.mm.yyyy"/> </section> </layout> <customize> <object name="B_HIREDATE" type="REP_GRAPHIC_TEXT"> <properties> <property name="textSegment"> Anst.Dat. </property> </properties> </object> </customize> </report>
Besides changing the visual attributes of a report, you can easily modify its logic by customizing the PL/SQL code as well. In this example, the report we created does not contain format-triggers. By applying this XML file, a trigger is added to field, F_SAL, that hides the field if the value of :SAL is less than 2500.
<report name="emp_report" DTDVersion="1.0"> <layout> <section name="main"> <field name="F_EMPNO" source="EMPNO" textColor="red"/> <field name="F_HIREDATE" source="HIREDATE" formatMask="dd.mm.yyyy"/> <field name="F_SAL" source="SAL" formatTrigger="SAL_FORMAT"/> </section> </layout> <programUnits> <function name="SAL_FORMAT"> <![CDATA[ function sal_format return boolean is begin if :SAL > 2500 then return (true); else return (false); end if; end; ]]> </function> </programUnits> <customize> <object name="B_HIREDATE" type="REP_GRAPHIC_TEXT"> <properties> <property name="textSegment"> Anst.Dat. </property> </properties> </object> </customize> </report>
Reports' ability to externalize modifications simplifies upgrading and the need to make an application site-specific. You can apply an XML definition and save the resultant definition as a new unique module. This also facilitates updates or upgrades without having to open each file in Reports Builder to make changes.
To update a large number of reports, you can use the CUSTOMIZE command line argument with RWCON60,the Reports Conversion Tool, to perform modifications in batch. Batch modifications are useful when making repetitive changes to a large number of reports, for example, when changing a field's format mask. From Oracle Report Builder, you can run RWCON60 once and make the same change to a large number of reports.
The following example applies two XML report definitions, translate.xml and customize.xml, to three .RDF files, inven.rdf, inven2.rdf, and manu.rdf.
It saves the revised definitions to new files:
nven1_new.rdf
inven2_new.rdf
manu_new.rdf
rwcon60 username/password@mydb stype=rdffile source="(inven1.rdf, inven2.rdf, manu.rdf)" dtype=rdffile dest="(inven1_new.rdf, inven2_new.rdf, manu_new.rdf)" customize="(e:\apps\trans\translate.xml, e:\apps\custom\customize.xml)" batch=yes
For example, ERP vendors may want each customer to individualize their own reports. This requires modifying fonts, colors, and so on. By using the Reports XML based customization, vendors can make changes like this to the entire application in one step.
To do so, simply apply the XML customization files using RWCON60 and create a new RDF or REP file out of it, like you an see in the example above. This way, you can create customized applications for each customer without the problem of maintaining different versions of a report. You only have one master-report containing your basic layout and maybe placeholder objects for customer-specific things like logos, company names. And then you apply a customization file for each customer building their own report.
Creating multi-language reports is always a challenging task. Developing a base report and then translating it for all supported languages can cause maintenance problems. With Oracle9iAS Reports Services' XML customization feature, this is easily done.
You create your report in the base-language and then apply different XML customization files containing language specific settings, such as, label text, data-format, and numeric formats. This way you can create different language versions of your report easily.
This example shows how you can easily create different language versions from one report definition. You no longer have to do heavy coding or multiple versions of the same report. Simply create one layout and localize it by applying a "language-XML".
The example modifies emp_report to have a German boilerplate and date-formats. You can easily change this to your local language. Simply replace the German terms by ones used in your language and you have localized the report.
<report name="emp_report" DTDVersion="1.0"> <layout> <section name="main"> <field name="F_EMPNO" source="EMPNO" textColor="red"/> <field name="F_HIREDATE" source="HIREDATE" formatMask="dd.mm.yyyy"/> <field name="F_DATE1_SEC2" source="Current Date" formatMask="dd.mm.yyyy"/> </section> </layout> <customize> <object name="B_EMPNO" type="REP_GRAPHIC_TEXT"> <properties> <property name="textSegment"> Pers.No. </property> </properties> </object> <object name="B_ENAME" type="REP_GRAPHIC_TEXT"> <properties> <property name="textSegment"> Name </property> </properties> </object> <object name="B_JOB" type="REP_GRAPHIC_TEXT"> <properties> <property name="textSegment"> Pos. </property> </properties> </object> <object name="B_MGR" type="REP_GRAPHIC_TEXT"> <properties> <property name="textSegment"> Vorges. </property> </properties> </object> <object name="B_HIREDATE" type="REP_GRAPHIC_TEXT"> <properties> <property name="textSegment"> Anst.Dat. </property> </properties> </object> <object name="B_SAL" type="REP_GRAPHIC_TEXT"> <properties> <property name="textSegment"> Geh. </property> </properties> </object> <object name="B_COMM" type="REP_GRAPHIC_TEXT"> <properties> <property name="textSegment"> Prov. </property> </properties> </object> <object name="B_DEPTNO" type="REP_GRAPHIC_TEXT"> <properties> <property name="textSegment"> Abt. </property> </properties> </object> <object name="B_DATE1_SEC2" type="REP_GRAPHIC_TEXT"> <properties> <property name="textSegment"> Stand vom </property> </properties> </object> </customize> </report>
If your application requires dynamic switching of languages, you can also consider applying the customization at runtime.
Another use of XML report definitions is to make an entire report definition in XML that can be run independently of another report. The advantage of this is that you can build a report without using the Oracle Report Builder. In fact, you could even use your own front end to generate the necessary XML and allow your users to build their own reports dynamically.
With Oracle9iAS Reports Services you can also create a report in Report-Builder, save it in XML format so that you have a starting point. You can then modify the XML or use it as template for an application that creates the XML.
This example requires an empty RDF file. Just create an empty report, and save it as an empty .rdf. You then apply the following XML that contains the needed modifications to create a report out of XML definitions only.
rwrun60 report=empty userid=scott/tiger customize=modify.xml
This example creates a simple report that displays the columns EMPNO, ENAME, SAL, and COMM using the template corp1.tdf. This report looks exactly like one created using the report-wizard.
<report name="emp_report" DTDVersion="1.0"> <data> <dataSource name="Q_EMP"> <select> select empno, ename, sal, comm from emp </select> </dataSource> </data> <layout> <section name="main"> <groupLeft name="M_emp" template="corp1.tdf"> <group> <field name="F_EMPNO" source="empno"/> <field name="F_ENAME" source="ename"/> <field name="F_SAL" source="sal"/> <field name="F_COMM" source="comm"/> </group> </groupLeft> </section> </layout> </report>
Once you have created your XML report definition, you can use it in the following ways.
.RDF or other .XML files at runtime by specifying the CUSTOMIZE command line argument or the SRW.APPLY_DEFINITION built-in.
To run an XML report definition by itself, send a request with an XML file specified in the REPORT argument. You can do this in the following ways:
rwcli60 userid=username/password@mydb report=e:\corp\myreports\emp.xml destype=file desname=emp.pdf desformat=PDF server=repserver
rwrun60 userid=username/password@mydb report=e:\corp\myreports\emp.xml destype=file desname=emp.pdf desformat=PDF
When running an XML report definition in this way, the file extension must be .XML. You could apply an XML customization file to this report using the CUSTOMIZE argument.
In Reports 9i, besides using RDF and XML, you can also use Java Server Pages (JSP), as a format for saving a report. Inside the JSP, report elements such as data-model and paper-layout, are stored in XML format.
Depending on what you want to do with the JSP, it may contain only the data-model and the web-source, or it may also contain the paper-layout. In this case you can produce, for example, a PDF document from the same file.
Oracle9iAS Reports Services introduces the concept of a pluggable data source (PDS). PDS enables you to create interfaces to your own data sources, hence allowing reports to access this data and use it together with data from other PDSs in a single report.
Through a published interface, Oracle9iAS Reports Services communicates with the PDS and uses it to fetch data from the specified source. Reports PDSs are transparent to the user. The Reports pluggable data sources (PDS) can be used side-by-side in the same data model and linked together.
The PDS is written in JAVATM and then linked into Oracle9iAS Reports Services using the configuration files.
The XML-PDS is one of the PDSs shipped with Oracle9iAS Reports Services. It enables you to access XML-data from a file or live stream from the Internet. The structure of the XML data must follow a DTD (document type definition) or XSD (XML schema definition). The advantage of using the XML schema is:
As the configuration-files for Oracle 9i Application Server - Reports Service became more and more complex, their format has moved to XML for easier modification and readability.
Needing to store configuration information, the XML-PDS and the JDBC-PDS both use XML-files for storing their preference settings.
The configuration file for the server has also moved to XML format. When you migrate from an older version, such as, Reports 6i, the server will read the old configuration file and create a file in XML format for you.
Here is an example of a simple configuration file:
<?xml version = '1.0' encoding = 'ISO-8859-1'?> <!DOCTYPE server PUBLIC "-//Oracle Corp.//DTD Reports Server Configuration 9i//EN" "file:/d:/orawin70/report70/server/jasmine.dtd"> <server> <cache class="oracle.reports.cache.RWCache"> <property name="cacheSize" value="50"/> <property name="cacheDir" value="d:\orawin70\report70\server\cache"/> </cache> <!--Please do not change the id for reports engine.--> <!--The class specifies below is subclass of _EngineImplBase and implements EngineInterface.--> <engine id="rwEng" class="oracle.reports.engine.EngineImpl" initEngine="1" maxEngine="1" minEngine="0" engLife="50" maxIdle="30" callbackTimeOut="60000"> <property name="cacheDir" value="d:\orawin70\report70\server\cache"/> </engine> <job jobType="report" engineId="rwEng"/> <log option="noJob"/> <trace traceOpts="trace_all" traceFile="foo.txt" traceMode="trace_replace"/> <connection maxConnect="20" idleTimeOut="15"> <orbClient id="RWClient" publicKeyFile="clientpub.key"/> <cluster publicKeyFile="serverpub.key" privateKeyFile="serverpri.key"/> </connection> <queue maxQueueSize="10000"/> <persistFile fileName="d:\orawin70\report70\server\demo-pc.c7.dat"/> </server>
The distribution capabilities in Oracle9iAS Reports Services have been enhanced dramatically. To fit these changes and make the management of the distribution file easier, it also now uses the XML format.
The new distribution files now look like the following:
<?xml version = "1.0" encoding = "UTF-8"?> <destination> <file id = "1" name = "Testfile.html" section = "main" format = "htmlcss"> <include src = ""/> </file> <mail id = "2" to = "test@myserver.com"> <attach format = "pdf" srctype = "report"> <include src = "test.pdf"/> </attach> </mail> </destination>
XML-PDS allows you to specify an XSQL Servlet file as the XML data source.
You can specify the URL (local or remote) of the XSQL file, as the data source URL for the XML- PDS. XML-PDS then sends the URL request to the Web Server. To process the XSQL file, the webserver noted in the URL, must be configured with XSQL Servlet. By identifying the specific extension of the file mentioned in the URL, such as .xsql in this case, (which can be configured in the Webserver), it invokes XSQL Servlet.
XSQL servlet has the information on the database connection in an XSQL Pages configuration file. It processes the given XSQL Page, sends the SQL query to the database through a JDBC interface, receives the resultset , and puts the resultset in XML format. XSQL Servlet sends this to XML-PDS. Hereafter XML-PDS treats this XML as any other XML data source and processes it inside the reports.
An XML Schema or DTD must be used as Data Definition when XSQL is used as Data Source.
In the ever-growing B2B environment, time has become a vital factor. Many businesses rely on just-in-time delivery to avoid large inventories and the need for large amounts of fixed capital. Hence, it is vital for merchants to know, what their suppliers have in stock at any moment.
To offer such a service, suppliers could provide their inventory-information on their web-page. However, in most cases this information is needed in a format that can be used for further processing; a format such as XML. Even more useful would be a tool that can produce a report in HTML, PDF, and XML, out of the same report. So, merchants can either:
In this case, Oracle 9i Application Server - Reports service offers an ideal solution. You can design a good looking report for creating HTML or PDF output, and at the same time use this to produce an XML stream.
Figure 14-4 shows an example of a report produced by Oracle9iAS Reports Services in HTML format.
Figure 14-5 shows an example report representing the same inventory data in Figure 14-4 but as an XML stream.
As merchants, you can use the inventory data provided by your suppliers and combine it with data your own inventory to create a virtual stock list.
Oracle9iAS Reports Services now also enables you to use:
To do so, you, ideally need the URL to your suppliers XML-stream and a DTD or XML schema definition that describes the data provided by the XML stream. For example, the DTD would look something like the following:
<?xml version='1.0' encoding='UTF-8' ?> <!ELEMENT INVENTORY (INVENTORY_ITEM+)> <!ELEMENT INVENTORY_ITEM ( ID , TITLE , SUBTITLE , IN_STOCK , BACK_ORDER , BACKORDER_AVAILABLE , ITEM_CANCELED_SINCE , PRICE_PER_UNIT , UNITS_PER_PACKAGING , PACKAGE_DESCRIPTION )> <!ELEMENT ID (#PCDATA)> <!ELEMENT TITLE (#PCDATA)> <!ELEMENT SUBTITLE (#PCDATA)> <!ELEMENT IN_STOCK (#PCDATA)> <!ELEMENT BACK_ORDER (#PCDATA)> <!ELEMENT BACKORDER_AVAILABLE (#PCDATA)> <!ELEMENT ITEM_CANCELED_SINCE (#PCDATA)> <!ELEMENT PRICE_PER_UNIT (#PCDATA)> <!ELEMENT UNITS_PER_PACKAGING (#PCDATA)> <!ELEMENT PACKAGE_DESCRIPTION (#PCDATA)>
As the PDS is transparent to you, different PDSs (in this case, SQL and XML PDSs), work seamlessly together in the data-model and can be handled as if they are SQL-ones (that is, joined together).
Figure 14-6 shows a data-model in Oracle Report Builder, with both the SQL and XML data sources linked together.
You can also create a layout using data from both the data sources together. In this case, the columns, 'virtual stock' and 'backorder' come from the XML-stream and represent data for this particular item in your one supplier's inventory. The data is fetched everytime the report is run using a URL to a report on the supplier's site. This produces an XML stream of your supplier's inventory data.
Figure 14-7 shows the finished report of inventory data that combines SQL data, for example, retrieved from the merchants own inventory, and data from a remote XML stream (virtual data), for example, of inventory data retrieved from a supplier.
We are working on AU/SG Year End Reporting which involve archiving and production of magtape files. Do you have information on EOY reporting?
Have you created any sites/documentation that explain how XML is actually being used for EOY reporting? And what it's being used for?
We are using DBIs in fast formulas to obtain a lot of the YE information. Can we use/access DBIs in XML?
You can output XML from Reports 6i by just changing a single parameter - DESFORMAT.
Instead of it being set to HTML or PDF,.... just change it to XML. This will work with your existing report, so you should not have to do anything extra.
What you describe after generating the XML output (that is, applying a stylesheet) is trivial in reports - there is an undocumented PL/SQL built-in SRW.SET_XML_PROLOG to allow you to set the XML prolog line. Refer to bug 1265291 for information on how to do this. It should take about 5mins to test.
With Reports you could have nice looking printable output (PDF, postscript, and so on) or on the web in HTML or HTMLCSS - but at the same time, get XML out just by changing a single parameter, and then use in a B2B environment such as the one described.
Regarding XML report generation, you can do this in different ways:
About your question on DBIs, you can access any table/view in the database using the XML developer kit or the XSQL Servlet, so you can generate XML code using the information from the database items (FF_DATABASE_ITEMS table)
I have a report with a template. The same report is needed with another template. I copied the report and try to change the template. Is there another way of changing the template than the wizard, because the wizard changes the layout of the report and I do not want that.
You must apply the template first, because when you use the Report Wizard to apply a new template, it will then create a new layout overriding the existing layout. There is no other way.
However, you can use XML for modifying the Report at runtime without changing the layout. See this chapter under section, "Customizing Reports at Runtime with XML" , for some simple examples for modifying a report using XML at runtime.
I tried to apply XML to my RDF file to change the boilerplate text from English to Chinese. My XML is shown as below:
<report name="am01.rdf" DTDVersion="1.0"> <layout> <section name="main"> <field name="f_title" source="title" textColor="red" fontSize="16" fontStyle="bold"/> </section> </layout> <customize> <object name="B_4" type="REP_GRAPHIC_TEXT"> <properties> <property name="textSegment">µçÓ°Ãû</property> </properties> </object> </customize> </report>
I saved this XML file as unicode format, and when I tried applying XML to my RDF through RWRUN60 from command line, report builder gives me the following message:
REP-6106:Error in the XML report definition at line 1 in 'c:\am01.xml' Start of root element expected instead of TEXT 'null'
I am using Oracle Report Server/Developer 6i running with Apache web server.
See the following example for ideas on answering your question:
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|