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.
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
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:
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
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
DBMS_LOB supplied package (to manage CLOB
A standard Oracle9i/10g install would normally
have these available by default.
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
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
REPORT_NAME VARCHAR2(255 BYTE),
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.
Lets have a look at the main procedure of the
t_parameters := g_parameters,
t_values := g_values
k_procedure_name CONSTANT VARCHAR2(30)
k_procedure_name, 'Executed successfully');
WHEN OTHERS THEN
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
The show_page procedure has two optional array
(TABLE TYPE) parameters:
the name of the parameter.
p_value takes the
value of the parameter
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.
p_page_id OUT NUMBER
INSERT INTO web_page_logs
RETURNING page_id INTO p_page_id;
g_page_id := p_page_id;
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
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
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.
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
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
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).
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.
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
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
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
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.
decimal-separator="." grouping-separator="," minus-sign="-"
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.
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
As XML is plain text and it can be plugged with comments containing
your source control software’s tags, such as these CVS tags…
See the readme.txt file in the download zip
file for instructions to deploy the application.
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
INTO TABLE web_files
FIELDS TERMINATED BY '|'
source_filename FILLER CHAR(255),
content_clob LOBFILE(source_filename) TERMINATED BY EOF
The contents of the data file would look like
Image files (.jpg, .gif) would be loaded into
the BLOB column of the web_pages table using this sqlloader control
INTO TABLE web_files
FIELDS TERMINATED BY '|'
source_filename FILLER CHAR(255),
content_blob LOBFILE(source_filename) TERMINATED BY EOF
Using this data file:
So to load up these files run the following
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
Where 7777 is the default port number of the
HTTP listener (could be something different as defined by the
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
To run the report, call the show_page package
procedure directly using the following URL syntax:
A page should be displayed looking like this:
To run the report with parameters, then you
would use the following syntax:
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:
If the report does not display, try running the
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).
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