An Insider Guide to XML Publisher Oracle XML Publisher has gained a lot of popularity among developers and also among the businesses as its very easy to create a report using XML Publisher and even more easy to generate reports in various formats like PDF, EXCEL, RTF etc.

A short guide to XML Publisher

XML Publisher Oracle XML Publisher has gained a lot of popularity among developers and also among the businesses as its very easy to create a report using XML Publisher and even more easy to generate reports in various formats like PDF, EXCEL, RTF etc. Oracle created XML Publisher is as a template based tool for publishing reports and much more along with Oracle Ebusiness Suite. XML Publisher is powerful enough to populate the data fetched by a PL SQL package into a custom, yes custom, template and then generate the report in any of the desired form such as PDF, HTML, RTF, EXCEL (HTML) and even PLAIN TEXT for transmission using EFT and EDI.

Difference between Classical Reporting Tools and XML Publisher: The classical reporting tool had very different approach from that of XML Publisher e.g. in Report 6i used to combine all the aspects of report generation i.e. the data definition, the report layout and the translation in to a single entity. In this case it became very complex and cumbersome even if the report needed minor changes. Also if the same report is required in another language then one more report needs to be generated in other language. All these and many more hiccups made Oracle to come up with XML Publisher. With XML Publisher things were very different in a positive way. XML Publisher treats all the three aspects of report generation i.e. the data definition, the report layout and the translation separately at design time, but at run time all the three aspects are brought together and the final report is generated. So if the report needs changes at the report layout level then Data Definition and the Translation part are not supposed to be touched and only the Report Design can be changed alone. This means less time and money is being spent in maintenance of these reports. The working of the XML Publisher can be understood with the help of the following diagram:

Business Logic: In this part or aspect the required data (as per the business logic) is extracted/fetched from the database and is converted in to XML Strings. Report Layout: In this aspect or section of the XML Publisher the layout or the template of the final report to be generated is stored and Managed in the Template Manager. These layouts or template can be very easily created using familiar word processing software like Microsoft Word, Microsoft Excel etc. Translation: The translation handler will manage the translation that is required at runtime.

www.techhoney.com 2

A short guide to XML Publisher

How to generate reports using XML Publisher Pre-Requisites: 1. Oracle XML Publisher Release 5.5 or higher. 2. Template builder (Usually gets installed with Oracle XML Publisher 5.5 itself). 3. MS Office 2000 or higher to create templates. Steps to install XML Publisher: 1. Go to XML Publisher Desktop and double click the setup.exe, this will start the Install Shield Wizard which will guide you throughout the process

2. After the installation is completed Open MS Word, you will be able to see one more menu item named “Template Builder” as shown in the image below. This menu will enable you embed the XML data into your template, set many preferences and preview the output of the report.

www.techhoney.com 3

A short guide to XML Publisher

Steps to generate a report using XML Publisher: 1. Create a PL SQL Package or Procedure that will fetch the required data as per the business requirement and then place the data to be displayed in XML tags into an output file. 2. Create an AOL concurrent program that will execute the PL SQL stored procedure created in step 1. 3. Create a template or the desired layout of the report to be generated using Template Builder. 4. Upload the finalized template or layout of the report to Oracle Application using XML Publisher Administrator responsibility. 5. Run the AOL concurrent program so that it in turn executes the PL SQL stored procedure to generate the data for the report. 6. Republish the Report. Now let’s elaborate each and every point mentioned above:



We will create a PL SQL package that will fetch the data from the data base tables and then populate the same data in to XML tags. Below is a simple example of such a PL SQL package.

/*Demo Package Specification*/

CREATE OR REPLACE PACKAGE Demo_XDO_pkg AS PROCEDURE Demo_RTF(o_errbuf OUT VARCHAR2,o_retcode END Demo_XDO_pkg;

OUT VARCHAR2 );

/*Demo Package Definition*/

CREATE OR REPLACE PACKAGE BODY Demo_XDO_pkg as PROCEDURE Demo_RTF( o_errbuf OUT VARCHAR2,o_retcode AS /*Cursor to fetch Employee Records*/

CURSOR csr_employee IS SELECT papf.person_id prsn_id ,papf.full_name prsn_name ,employee_number emp_no ,pg.name grade FROM per_all_people_f papf, per_all_assignments_f paaf, per_grades pg WHERE papf.person_id=paaf.person_id AND paaf.grade_id=pg.grade_id AND paaf.assignment_type='E' AND paaf.primary_flag='Y' www.techhoney.com 4

OUT VARCHAR2 )

A short guide to XML Publisher

AND AND AND AND AND

TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date TRUNC(SYSDATE) BETWEEN NVL(pg.date_from,sysdate) AND NVL(pg.date_to,sysdate) papf.business_group_id=5603 ROWNUM