Home

Innovation

Articles

Products

Blog

Report Writing: Using Oracle XML, XSL, and HTML

Are you looking for a more open and universal approach to report writing? Have you considered an approach based on generating XML source data rendered to HTML using XSL, all achievable in the standard install of Oracle9i/10g.

by Philip Lambert

As an Oracle PL/SQL developer you may have been approached to write reports against the database, but have been deterred or hindered by the reporting tools available. You may have found these tools overly complicated for your needs, expensive, client based, with complex server based services, requiring a high degree of training, learning, and administration.

This article describes how to write a simple PL/SQL package to implement an XML/XSL/HTML reporting approach. 

Download: The demonstration files can be down loaded from here.

Approach

The reporting approach is based on generating all of your reporting data in an XML format achieved using the XML functions supported by Oracle’s SQL and PL/SQL engines. XHTML is then rendered from the XML by using XSL (XML Stylesheets). The XHTML is then served to a client based browser using the Oracle HTTP listener.

For the less sophisticated reporting requirements this may be considered a viable alternative to some of the more daunting tools you may being forced to use or consider.

What You Need

If you are going to invest some time in learning a new reporting tool, then it makes a lot of sense to learn something universal. XML, XSL, and HTML will not only be limited to just developing reports, but can be applied to many other types of solutions such as messaging, web services, configuration management, web development, etc.

For this demonstration it is assumed you already have a knowledge of PL/SQL. The additional skills that you will need are:

• XHTML (XML well formed version of HTML)

• XSL

• CSS (if you want to manage your styling centrally)

To what depth you learn these languages is very dependent on the IDE tools you use to assist you. Writing complex HTML and XSL from a plain text editor is not very advisable unless you have very basic needs. Tools such as XML Spy and Stylus Studio provide XSL editing capabilities, supported with drag and drop layout formatting, language syntax hinting, and browser based previewing for your generated HTML. My favourite is Stylus Studio which I feel has a superior HTML layout editor and previewer than XML Spy.

In order to implement this solution, your database should be set up to use the following PL/SQL functionality:

• XML functions in SQL (XMLAGG, XMLELEMENT, XMLATTRIBUTE, etc, available through XDB)

• HTP supplied package (only the print procedure needs to be used)

• DBMS_XMLDOM supplied package (to read in report configuration file – optional)

• WPG_DOCLOAD supplied package (to display images)

• DBMS_LOB supplied package (to manage CLOB data)

A standard Oracle9i/10g install would normally have these available by default.
 

Database Design

So where do you start? Well lets have a look at the database design. The demo solution has been kept as simple as possible, so only two tables are required. One table holds all of the XML, XSL files required, and the other holds a logging table.

CREATE TABLE WEB_FILES
(
    FILENAME      VARCHAR2(255 BYTE) NOT NULL,
    CONTENT_CLOB  CLOB,
    CONTENT_BLOB  BLOB,
CONSTRAINT PK_WEB_FILES PRIMARY KEY (FILENAME)
);

This table holds all files associated with the reporting application such as .gif image files, .xls stylesheet files, .xml configuration files, etc. It is used to simulate accessing the file system.

CREATE TABLE WEB_PAGE_LOGS
(
    PAGE_ID        NUMBER NOT NULL,
    PAGE_DT        DATE,
    REPORT_NAME    VARCHAR2(255 BYTE),
    CONTENT_XML    CLOB,
    CONTENT_XHTML  CLOB,
    ERROR_MSG      VARCHAR2(4000 BYTE),
CONSTRAINT PK_WEB_PAGE_LOGS PRIMARY KEY (PAGE_ID)
);

This table holds a log entry for each call of a report. You might consider this a bit of an overhead, but because the solution uses CLOBs, using permanently persisted CLOBs results in a more robust solution than using temporary CLOBs in my view , and therefore worth the overhead. The CLOBs hold XML data, so you may be asking why I haven’t used the XMLTYPE data type. The answer is I would like to, but there are some bugs in Oracle9i that cause internal errors when you attempt to use the XMLTRANSFORM (XSLT) function directly on an XMLTYPE (only in certain circumstances – I have it working well in other examples, but just couldn’t figure why it was upsetting the demo). Converting the CLOB dynamically to an XMLTYPE seems to circumvent the bug – very odd.

Application Design

Lets have a look at the main procedure of the demo package:

The PL/SQL Apache module (mod_plsql) has a very useful feature where if you repeat the same parameter name, then the values are passed in as an array into the procedure. This is very useful for specifying an indefinite number of generically named parameters.

The show_page procedure has two optional array (TABLE TYPE) parameters:

create_log

The first thing to be performed is to create a log entry for the report. This is then used to persist the XML data and transformed XHTML as CLOBs and record any execution errors.

get_report_metadata

This demo solution uses an XML file to define the report (see demo_report.xml). Again the demo has been kept very simple, so the definition is very basic, and only holds the SQL to generate the XML for the report plus a couple of other minor attributes.

There are opportunities to make this far more sophisticated by defining report columns, headings, formats, justification, etc, and merging this information into a wrapper element over the XML data, for the XSL to pick up and dynamically configure the format of the report. The XML definition file is stored in the WEB_FILES table, retrieved and interpreted using the DBMS_XMLDOM supplied package (this is an XML DOM API).

Since XML forms a hierarchical structure, in a more elaborate design, it is possible map the XML elements and attributes very conveniently to a similarly structured record type containing nested tables for each child element. This puts the XML data into a quick and easily accessible structure for PL/SQL (DBMS_XMLDOM can be too slow for large XML documents and repetitive access).

apply_parameters

The apply_parameters procedure searches for any bind variables (prefixed with a colon) in the report SQL text matching a named parameter in the parameter list and substitutes it with the corresponding value.

get_xml

Once the report definition has been retrieved from the XML configuration file, the report SQL XML can be executed and the resulting XML data stored against the log (since this solution is intended for a browser, the expectation is that the reports will not be overly large, however, this approach might require careful housekeeping consideration to avoid excessive use of storage).

render_page

Now that we have the data in XML format, its time to render it into XHTML. The X form of HTML is one that conforms to the well formed rules of XML. Standard HTML, though a tag based language, has syntax that would fail an XML well-formedness test, so XHTML addresses this. Since we are working within the XML development domain we should use XHTML as the target format.

The report style sheet is also stored in WEB_FILES table as a .xsl file (see demo.xsl). The report definition XML file specified the style sheet to use for the report. There are different ways of structuring your style sheets:

• You could have one all purpose style sheet for all your reports, and include in the specifics for the report from a separate style sheet;

• You could have one style sheet for each report and include in the common formating parts (such as header and trailer, etc).

sent_to_http

Once the XML data has been rendered into XHTML, it can be sent to the Oracle HTTP listener. The most basic way of doing this is to use the print procedure in the HTP supplied package. As this procedure works with VARCHAR2 only, the CLOB needs to be broken up into chunks less than 32Kb. The print procedure will terminate each print with a line feed, so it is essential that that the chunks do not dissect mid syntax, otherwise the HTML will not be interpreted correctly by the browser.

Displaying Images

Normally on a web server the image files are held on the file system, and it is possible to configure the Oracle HTTP Listener to source files from here. However, database servers often have limited access at the operating system level and it can be difficult to deploy files such as images. A more convenient method is to store the images in the database and define a PL/SQL procedure to retrieve them.

Image files are returned to the HTTP listener using the get_image procedure which uses the wpg_docload supplied package to handle the mime type correctly and send the image to the HTTP listener.

Using Cascading Style Sheets

Referencing cascading style sheets (CSS) stored in the database is performed in a similar way to retrieving images, by using the get_file procedure.

Report Writing Using XSL

When you see what can be achieved with XSL to generate HTML, particularly when using XML IDE tools such as XML Spy Suite and Stylus Studio, it can make you wonder why you would want to purchase any other form of simple reporting tool. XSL is a universal language for processing XML for all purposes, so by learning it, your are putting many useful strings to your bow.

Stylus Studio is my favourite XML tool and an evaluation copy can be down loaded from the Sonic website.

A useful feature is to be able to specify a “scenario” XML file, effectively the XML data for the report (saved and stored onto the file system for development purposes - see demo.xml),

The report layout is then maintained via a WYSIWYG layout editor and a browser pre-viewer allows you to see how your report will look.

The WYSIWYG editor both in Stylus Studio and XML Spy can unexpectedly overwrite any bespoke XSL, so personally, I prefer to edit the XSL directly and avoid switching to the WYSIWYG editor.

The previewer works fine along with the built in XSLT engine, however, the Oracle XSLT engine used by the XMLTRANSFORM command can have some issues, which I have in the main been able to circumvent.

One issue with the XMLTRANSFORM command is the use of the format-number function in XSL used for applying format masks. This function causes internal errors in Oracle unless you use a user defined format via the xsl:decimal-format command. E.g.

<xsl:decimal-format name="Oracle" decimal-separator="." grouping-separator="," minus-sign="-" zero-digit="0" digit="9"/>

….

<xsl:value-of select="format-number( /WebPage/Demo/DemoData/DemoDataItem/@Value,'999,999,990',
                 'Oracle')"/>
 

Deployment and Version Control

The report application is a single PL/SQL package which can be deployed into any schema along with its two underlying tables and sequence.

Version Control

A very useful feature with using XML is that it is in plain text format which means it can be conveniently managed by a source control system e.g. Visual SourceSafe or CVS. This reporting application is all driven by plain text files held in the WEB_FILES table which means that to deploy the application the files only need to be loaded into this table, easily achieved using SQL Loader.

As XML is plain text and it can be plugged with comments containing your source control software’s tags, such as these CVS tags…

<?xml version="1.0"?>
<!-- ==================================================

$Id$
$Name$

==================================================== -->

Application Deployment

See the readme.txt file in the download zip file for instructions to deploy the application.

File Deployment

To deploy files into the database using SQLLoader, character based files (XML, XSL, CSS) would be loaded into the CLOB column of the web_pages table using this sql loader control file:-

LOAD DATA
APPEND
INTO TABLE web_files
FIELDS TERMINATED BY '|'
(
    filename CHAR(255),
    source_filename FILLER CHAR(255),
    content_clob LOBFILE(source_filename) TERMINATED BY EOF
)

The contents of the data file would look like this:

demo.xsl|demo.xsl
demo_report|demo_report.xml
dbi.css|dbi.css

Image files (.jpg, .gif) would be loaded into the BLOB column of the web_pages table using this sqlloader control file:

LOAD DATA
APPEND
INTO TABLE web_files
FIELDS TERMINATED BY '|'
(
    filename CHAR(255),
    source_filename FILLER CHAR(255),
    content_blob LOBFILE(source_filename) TERMINATED BY EOF
)

Using this data file:

head-banner-white7.jpg|head-banner-white7.jpg
dbi-logo-white.gif|dbi-logo-white.gif
dbi-panel.jpg|dbi-panel.jpg

So to load up these files run the following sqlloader commands:

sqlldr CONTROL=web_demo_files.ctl, LOG=web_demo_files.log, BAD=web_demo_files.bad, DATA=web_demo_files.dat, USERID=<schema>/<password>@<database>

sqlldr CONTROL=web_demo_images.ctl, LOG=web_demo_images.log, BAD=web_demo_images.bad, DATA=web_demo_images.dat, USERID=<schema>/<password>@<database>

The only issue with this loading method is that sqlloader will not replace an existing file, so these would have to be deleted prior to loading.

Running the Reports

If you have never used Oracle’s built-in HTTP listener (a repackaged Apache), then the most interesting feature of it is the ModPL/SQL Apache module. This allows you to call a package or stored procedure directly in the database using an URL address.

Database Access Descriptor

Before you can run the report, the Database Access Descriptor (DAD) needs to be defined. This is done via the HTTP configuration main menu which will appear if you type in your database server as a host and the HTTP listener port number into your browser:

http://myserver:7777

Where 7777 is the default port number of the HTTP listener (could be something different as defined by the http.conf file).

From the main menu select the mod_plsql Configuration Menu and navigate through to the Gateway Database Access Descriptor Settings and Add for WebDB 2.x configuration. Give your DAD a name, a default schema name (where the package and tables reside), and connection details. If the password and/or username are left blank, then a login window will pop-up prompting for these to be entered.

URL Syntax

To run the report, call the show_page package procedure directly using the following URL syntax:

http://myserver:7777/pls/myDAD/pkg_web_demo.show_page?
                 p_report_name=demo_report

A page should be displayed looking like this:

To run the report with parameters, then you would use the following syntax:

http://myserver:7777/pls/myDAD/pkg_web_demo.show_page?
           p_report_name=demo_report_param&
           p_parameter=p_id&p_value=2
 

NOTE: This report is different from the first one (demo_report_param rather than demo_report). The SQL text in the report has been modified to inclue a bind variable :p_id. This version of the report will only return one row where the parameter matches the id column.

To add further parameters to the report, keeping adding the same paired parameters:

http://myserver:7777/pls/myDAD/pkg_web_demo.show_page?
           p_report_name=demo_report_paramX&
           p_parameter=p_id&p_value=2&
           p_parameter=p_name&p_value=test

Trouble Shooting

If the report does not display, try running the test procedure...

http://myserver:7777/pls/myDAD/pkg_web_demo.test

If this displays, then the HTTP listener is functioning properly, but you may have issues with the deployment of the report files. The WEB_PAGE_LOGS table should hold an entry for the attempted run of the report, and the ERR_MSG column will hold any Oracle errors raised.

If the page does not display, then the problem may be to do with issues with the HTTP Listener. Check that it is started, or even restarted if a new DAD has been added, and running on the expected port number (default is 7777).

Conclusion

It may not replace the more sophisticated reporting tools, but if you need to be up and running with a simple reporting solution with potentially no capital expenditure (depending on what XML tools you already have), then this may be an option for you.

The demonstration files can be down loaded from here.