Skip Headers
Oracle® XML Developer's Kit Programmer's Guide
10g Release 2 (10.1.2)
Part No. B14033-01
  Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

8 XSQL Pages Publishing Framework

This chapter contains these topics:

XSQL Pages Publishing Framework Overview

The Oracle XSQL Pages publishing framework is an extensible platform for easily publishing XML information in any format you desire. It greatly simplifies combining the power of SQL, XML, and XSLT to publish dynamic Web content based on database information.

Using the XSQL publishing framework, anyone familiar with SQL can create and use declarative templates called "XSQL pages" to:

Assembling and transforming information for publishing requires no programming. In fact, most of the common things you will want to do can be easily achieved in a declarative way. However, since the XSQL publishing framework is extensible, if one of the built-in features does not fit your needs, you can easily extend the framework using Java to integrate custom information sources or to perform custom server-side processing.

Using the XSQL Pages framework, the assembly of information to be published is cleanly separated from presentation. This simple architectural detail has profound productivity benefits. It enables you to:

What Can I Do with Oracle XSQL Pages?

Using server-side templates — known as "XSQL pages" due to their .xsql extension — you can publish any information in any format to any device. The XSQL page processor "engine" interprets, caches, and processes the contents of your XSQL page templates. Figure 8-1 illustrates that the core XSQL page processor engine can be "exercised" in four different ways:

  • From the command line or in batch using the XSQL Command-Line Utility

  • Over the Web, using the XSQL Servlet installed into your favorite Web server

  • As part of JSP applications, using <jsp:include> to include a template

  • Programmatically, with the XSQLRequest object, the engine's Java API

Figure 8-1 Understanding the Architecture of the XSQL Pages Framework

Description of xsql5.gif follows
Description of the illustration xsql5.gif

The same XSQL page templates can be used in any or all of these scenarios. Regardless of the means by which a template is processed, the same basic steps occur to produce a result. The XSQL page processor "engine":

  1. Receives a request to process an XSQL template

  2. Assembles an XML "datagram" using the result of one or more SQL queries

  3. Returns this XML "datagram" to the requestor

  4. Optionally transforms the "datagram" into any XML, HTML, or text format

During the transformation step in this process, you can use stylesheets that conform to the W3C XSLT 1.0 standard to transform the assembled "datagram" into document formats like:

  • HTML for browser display

  • Wireless Markup Language (WML) for wireless devices

  • Scalable Vector Graphics (SVG) for data-driven charts, graphs, and diagrams

  • XML Stylesheet Formatting Objects (XSL-FO), for rendering into Adobe PDF

  • Text documents, like e-mails, SQL scripts, Java programs, and so on

  • Arbitrary XML-based document formats

XSQL Pages bring this functionality to you by automating the use of underlying Oracle XML components to solve many common cases without resorting to custom programming. However, when only custom programming will do — as we'll see in the Advanced Topics section of this chapter — you can augment the framework's built-in actions and serializers to assemble the XSQL "datagrams" from any custom source and serialize the datagrams into any desired format, without having to write an entire publishing framework from scratch.

Where Can I Obtain Oracle XSQL Pages?

XSQL Servlet is provided with Oracle and is also available for download from the OTN site.

Where indicated, the examples and demos described in this chapter are also available from OTN.


See Also:

XSQL Servlet Release Notes on OTN at http://www.oracle.com/technology/tech/xml/

What Is Needed to Run XSQL Pages?

To run the Oracle XSQL Pages publishing framework from the command-line, all you need is a Java VM (1.1.8, 1.2.2, or 1.3). The XSQL Pages framework depends on two underlying components in the Oracle XML Developer's Kit:

  • Oracle XML Parser and XSLT Processor (xmlparserv2.jar)

  • Oracle XML SQL Utility (xsu12.jar)

Both of their Java archive files must be present in the CLASSPATH where the XSQL pages framework is running. Since most XSQL pages will connect to a database to query information for publishing, the framework also depends on a JDBC driver. Any JDBC driver is supported, but when connecting to Oracle, it's best to use the Oracle JDBC driver (classes12.jar) for maximum functionality and performance.

Lastly, the XSQL publishing engine expects to read its configuration file (by default, named XSQLConfig.xml) as a Java resource, so you must include the directory where the configuration file resides in the CLASSPATH as well.

To use the XSQL Pages framework for Web publishing, you need a Web server that supports Java Servlets.


  • See Also:

    For details on installing the XSQL Servlet on different Web servers, configuring your environment, and running XSQL Servlet, see the XSQL Servlet "Release Notes" on OTN at http://www.oracle.com/technology/tech/xml

Security Considerations for XSQL Pages

This section describes best practice security techniques for using the Oracle XSQL Servlet.

Install Your XSQLConfig.xml File in a Safe Directory

The XSQLConfig.xml configuration file contains sensitive database username/password information that must be kept secure on the server. This file should not reside in any directory that is mapped to a virtual path of your Web server, nor in any of its subdirectories. The read permissions of the configuration file need only be granted such that the UNIX account that owns the servlet engine can read it.

Failure to follow this recommendation could mean that a user of your site could accidentally, or intentionally, browse the contents of your configuration file.

Disable Default Client Stylesheet Overrides

By default, the XSQL Page Processor allows the user to supply a stylesheet in the request by passing a value for the special xml-stylesheet parameter. If you want the stylesheet that is referenced inside your server-side XSQL page to be the only stylesheet that is used, then you can include the allow-client-style="no" attribute on the document element of your page. You also can globally change the default setting to disallow client stylesheet overrides by changing a setting in your XSQLConfig.xml file. If you do this, then only pages that will allow client stylesheet overrides are ones that include the allow-client-style="yes" attribute on their document element.

Be Alert for the Use of Substitution Parameters

With power comes responsibility. Any product such as XSQL Pages that supports the use of lexical substitution variables in a SQL query can cause a developer problems. Any time you deploy an XSQL page that allows important parts of a SQL statement (or at the extreme, the entire SQL statement) to be substituted by a lexical parameter, you must make sure that you have taken appropriate precautions against misuse.

For example, one of the demonstrations that comes with XSQL Pages is the "adhoc query demo". It illustrates how the entire SQL statement of an <xsql:query> action handler can be supplied as a parameter. This is a powerful capability when in the right users hands, but be aware that if you deploy a similar kind of page to your product system, then the user can execute any query that the database security privileges for the connection associated with the page allows. The demo is setup to use a connection that maps to the SCOTT account, so a user of the "adhoc query demo" can query any data that SCOTT would be allowed to query from the SQL*Plus command line.

Techniques that can be used to make sure your pages are not abused include:

  • Making sure the database user account associated with the page has only the privileges for reading the tables and views you want your users to see.

  • Using true bind variables instead of lexical bind variables when substituting single values in a SELECT statement. If you need to make syntactic parts of your SQL statement parameterized, then lexical parameters are the only way to proceed. Otherwise, true bind variables are recommended, so that any attempt to pass an invalid value will generate an error instead of producing an unexpected result.

What's New in XSQL Pages Release 10.1

The following list highlights the key new features added in the release 10.1 to the XSQL Pages publishing framework. You can now:

The XSQL servlet processor has the following new features in release 10.1:

Overview of Basic XSQL Pages Features

In this section, we take a brief look at the most basic features you can exploit in your server-side XSQL page templates:

Producing XML Datagrams from SQL Queries

It is extremely easy to serve database information in XML format over the Web using XSQL pages. For example, let us see how simple it is to serve a real-time XML "datagram" from Oracle, of all available flights landing today at JFK airport. Using Oracle JDeveloper, or your favorite text editor, just build an XSQL page template like the one following, and save it in a file named, AvailableFlightsToday.xsql:

<?xml version="1.0"?>
<xsql:query connection="demo" bind-params="City" xmlns:xsql="urn:oracle-xsql">
    SELECT Carrier, FlightNumber, Origin, TO_CHAR(ExpectedTime,'HH24:MI') AS Due
      FROM FlightSchedule
     WHERE TRUNC(ExpectedTime) = TRUNC(SYSDATE) AND Arrived = 'N'
       AND Destination = ?   /* The ? is a bind variable being bound */
  ORDER BY ExpectedTime      /* to the value of the City parameter   */
</xsql:query>

With XSQL Servlet properly installed on your Web server, you just need to copy the AvailableFlightsToday.xsql file preceding to a directory under your Web server's virtual directory hierarchy. Then you can access the template through a Web browser by requesting the URL:

http://yourcompany.com/AvailableFlightsToday.xsql?City=JFK

The results of the query in your XSQL page are materialized automatically as XML and returned to the requester. This XML-based "datagram" is typically requested by another server program for processing, but if you are using a browser such as Internet Explorer 5.0, you can directly view the XML result as shown in Figure 8-2.

Figure 8-2 XML Result From XSQL Page (AvailableFlightsToday.xsq) Query

Description of xsql1.gif follows
Description of the illustration xsql1.gif

Let us take a closer look at the XSQL page template we used. Notice the XSQL page begins with:

<?xml version="1.0"?>

This is because the XSQL template is itself an XML file (with an *.xsql extension) that contains any mix of static XML content and XSQL "action elements". The AvailableFlightsToday.xsql example preceding contains no static XML elements, and just a single XSQL action element <xsql:query>. It represents the simplest useful XSQL page we can build, one that just contains a single query.

Notice that the first (and in this case, only!) element in the page <xsql:query> includes a special attribute that declares the xsql namespace prefix as a "synonym" for the Oracle XSQL namespace identifier urn:oracle-xsql.

<xsql:query connection="demo" bind-params="City" xmlns:xsql="urn:oracle-xsql">

This first, outermost element — known at the "document element" — also contains a connection attribute whose value "demo" is the name of one of the pre-defined connections in the XSQL configuration file (by default, named XSQLConfig.xml):

<xsql:query connection="demo" bind-params="City" xmlns:xsql="urn:oracle-xsql">

The details concerning the username, password, database, and JDBC driver that will be used for the "demo" connection are centralized into the configuration file. Setting up these connection definitions is discussed in a later section of this chapter.

Lastly, the <xsql:query> element contains a bind-params attribute that associates the values of parameters in the request by name to bind parameters represented by question marks in the SQL statement contained inside the <xsql:query> tag.

Note that if we wanted to include more than one query on the page, we need to invent an XML element of our own creation to "wrap" the other elements like this:

<?xml version="1.0"?>
<page connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:query bind-params="City">
    SELECT Carrier, FlightNumber, Origin, TO_CHAR(ExpectedTime,'HH24:MI') AS Due
      FROM FlightSchedule
     WHERE TRUNC(ExpectedTime) = TRUNC(SYSDATE) AND Arrived = 'N'
       AND Destination = ?   /* The ? is a bind variable being bound */
      ORDER BY ExpectedTime  /* to the value of the City parameter   */
  </xsql:query>
  <!-- Other xsql:query actions can go here inside <page> and </page> -->
</page>

Notice in this example that the connection attribute and the xsql namespace declaration always go on the document element, while the bind-params is specific to the <xsql:query> action.

Transforming XML Datagrams into an Alternative XML Format

If the canonical <ROWSET> and <ROW> XML output from Figure 8-2 is not the XML format you need, then you can associate an XSLT stylesheet to your XSQL page template to transform this XML "datagram" in the server before returning the information in any alternative format desired.

When exchanging data with another program, typically you will agree in advance with the other party on a specific Document Type Definition (DTD) that describes the XML format you will be exchanging. A DTD is in effect, a "schema" definition. It formally defines what XML elements and attributes that a document of that type can have.

Let us assume you are given the flight-list.dtd definition and are told to produce your list of arriving flights in a format compliant with that DTD. You can use a visual tool such as Extensibility's "XML Authority" to browse the structure of the flight-list DTD as shown in Figure 8-3.

Figure 8-3 Exploring the "industry standard" flight-list.dtd using Extensibility's XML Authority

Description of xsql2.gif follows
Description of the illustration xsql2.gif

This shows that the standard XML formats for Flight Lists are:

  • <flight-list> element, containing one or more…

  • <flight> elements, having attributes airline and number, each of which contains an…

  • <arrives> element.

By associating the following XSLT stylesheet, flight-list.xsl, with the XSQL page, you can change the default <ROWSET> and <ROW> format of your arriving flights into the "industry standard" DTD format.

<!-- XSLT Stylesheet to transform ROWSET/ROW results into flight-list format
 --> 
<flight-list xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
               xsl:version="1.0">   
  <xsl:for-each select="ROWSET/ROW">
      <flight airline="{CARRIER}" number="{FLIGHTNUMBER}">
        <arrives><xsl:value-of select="DUE"/></arrives>
      </flight>
  </xsl:for-each>
</flight-list>

The stylesheet is a template that includes the literal elements that you want produced in the resulting document, such as, <flight-list>, <flight>, and <arrives>, interspersed with special XSLT "actions" that allow you to do the following:

  • Loop over matching elements in the source document using <xsl:for-each>

  • Plug in the values of source document elements where necessary using <xsl:value-of>

  • Plug in the values of source document elements into attribute values using {something}

Note two things have been added to the top-level <flight-list> element in the stylesheet:

  • xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

    This defines the XML Namespace (xmlns) named "xsl" and identifies the uniform resource locator string that uniquely identifies the XSLT specification. Although it looks just like a URL, think of the string http://www.w3.org/1999/XSL/Transform as the "global primary key" for the set of elements that are defined in the XSLT 1.0 specification. Once the namespace is defined, we can then make use of the <xsl:XXX> action elements in our stylesheet to loop and plug values in where necessary.

  • xsl:version="1.0"

    This attribute identifies the document as an XSLT 1.0 stylesheet. A version attribute is required on all XSLT Stylesheets for them to be valid and recognized by an XSLT Processor.

Associate the stylesheet to your XSQL Page by adding an <?xml-stylesheet?> processing instruction to the top of the page as follows:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="flight-list.xsl"?>
<xsql:query connection="demo" bind-params="City" xmlns:xsql="urn:oracle-xsql">
    SELECT Carrier, FlightNumber, Origin, TO_CHAR(ExpectedTime,'HH24:MI') AS Due
      FROM FlightSchedule
     WHERE TRUNC(ExpectedTime) = TRUNC(SYSDATE) AND Arrived = 'N'
       AND Destination = ?   /* The ? is a bind variable being bound */
      ORDER BY ExpectedTime  /* to the value of the City parameter   */
</xsql:query>

This is the W3C Standard mechanism of associating stylesheets with XML documents (http://www.w3.org/TR/xml-stylesheet). Specifying an associated XSLT stylesheet to the XSQL page causes the requesting program or browser to see the XML in the "industry-standard" format as specified by flight-list.dtd you were given as shown in Figure 8-4.

Figure 8-4 XSQL Page Results in "industry standard" XML Format

Description of xsql3.gif follows
Description of the illustration xsql3.gif

Transforming XML Datagrams into HTML for Display

To return the same XML information in HTML instead of an alternative XML format, simply use a different XSLT stylesheet. Rather than producing elements like <flight-list> and <flight>, your stylesheet produces HTML elements like <table>, <tr>, and <td> instead. The result of the dynamically queried information then looks like the HTML page shown in Figure 8-5. Instead of returning "raw" XML information, the XSQL Page leverages server-side XSLT transformation to format the information as HTML for delivery to the browser.

Figure 8-5 Using an Associated XSLT Stylesheet to Render HTML

Description of xsql4.gif follows
Description of the illustration xsql4.gif

Similar to the syntax of the flight-list.xsl stylesheet, the flight-display.xsl stylesheet looks like a template HTML page, with <xsl:for-each>, <xsl:value-of> and attribute value templates like {DUE} to plug in the dynamic values from the underlying <ROWSET> and <ROW> structured XML query results.

<!-- XSLT Stylesheet to transform ROWSET/ROW results into HTML -->
<html xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xsl:version="1.0">
  <head><link rel="stylesheet" type="text/css" href="flights.css" /></head>
  <body>
    <center><table border="0">
      <tr><th>Flight</th><th>Arrives</th></tr>
      <xsl:for-each select="ROWSET/ROW">
        <tr>
          <td>
            <table border="0" cellspacing="0" cellpadding="4">
              <tr>
                <td><img align="absmiddle" src="images/{CARRIER}.gif"/></td>
                <td width="180">
                  <xsl:value-of select="CARRIER"/>
                  <xsl:text> </xsl:text>
                  <xsl:value-of select="FLIGHTNUMBER"/>
                </td>
              </tr>
            </table>
          </td>
          <td align="center"><xsl:value-of select="DUE"/></td>
        </tr>
      </xsl:for-each>
    </table></center>
  </body>
</html>


Note:

The stylesheet looks exactly like HTML, with one tiny difference. It is well-formed HTML. This means that each opening tag is properly closed (for example, <td></td>) and that empty tags use the XML empty element syntax <br/> instead of just <br>.

You can see that by combining the power of:

  • Parameterized SQL statements to select any information you need from our Oracle database,

  • Industry-standard XML as a portable, interim data exchange format

  • XSLT to transform XML-based "data pages" into any XML- or HTML-based format you need

you can achieve very interesting and useful results quickly. You will see in later sections that what you have seen earlier is just scratching the surface of what you can do using XSQL pages.


See Also:

For a detailed introduction to XSLT and a thorough tutorial on how to apply XSLT to many different Oracle database scenarios, see the book Building Oracle XML Applications, by Steve Muench, from O'Reilly and Associates.

Setting Up and Using XSQL Pages in Your Environment

You can develop and use XSQL pages in a variety of ways. We start by describing the easiest way to get started, using Oracle JDeveloper, then cover the details you'll need to understand to use XSQL pages in your production environment.

Using XSQL Pages with Oracle JDeveloper

The easiest way to work with XSQL pages during development is to use Oracle JDeveloper. Versions 3.1 and higher of the JDeveloper IDE support color-coded syntax highlighting, XML syntax checking, and easy testing of your XSQL pages. In addition, the JDeveloper 3.2 release supports debugging XSQL pages and adds new wizards to help create XSQL actions.

To create an XSQL page in a JDeveloper project, you can:

  • Click the plus icon at the top of the navigator to add a new or existing XSQL page to your project

  • Select File | New... and select "XSQL" from the "Web Objects" tab of the gallery

To get assistance adding XSQL action elements like <xsql:query> to your XSQL page, place the cursor where you want the new element to go and either:

  • Select XSQL Element... from the right mouse menu, or

  • Select Wizards | XSQL Element... from the IDE menu.

The XSQL Element wizard takes you through the steps of selecting which XSQL action you want to use, and which attributes you need to provide.

To syntax-check an XSQL page template, you can select Check XML Syntax... at any time from the right-mouse menu in the navigator after selecting the name of the XSQL page you'd like to check. If there are any XML syntax errors, they will appear in the message view and your cursor will be brought to the first one.

To test an XSQL page, simply select the page in the navigator and choose Run from the right-mouse menu. JDeveloper automatically starts up a local Web-to-go Web server, properly configured to run XSQL pages, and tests your page by launching your default browser with the appropriate URL to request the page. Once you've run the XSQL page, you can continue to make modifications to it in the IDE — as well as to any XSLT stylesheets with which it might be associated — and after saving the files in the IDE you can immediately refresh the browser to observe the effect of the changes.

Using JDeveloper, the "XSQL Runtime" library must be added to your project's library list so that the CLASSPATH is properly setup. The IDE adds this entry automatically when you go through the New Object gallery to create a new XSQL page, but you can also add it manually to the project by selecting Project | Project Properties... and clicking on the "Libraries" tab.

Setting the CLASSPATH Correctly in Your Production Environment

Outside of the JDeveloper environment, you need to make sure that the XSQL page processor engine is properly configured to run. Oracle comes with the XSQL Servlet pre-installed to the Oracle HTTP Server that accompanies the database, but using XSQL in any other environment, you'll need to ensure that the Java CLASSPATH is setup correctly.

There are three "entry points" to the XSQL page processor:

  • oracle.xml.xsql.XSQLServlet, the servlet interface

  • oracle.xml.xsql.XSQLCommandLine, the command-line interface

  • oracle.xml.xsql.XSQLRequest, the programmatic interface

Since all three of these interfaces, as well as the core XSQL engine itself, are written in Java, they are very portable and very simple to setup. The only setup requirements are to make sure the appropriate JAR files are in the CLASSPATH of the JavaVM that will be running processing the XSQL Pages. The JAR files include:

  • oraclexsql.jar, the XSQL page processor

  • xmlparserv2.jar, the Oracle XML Parser for Java v2

  • xsu12.jar, the Oracle XML SQL utility

  • classes12.jar, the Oracle JDBC driver

In addition, the directory where XSQL Page Processor's configuration file (by default, named XSQLConfig.xml) resides must also be listed as a directory in the CLASSPATH.

Putting all this together, if you have installed the XSQL distribution in C:\xsql, then your CLASSPATH is:

C:\xsql\lib\classes12.classes12.jar;C:\xsql\lib\xmlparserv2.jar;
C:\xsql\lib\xsu12.jar;C:\xsql\lib\oraclexsql.jar;
directory_where_XSQLConfig.xml_resides

On UNIX, if you extracted the XSQL distribution into your /web directory, the CLASSPATH is:

/web/xsql/lib/classes12.jarclasses12.jar:/web/xsql/lib/xmlparserv2.jar:
/web/xsql/lib/xsu12.jar:/web/xsql/lib/oraclexsql.jar:
directory_where_XSQLConfig.xml_resides

To use the XSQL Servlet, one additional setup step is required. You must associate the .xsql file extension with the XSQL Servlet Java class oracle.xml.xsql.XSQLServlet. How you set the CLASSPATH of the Web server's servlet environment and how you associate a Servlet with a file extension are done differently for each Web server. The XSQL Servlet Release Notes contain detailed setup information for specific Web servers you might want to use with XSQL Pages.

Setting Up the Connection Definitions

XSQL pages refer to database connections by using a short name for the connection defined in the XSQL configuration file. Connection names are defined in the <connectiondefs> section of the XSQL configuration file (by default, named XSQLConfig.xml) like this:

<connectiondefs>
   <connection name="demo">
     <username>scott</username>
     <password>tiger</password>
     <dburl>jdbc:oracle:thin:@localhost:1521:testDB</dburl>
     <driver>oracle.jdbc.driver.OracleDriver</driver>
     <autocommit>false</autocommit>
   </connection>
   <connection name="lite">
     <username>system</username>
     <password>manager</password>
     <dburl>jdbc:Polite:POlite</dburl>
     <driver>oracle.lite.poljdbc.POLJDBCDriver</driver>
    </connection>
</connectiondefs>

For each connection, you can specify five pieces of information:

  1. <username>

  2. <password>

  3. <dburl>, the JDBC connection string

  4. <driver>, the fully-qualified class name of the JDBC driver to use

  5. <autocommit>, optionally forces the AUTOCOMMIT to TRUE or FALSE

If the <autocommit> element is omitted, then the XSQL page processor will use the JDBC driver's default setting of the AUTOCOMMIT flag.

Any number of <connection> elements can be placed in this file to define the connections you need. An individual XSQL page refers to the connection it wants to use by putting a connection="xxx" attribute on the top-level element in the page (also called the "document element").


Note:

For security reasons, when installing XSQL Servlet on your production Web server, make sure the XSQLConfig.xml file does not reside in a directory that is part of the Web server's virtual directory hierarchy. Failure to take this precaution risks exposing your configuration information over the Web.

Using the XSQL Command-Line Utility

Often the content of a dynamic page will be based on data that is not frequently changing in your environment. To optimize performance of your Web publishing, you can use operating system facilities to schedule offline processing of your XSQL pages, leaving the processed results to be served statically by your Web server.

You can process any XSQL page from the command line using the XSQL command-line utility. The syntax is:

$ java oracle.xml.xsql.XSQLCommandLine xsqlpage [outfile] [param1=value1 ...]

If an outfile is specified, the result of processing xsqlpage is written to it, otherwise the result goes to standard out. Any number of parameters can be passed to the XSQL page processor and are available for reference by the XSQL page being processed as part of the request. However, the following parameter names are recognized by the command-line utility and have a pre-defined behavior:

  • xml-stylesheet=stylesheetURL

    Provides the relative or absolute URL for a stylesheet to use for the request. Also can be set to the string none to suppress XSLT stylesheet processing for debugging purposes.

  • posted-xml=XMLDocumentURL

    Provides the relative or absolute URL of an XML resource to treat as if it were posted as part of the request.

  • useragent=UserAgentString

    Used to simulate a particular HTTP User-Agent string from the command line so that an appropriate stylesheet for that User-Agent type will be selected as part of command-line processing of the page.

The /xdk/java/xsql/bin directory contains a platform-specific command script to automate invoking the XSQL command-line utility. This script sets up the Java runtime to run oracle.xml.xsql.XSQLCommandLine class.

Overview of All XSQL Pages Capabilities

So far we've only seen a single XSQL action element, the <xsql:query> action. This is by far the most popular action, but it is not the only one that comes built-in to the XSQL Pages framework. We explore the full set of functionality that you can exploit in your XSQL pages in the following sections.

Using All of the Core Built-in Actions

This section provides a list of the core built-in actions, including a brief description of what each action does, and a listing of all required and optional attributes that each supports.

The <xsql:query> Action

The <xsql:query> action element executes a SQL select statement and includes a canonical XML representation of the query's result set in the data page. This action requires a database connection to be provided by supplying a connection="connname" attribute on the document element of the XSQL page in which it appears.

The syntax for the action is:

<xsql:query>
   SELECT Statement
</xsql:query>

Any legal SQL select statement is allowed. If the select statement produces no rows, a fallback query can be provided by including a nested <xsql:no-rows-query> element like this:

<xsql:query>
  SELECT Statement
  <xsql:no-rows-query>
    SELECT Statement to use if outer query returns no rows
  </xsql:no-rows-query>
</xsql:query>

An <xsql:no-rows-query> element can itself contain nested <xsql:no-rows-query> elements to any level of nesting. The options available on the <xsql:no-rows-query> are identical to those available on the <xsql:query> action element.

By default, the XML produced by a query will reflect the column structure of its resultset, with element names matching the names of the columns. Columns in the result with nested structure like:

  • Object Types

  • Collection Types

  • CURSOR Expressions

produce nested elements that reflect this structure. The result of a typical query containing different types of columns and returning one row might look like this:

<ROWSET>
  <ROW id="1">
    <VARCHARCOL>Value</VARCHARCOL>
    <NUMBERCOL>12345</NUMBERCOL>
    <DATECOL>12/10/2001 10:13:22</DATECOL>
    <OBJECTCOL>
       <ATTR1>Value</ATTR1>
       <ATTR2>Value</ATTR2>
    </OBJECTCOL>
    <COLLECTIONCOL>
       <COLLECTIONCOL_ITEM>
         <ATTR1>Value</ATTR1>
         <ATTR2>Value</ATTR2>
       </COLLECTIONCOL_ITEM>
       <COLLECTIONCOL_ITEM>
         <ATTR1>Value</ATTR1>
         <ATTR2>Value</ATTR2>
       </COLLECTIONCOL_ITEM>
    </COLLECTIONCOL>
    <CURSORCOL>
      <CURSORCOL_ROW>
        <COL1>Value1</COL1>
        <COL2>Value2</COL2>
      </CURSORCOR_ROW>
    </CURSORCOL>
  </ROW>
</ROWSET>

A <ROW> element will repeat for each row in the result set. Your query can use standard SQL column aliasing to rename the columns in the result, and in doing so effectively rename the XML elements that are produced as well. Such column aliasing is required for columns whose names otherwise are a illegal names for an XML element.

For example, an <xsql:query> action like this:

<xsql:query>SELECT TO_CHAR(hire_date,'DD-MON') FROM employees</xsql:query>

produces an error because the default column name for the calculated expression will be an illegal XML element name. You can fix the problem with column aliasing like this:

<xsql:query>
  SELECT TO_CHAR(hire_date,'DD-MON') as hiredate FROM employees
</xsql:query>

The optional attributes listed in Table 8-1 can be supplied to control various aspects of the data retrieved and the XML produced by the <xsql:query> action.

Table 8-1 Attributes for <xsql:query>

Attribute Name Description
bind-params = "string"
Ordered, space-delimited list of one or more XSQL parameter names whose values will be used to bind to the JDBC bind variable in the appropriate sequential position in the SQL statement.
date-format = "string"
Date format mask to use for formatted date column/attribute values in XML being queried. Valid values are those documented for the java.text.SimpleDateFormat class.
error-param = "string"
Name of a page-private parameter that must be set to the string 'Error' if a non-fatal error occurs while processing this action. Valid value is any parameter name.
error-statement = "boolean"
If set to no, suppresses the inclusion of the offending SQL statement in any <xsql-error> element generated. Valid values are yes and no. The default value is yes.
fetch-size = "integer"
Number of records to fetch in each round-trip to the database. If not set, the default value is used as specified by the /XSQLConfig/processor/default-fetch-size configuration setting in XSQLConfig.xml
id-attribute = "string"
XML attribute name to use instead of the default num attribute for uniquely identifying each row in the result set. If the value of this attribute is the empty string, the row id attribute is suppressed.
id-attribute-column = "string"
Case-sensitive name of the column in the result set whose value must be used in each row as the value of the row id attribute. The default is to use the row count as the value of the row id attribute.
include-schema = "boolean"
If set to yes, includes an inline XML schema that describes the structure of the result set. Valid values are yes and no. The default value is no.
max-rows = "integer"
Maximum number of rows to fetch, after optionally skipping the number of rows indicated by the skip-rows attribute. If not specified, default is to fetch all rows.
null-indicator = "boolean"
Indicates whether to signal that a column's value is NULL by including the NULL="Y" attribute on the element for the column. By default, columns with NULL values are omitted from the output. Valid values are yes and no. The default value is no.
row-element = "string"
XML element name to use instead of the default <ROW> element name for the entire rowset of query results. Set to the empty string to suppress generating a containing <ROW> element for each row in the result set.
rowset-element = "string"
XML element name to use instead of the default <ROWSET> element name for the entire rowset of query results. Set to the empty string to suppress generating a containing <ROWSET> element.
skip-rows = "integer"
Number of rows to skip before fetching rows from the result set. Can be combined with max-rows for stateless paging through query results.
tag-case = "string"
Valid values are lower and upper. If not specified, the default is to use the case of column names as specified in the query as corresponding XML element names.

The <xsql:dml> Action

You can use the <xsql:dml> action to perform any DML or DDL operation, as well as any PL/SQL block. This action requires a database connection to be provided by supplying a connection="connname" attribute on the document element of the XSQL page in which it appears.

The syntax for the action is:

<xsql:dml>
  DML Statement or DDL Statement or PL/SQL Block
</xsql:dml>

Table 8-2 lists the optional attributes that you can use on the <xsql:dml> action.

Table 8-2 Attributes for <xsql:dml>

Attribute Name Description
commit = "boolean"
If set to yes, calls commit on the current connection after a successful execution of the DML statement. Valid values are yes and no. The default value is no.

bind-params = "string"
Ordered, space-delimited list of one or more XSQL parameter names whose values will be used to bind to the JDBC bind variable in the appropriate sequential position in the SQL statement.
error-param = "string"
Name of a page-private parameter that must be set to the string 'Error' if a non-fatal error occurs while processing this action. Valid value is any parameter name.
error-statement = "boolean"
If set to no, suppresses the inclusion of the offending SQL statement in any <xsql-error> element generated. Valid values are yes and no. The default value is yes.

The <xsql:ref-cursor-function> Action

The <xsql:ref-cursor-function> action enables you to include the XML results produced by a query whose result set is determined by executing a PL/SQL stored function. This action requires a database connection to be provided by supplying a connection="connname" attribute on the document element of the XSQL page in which it appears.

By exploiting PL/SQL's dynamic SQL capabilities, the query can be dynamically and conditionally (or conditionally) constructed by the function before a cursor handle to its result set is returned to the XSQL page processor. As its name implies, the return value of the function being invoked must be of type REF CURSOR.

The syntax of the action is:

<xsql:ref-cursor-function>
  [SCHEMA.][PACKAGE.]FUNCTION_NAME(args);
</xsql:ref-cursor-function>

With the exception of the fetch-size attribute, the optional attributes available for the <xsql:ref-cursor-function> action are exactly the same as for the <xsql:query> action that are listed Table 8-1.

For example, consider the PL/SQL package:

CREATE OR REPLACE PACKAGE DynCursor IS
  TYPE ref_cursor IS REF CURSOR;
  FUNCTION DynamicQuery(id NUMBER) RETURN ref_cursor;
END;
CREATE OR REPLACE PACKAGE BODY DynCursor IS
  FUNCTION DynamicQuery(id NUMBER) RETURN ref_cursor IS
    the_cursor ref_cursor;
  BEGIN
   -- Conditionally return a dynamic query as a REF CURSOR
   IF id = 1 THEN
     OPEN the_cursor  -- An employees Query
      FOR 'SELECT employee_id, email FROM employees';
   ELSE
     OPEN the_cursor  -- A departments Query
       FOR 'SELECT department_name, department_id FROM departments'; 
  END IF;
   RETURN the_cursor;
  END;
END;

An <xsql:ref-cursor-function> can include the dynamic results of the REF CURSOR returned by this function by doing:

<xsql:ref-cursor-function> 
  DynCursor.DynamicQuery(1);
</xsql:ref-cursor-function>

The <xsql:include-owa> Action

The <xsql:include-owa> action enables you to include XML content that has been generated by a database stored procedure. This action requires a database connection to be provided by supplying a connection="connname" attribute on the document element of the XSQL page in which it appears.

The stored procedure uses the standard Oracle Web Agent (OWA) packages (HTP and HTF) to "print" the XML tags into the server-side page buffer, then the XSQL page processor fetches, parses, and includes the dynamically-produced XML content in the data page. The stored procedure must generate a well-formed XML page or an appropriate error is displayed.

The syntax for the action is:

<xsql:include-owa>
   PL/SQL Block invoking a procedure that uses the HTP and HTF (or HTF) packages
</xsql:include-owa>

Table 8-3 lists the optional attributes supported by this action.

Table 8-3 Attributes for <xsql:include-owa>

Attribute Name Description
bind-params = "string"
Ordered, space-delimited list of one or more XSQL parameter names whose values will be used to bind to the JDBC bind variable in the appropriate sequential position in the SQL statement.
error-param = "string"
Name of a page-private parameter that must be set to the string 'Error' if a non-fatal error occurs while processing this action. Valid value is any parameter name.
error-statement = "boolean"
If set to no, suppresses the inclusion of the offending SQL statement in any <xsql-error> element generated. Valid values are yes and no. The default value is yes.

Using Bind Variables

To parameterize the results of any of the preceding actions, you can use SQL bind variables. This enables your XSQL page template to produce different results based on the value of parameters passed in the request. To use a bind variable, simply include a question mark anywhere in the statement where bind variables are allowed by SQL. For example, your <xsql:query> action might contain the select statement:

SELECT s.ticker as "Symbol", s.last_traded_price as "Price"
  FROM latest_stocks s, customer_portfolio p
 WHERE p.customer_id = ?
   AND s.ticker = p.ticker

Using a question mark to create a bind-variable for the customer id. Whenever the SQL statement is executed in the page, parameter values are bound to the bind variable by specifying the bind-params attribute on the action element. Using the example preceding, we can create an XSQL page that binds the indicated bind variables to the value of the custid parameter in the page request like this:

<!-- CustomerPortfolio.xsql -->
<portfolio connnection="prod" xmlns:xsql="urn:oracle-xsql">
  <xsql:query bind-params="custid">
    SELECT s.ticker as "Symbol", s.last_traded_price as "Price"
      FROM latest_stocks s, customer_portfolio p
     WHERE p.customer_id = ?
       AND s.ticker = p.ticker
  </xsql:query>
</portfolio>

The XML data for a particular customer's portfolio can then be requested by passing the customer id parameter in the request like this:

http://yourserver.com/fin/CustomerPortfolio.xsql?custid=1001

The value of the bind-params attribute is a space-delimited list of parameter names whose left-to-right order indicates the positional bind variable to which its value will be bound in the statement. So, if your SQL statement has five question marks, then your bind-params attribute needs a space-delimited list of five parameter names. If the same parameter value needs to be bound to several different occurrences of a question-mark-indicated bind variable, you simply repeat the name of the parameters in the value of the bind-params attribute at the appropriate position. Failure to include exactly as many parameter names in the bind-params attribute as there are question marks in the query, will results in an error when the page is executed.

Bind variables can be used in any action that expects a SQL statement. The following page gives additional examples:

<!-- CustomerPortfolio.xsql -->
<portfolio connnection="prod" xmlns:xsql="urn:oracle-xsql">
  <xsql:dml commit="yes" bind-params="useridCookie">
     BEGIN log_user_hit(?); END;
  </xsql:dml>
  <current-prices>
    <xsql:query bind-params="custid">
      SELECT s.ticker as "Symbol", s.last_traded_price as "Price"
        FROM latest_stocks s, customer_portfolio p
       WHERE p.customer_id = ?
         AND s.ticker = p.ticker
    </xsql:query>
  </current-prices>
  <analysis>
    <xsql:include-owa bind-params="custid userCookie">
      BEGIN portfolio_analysis.historical_data(?,5 /* years */, ?); END;
    </xsql:include-owa>
  </analysis>
</portfolio>

Using Lexical Substitution Parameters

For any XSQL action element, you can substitute the value of any attribute, or the text of any contained SQL statement, by using a lexical substitution parameter. This enables you to parameterize how the actions behave as well as substitute parts of the SQL statements they perform. Lexical substitution parameters are referenced using the syntax {@ParameterName}.

The following example illustrates using two lexical substitution parameters, one which allows the maximum number of rows to be passed in as a parameter, and the other which controls the list of columns to ORDER BY.

<!-- DevOpenBugs.xsql -->
<open-bugs connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:query max-rows="{@max}" bind-params="dev prod">
    SELECT bugno, abstract, status
      FROM bug_table
     WHERE programmer_assigned = UPPER(?)
       AND product_id          = ?
       AND status < 80
    ORDER BY {@orderby}
  </xsql:query>
</open-bugs>

This example can then show the XML for a given developer's open bug list by requesting the URL:

http://yourserver.com/bug/DevOpenBugs.xsql?dev=smuench&prod=817

or using the XSQL Command-Line Utility to request:

$ xsql DevOpenBugs.xsql dev=smuench prod=817

We close by noting that lexical parameters can also be used to parameterize the XSQL page connection, as well as parameterize the stylesheet that is used to process the page like this:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="{@sheet}.xsl"?>
<!-- DevOpenBugs.xsql -->
<open-bugs connection="{@conn}" xmlns:xsql="urn:oracle-xsql">
  <xsql:query max-rows="{@max}" bind-params="dev prod">
    SELECT bugno, abstract, status
      FROM bug_table
     WHERE programmer_assigned = UPPER(?)
       AND product_id          = ?
       AND status < 80
    ORDER BY {@orderby}
  </xsql:query>
</open-bugs>

Providing Default Values for Bind Variables and Parameters

It is often convenient to provide a default value for a bind variable or a substitution parameter directly in the page. This allows the page to be parameterized without requiring the requester to explicitly pass in all the values in each request.

To include a default value for a parameter, simply add an XML attribute of the same name as the parameter to the action element, or to any ancestor element. If a value for a given parameter is not included in the request, the XSQL page processor looks for an attribute by the same name on the current action element. If it doesn't find one, it keeps looking for such an attribute on each ancestor element of the current action element until it gets to the document element of the page.

As a simple example, the following page defaults the value of the max parameter to 10 for both <xsql:query> actions in the page:

<example max="10" connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:query max-rows="{@max}">SELECT * FROM TABLE1</xsql:query>
  <xsql:query max-rows="{@max}">SELECT * FROM TABLE2</xsql:query>
</example>

This example defaults the first query to have a max of 5, the second query to have a max of 7 and the third query to have a max of 10.

<example max="10" connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:query max="5" max-rows="{@max}">SELECT * FROM TABLE1</xsql:query>
  <xsql:query max="7" max-rows="{@max}">SELECT * FROM TABLE2</xsql:query>
  <xsql:query max-rows="{@max}">SELECT * FROM TABLE3</xsql:query>
</example>

Of course, all of these defaults are overridden if a value of max is supplied in the request like:

http://yourserver.com/example.xsql?max=3

Bind variables respect the same defaulting rules, so a not very useful, yet educational page, like this:

<example val="10" connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:query tag-case="lower" bind-params="val val val">
    SELECT ? as somevalue
      FROM DUAL
     WHERE ? = ?
  </xsql:query>
</example>

returns the XML datagram:

<example>
  <rowset>
    <row>
      <somevalue>10</somevalue>
    </row>
  </row>
</example>

if the page were requested without any parameters, while a request like:

http://yourserver.com/example.xsql?val=3

returns:

<example>
  <rowset>
    <row>
      <somevalue>3</somevalue>
    </row>
  </row>
</example>

To illustrate an important point for bind variables, imagine removing the default value for the val parameter from the page by removing the val attribute like this:

<example connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:query tag-case="lower" bind-params="val val val">
    SELECT ? as somevalue
      FROM DUAL
     WHERE ? = ?
  </xsql:query>
</example>

Now a request for the page without supplying any parameters returns:

<example>
  <rowset/>
</example>

because a bind variable that is bound to a parameter with neither a default value nor a value supplied in the request will be bound to NULL, causing the WHERE clause in our example page preceding to return no rows.

Understanding the Different Kinds of Parameters

XSQL pages can make use of parameters supplied in the request, as well as page-private parameters whose names and values are determined by actions in the page. If an action encounters a reference to a parameter named param in either a bind-params attribute or in a lexical parameter reference, the value of the param parameter is resolved by using:

  1. The value of the page-private parameter named param, if set, otherwise

  2. The value of the request parameter named param, if supplied, otherwise

  3. The default value provided by an attribute named param on the current action element or one of its ancestor elements, otherwise

  4. The value NULL for bind variables and the empty string for lexical parameters

For XSQL pages that are processed by the XSQL Servlet over HTTP, two additional HTTP-specific type of parameters are available to be set and referenced. These are HTTP-Session-level variables and HTTP Cookies. For XSQL pages processed through the XSQL Servlet, the parameter value resolution scheme is augmented as follows. The value of a parameter param is resolved by using:

  1. The value of the page-private parameter param, if set, otherwise

  2. The value of the cookie named param, if set, otherwise

  3. The value of the session variable named param, if set, otherwise

  4. The value of the request parameter named param, if supplied, otherwise

  5. The default value provided by an attribute named param on the current action element or one of its ancestor elements, otherwise

  6. The value NULL for bind variables and the empty string for lexical parameters

The resolution order is arranged this way so that users cannot supply parameter values in a request to override parameters of the same name that have been set in the HTTP session — whose lifetime is the duration of the HTTP session and controlled by your Web server — or set as cookies, which can bet set to "live" across browser sessions.

The <xsql:include-request-params> Action

The <xsql:include-request-params> action enables you to include an XML representation of all parameters in the request in your datagram. This is useful if your associated XSLT stylesheet wants to refer to any of the request parameter values by using XPath expressions.

The syntax of the action is:

<xsql:include-request-params/>

The XML included will have the form:

<request>
  <parameters>
    <paramname>value1</paramname>
    <ParamName2>value2</ParamName2>
     ...
  </parameters>
</request>

or the form:

<request>
  <parameters>
    <paramname>value1</paramname>
    <ParamName2>value2</ParamName2>
     ...
  </parameters>
  <session>
    <sessVarName>value1</sessVarName>
     ...
  </session>
  <cookies>
    <cookieName>value1</cookieName>
     ...
  </cookies>
</request>

when processing pages through the XSQL Servlet.

This action has no required or optional attributes.

The <xsql:include-param> Action

The <xsql:include-param> action enables you to include an XML representation of a single parameter in your datagram. This is useful if your associated XSLT stylesheet wants to refer to the parameter's value by using an XPath expression.

The syntax of the action is:

<xsql:include-param name="paramname" />

This name attribute is required, and supplies the name of the parameter whose value you want to include. This action has no optional attributes.

If you provide a simple parameter name like this:

<xsql:include-param name="productid"/>

Then the XML fragment included in the data page will be:

<productid>12345</productid>

If you use an array-parameter name to indicate that you want to treat the value as an array, like this:

<xsql:include-param name="productid[]"/>

then the XML fragment will reflect all of the array values like this:

<productid>
  <value>12345<value>
  <value>33455</value>
  <value>88199</value>
</productid>

In this array-parameter name scenario, if productid happens to be a single-valued parameter, then the fragment will look as if it were a one-element array like this:

<productid>
  <value>12345<value>
</productid>

The <xsql:include-xml> Action

The <xsql:include-xml> action includes the XML contents of a local, remote, or database-driven XML resource into your datagram. The resource is specified either by URL or a SQL statement.

The syntax for this action is:

<xsql:include-xml href="URL"/>

or

<xsql:include-xml>
  SQL select statement selecting a single row containing a single
  CLOB or VARCHAR2 column value
</xsql:include-xml>

The URL can be an absolute, http-based URL to retrieve XML from another Web site, or a relative URL. The href attribute and the SQL statement are mutually exclusive. If one is provided the other is not allowed.

Table 8-5 lists the attributes supported by this action. Attributes in bold are required.

Table 8-4 Attributes for <xsql:include-xml>

Attribute Name Description
bind-params = "string"
Ordered, space-delimited list of one or more XSQL parameter names whose values will be used to bind to the JDBC bind variable in the appropriate sequential position in the SQL statement.
error-param = "string"
Name of a page-private parameter that must be set to the string 'Error' if a non-fatal error occurs while processing this action. Valid value is any parameter name.

The <xsql:include-posted-xml> Action

The <xsql:include-posted-xml> action includes the XML document that has been posted in the request into the XSQL page. If an HTML form is posted instead of an XML document, the XML included will be similar to that included by the <xsql:include-request-params> action.

The <xsql:set-page-param> Action

The <xsql:set-page-param> action sets a page-private parameter to a value. The value can be supplied by a combination of static text and other parameter values, or alternatively from the result of a SQL select statement.

The syntax for this action is:

<xsql:set-page-param name="paramname" value="value"/>

or

<xsql:set-page-param name="paramname">
  SQL select statement
</xsql:set-page-param>

or

<xsql:set-page-param name="paramname" xpath="XPathExpression"/>

If you use the SQL statement option, a single row is fetched from the result set and the parameter is assigned the value of the first column. This usage requires a database connection to be provided by supplying a connection="connname" attribute on the document element of the XSQL page in which it appears.

As an alternative to providing the value attribute, or a SQL statement, you can supply the xpath attribute to set the page-level parameter to the value of an XPath expression. The XPath expression is evaluated against an XML document or HTML form that has been posted to the XSQL Page Processor. The value of the xpath attribute can be any valid XPath expression, optionally built using XSQL parameters as part of the attribute value like any other XSQL action element.

Once a page-private parameter is set, subsequent action handlers can use this value as a lexical parameter, for example {@po_id}, or as a SQL bind parameter value by referencing its name in the bind-params attribute of any action handler that supports SQL operations.

If you need to set several session parameter values based on the results of a single SQL statement, instead of using the name attribute, you can use the names attribute and supply a space-or-comma-delimited list of one or more session parameter names. For example:

<xsql:set-page-param names="paramname1 paramname2 paramname3">
  SELECT expression_or_column1, expression_or_column2, expression_or_column3
    FROM table
   WHERE clause_identifying_a_single_row
</xsql:set-page-param>

Either the name or the names attribute is required. The value attribute and the contained SQL statement are mutually exclusive. If one is supplied, the other must not be.

Table 8-5 lists the attributes supported by this action. Attributes in bold are required.

Table 8-5 Attributes for <xsql:set-page-param>

Attribute Name Description
name = "string"
Name of the page-private parameter whose value you want to set.
names = "string string ..."
Space-or-comma-delimited list of the page parameter names whose values you want to set. Either use the name or the names attribute, but not both.
bind-params = "string"
Ordered, space-delimited list of one or more XSQL parameter names whose values will be used to bind to the JDBC bind variable in the appropriate sequential position in the SQL statement.
error-param = "string"
Name of a page-private parameter that must be set to the string 'Error' if a non-fatal error occurs while processing this action. Valid value is any parameter name.
ignore-empty-value = "boolean"
Indicates whether the page-level parameter assignment is ignored if the value to which it is being assigned is an empty string.Valid values are yes and no. The default value is no.
treat-list-as-array = "boolean"
Indicates whether the string-value being assigned to the parameter is tokenized into an array of separate values before assignment. If any comma is present in the string, then the comma is used for separating tokens, otherwise spaces are used.Valid values are yes and no. The default value is yes if the parameter name being set is an array parameter name (for example, myparam[]), and default is no if the parameter name being set is a simple-valued parameter name like myparam.
iquote-array-values = "boolean"
If the parameter name being set is a simple-valued parameter name (for example, myparam) and if the treat-list-as-array="yes" has been specified, then specifying quote-array-values="yes" will surround each string token with single quotes before separating the values with commas. Valid values are yes and no. The default value is no.
xpath = "XPathExpression"
Sets the value of the parameter to an XPath expression evaluated against an XML document or HTML form that has been posted to the XSQL Page Processor.

The <xsql:set-session-param> Action

The <xsql:set-session-param> action sets an HTTP session-level parameter to a value. The value of the session-level parameter remains for the lifetime of the current browser user's HTTP session, which is controlled by the Web server. The value can be supplied by a combination of static text and other parameter values, or alternatively from the result of a SQL select statement.

Since this feature is specific to Java Servlets, this action is only effective if the XSQL page in which it appears is being processed by the XSQL Servlet. If this action is encountered in an XSQL page being processed by the XSQL command-line utility or the XSQLRequest programmatic API, this action is a no-op.

The syntax for this action is:

<xsql:set-session-param name="paramname" value="value"/>

or

<xsql:set-session-param name="paramname">
  SQL select statement
</xsql:set-session-param>

If you use the SQL statement option, a single row is fetched from the result set and the parameter is assigned the value of the first column. This use requires a database connection to be provided by supplying a connection="connname" attribute on the document element of the XSQL page in which it appears.

If you need to set several session parameter values based on the results of a single SQL statement, instead of using the name attribute, you can use the names attribute and supply a space-or-comma-delimited list of one or more session parameter names. For example:

<xsql:set-session-param names="paramname1 paramname2 paramname3">
  SELECT expression_or_column1, expression_or_column2, expression_or_column3
    FROM table
   WHERE clause_identifying_a_single_row
</xsql:set-session-param>

Either the name or the names attribute is required. The value attribute and the contained SQL statement are mutually exclusive. If one is supplied, the other must not be.

Table 8-6 lists the optional attributes supported by this action.

Table 8-6 Attributes for <xsql:set-session-param>

Attribute Name Description
name = "string"
Name of the session-level variable whose value you want to set.
names = "string string ..."
Space-or-comma-delimited list of the session parameter names whose values you want to set. Either use the name or the names attribute, but not both.
bind-params = "string"
Ordered, space-delimited list of one or more XSQL parameter names whose values will be used to bind to the JDBC bind variable in the appropriate sequential position in the SQL statement.
error-param = "string"
Name of a page-private parameter that is set to the string 'Error' if a non-fatal error occurs while processing this action. Valid value is any parameter name.
ignore-empty-value = "boolean"
Indicates whether the session-level parameter assignment is ignored if the value to which it is being assigned is an empty string.Valid values are yes and no. The default value is no.
only-if-unset = "boolean"
Indicates whether the session variable assignment only occurs when the session variable currently does not exists.Valid values are yes and no. The default value is no.

The <xsql:set-cookie> Action

The <xsql:set-cookie> action sets an HTTP cookie to a value. By default, the value of the cookie remains for the lifetime of the current browser, but its lifetime can be changed by supplying the optional max-age attribute. The value to be assigned to the cookie can be supplied by a combination of static text and other parameter values, or alternatively from the result of a SQL select statement.

Since this feature is specific to the HTTP protocol, this action is only effective if the XSQL page in which it appears is being processed by the XSQL Servlet. If this action is encountered in an XSQL page being processed by the XSQL command-line utility or the XSQLRequest programmatic API, this action is a no-op.

The syntax for this action is:

<xsql:set-cookie name="paramname" value="value"/>

or

<xsql:set-cookie name="paramname">
  SQL select statement
</xsql:set-cookie>

If you use the SQL statement option, a single row is fetched from the result set and the parameter is assigned the value of the first column. This use requires a database connection to be provided by supplying a connection="connname" attribute on the document element of the XSQL page in which it appears.

If you need to set several cookie values based on the results of a single SQL statement, instead of using the name attribute, you can use the names attribute and supply a space-or-comma-delimited list of one or more cookie names. For example:

<xsql:set-cookie names="paramname1 paramname2 paramname3">
  SELECT expression_or_column1, expression_or_column2, expression_or_column3
    FROM table
   WHERE clause_identifying_a_single_row
</xsql:set-cookie>

Either the name or the names attribute is required. The value attribute and the contained SQL statement are mutually exclusive. If one is supplied, the other must not be. The number of columns in the select list must match the number of cookies being set or an error message will result.

Table 8-7 lists the optional attributes supported by this action.

Table 8-7 Attributes for <xsql:set-cookie>

Attribute Name Description
name = "string"
Name of the cookie whose value you want to set.
names = "string string ..."
Space-or-comma-delimited list of the cookie names whose values you want to set. Either use the name or the names attribute, but not both.
bind-params = "string"
Ordered, space-delimited list of one or more XSQL parameter names whose values will be used to bind to the JDBC bind variable in the appropriate sequential position in the SQL statement.
domain = "string"
Domain in which cookie value is valid and readable. If domain is not set explicitly, then it defaults to the fully-qualified host name (for example, bigserver.yourcompany.com) of the document creating the cookie.
error-param = "string"
Name of a page-private parameter that is set to the string 'Error' if a non-fatal error occurs while processing this action. Valid value is any parameter name.
ignore-empty-value = "boolean"
Indicates whether the cookie assignment is ignored if the value to which it is being assigned is an empty string.Valid values are yes and no. The default value is no.
max-age = "integer"
Sets the maximum age of the cookie in seconds. Default is to set the cookie to expire when users current browser session terminates.
only-if-unset = "boolean"
Indicates whether the cookie assignment only occurs when the cookie currently does not exists.Valid values are yes and no. The default value is no.
path = "string"
Relative URL path within domain in which cookie value is valid and readable. If path is not set explicitly, then it defaults to the URL path of the document creating the cookie.
immediate = "boolean"
Indicates whether the cookie assignment is immediately visible to the current page. Typically cookies set in the current request are not visible until the browser sends them back to the server in a subsequent request.Valid values are yes and no. The default value is no.

The <xsql:set-stylesheet-param> Action

The <xsql:set-stylesheet-param> action sets a top-level XSLT stylesheet parameter to a value. The value can be supplied by a combination of static text and other parameter values, or alternatively from the result of a SQL select statement. The stylesheet parameter will be set on any stylesheet used during the processing of the current page.

The syntax for this action is:

<xsql:set-stylesheet-param name="paramname" value="value"/>

or

<xsql:set-stylesheet-param name="paramname">
  SQL select statement
</xsql:set-stylesheet-param>

If you use the SQL statement option, a single row is fetched from the result set and the parameter is assigned the value of the first column. This use requires a database connection to be provided by supplying a connection="connname" attribute on the document element of the XSQL page in which it appears.

If you need to set several stylesheet parameter values based on the results of a single SQL statement, instead of using the name attribute, you can use the names attribute and supply a space-or-comma-delimited list of one or more stylesheet parameter names. For example:

<xsql:set-stylesheet-param names="paramname1 paramname2 paramname3">
  SELECT expression_or_column1, expression_or_column2, expression_or_column3
    FROM table
   WHERE clause_identifying_a_single_row
</xsql:set-stylesheet-param>

Either the name or the names attribute is required. The value attribute and the contained SQL statement are mutually exclusive. If one is supplied, the other must not be.

Table 8-8 lists the optional attributes supported by this action.

Table 8-8 Attributes for <xsql:set-stylesheet-param>

Attribute Name Description
name = "string"
Name of the top-level stylesheet parameter whose value you want to set.
names = "string string ..."
Space-or-comma-delimited list of the top-level stylesheet parameter names whose values you want to set. Either use the name or the names attribute, but not both.
bind-params = "string"
Ordered, space-delimited list of one or more XSQL parameter names whose values will be used to bind to the JDBC bind variable in the appropriate sequential position in the SQL statement.
error-param = "string"
Name of a page-private parameter that has to be set to the string 'Error' if a non-fatal error occurs while processing this action. Valid value is any parameter name.
ignore-empty-value = "boolean"
Indicates whether the stylesheet parameter assignment is to be ignored if the value to which it is being assigned is an empty string.Valid values are yes and no. The default value is no.

Working with Array-Valued Parameters

In addition to support for simple-string values, request parameters, session parameters, and page-private parameters may have values that are arrays of strings. To treat to the value of a parameter as an array, you add two empty square brackets to the end of its name. For example, if an HTML form is posted having four occurrences of a input control named productid, then to refer to the array-valued productid parameter you use the notation productid[].

If you refer to an array-valued parameter as a lexical substitution parameter, either inside an action handler attribute value or inside the content of an action handler element, its value will be converted to a comma-delimited list of all non-null and non-empty strings in the array in the order that they appear in the array. For example, if you had a page like:

<page xmlns:xsql="urn:oracle-xsql">
  <xsql:query>
    select description
      from product
     where productid in ( {@productid[]} )  /* Using lexical parameter */
  </xsql:query>
</page>

and the request contains four values for the productid parameter, then the {@productid[]} lexical substitution expression will be replaced in the query by a string like "111,222,333,444".

If you refer to an array-valued parameter without using the array-brackets notation on the end of the name, then the value used will be the value of the first array entry


Note:

Use of a number inside the array brackets is not supported. That is, you can refer to productid or productid[], but not productid[2]. Only the request parameters, page-private parameters, and session parameters can use string arrays. The <xsql:set-stylesheet-param> and <xsql:set-cookie> only support working with parameters as simple string values. To refer to a multi-valued parameter in your XSLT stylesheet, use <xsql:include-param> to include the multi-valued parameter into your XSQL datapage, then use an appropriate XPath expression in the stylesheet to refer to the values from the datapage.

Setting Array-Valued Page or Session Parameters from Strings

You can set the value of a page-private parameter or session parameter to a string-array value simply by using the array-brackets notation on the name like this:

<!-- Note, param name contains array brackets -->
<xsql:set-page-param name="names[]" value="Tom Jane Joe"/>

or similarly for session parameters:

<!-- Note, param name contains array brackets -->
<xsql:set-session-param name="dates[]" value="12-APR-1962 15-JUL-1968"/>

By default, when the name of the parameter being set is an name with array-brackets, the value will be treated as a space-or-comma-delimited list and tokenized.

The resulting string array value will contain these separate tokens. In the examples earlier, the names[] parameter is the string array {"Tom", "Jane", "Joe"} and the dates[] parameter is the string array {"12-APR-1962", "15-JUL-1968"}.

In order to handle strings that contain spaces, the tokenization algorithm first checks the string being tokenized for the presence of any commas. If at least one comma is found in the string, then commas are used as the token delimiter. So, for example, the following action:

<!-- Note, param name contains array brackets -->
<xsql:set-page-param name="names[]" value="Tom Jones,Jane York"/>

sets the value of the names[] parameter to the string array {"Tom Jones", "Jane York"}.

By default, when you set a parameter whose name does not end with the array-brackets, then the string-tokenization does not occur. So, as in previous releases of XSQL Pages, the following action:

<!-- Note, param name does NOT contain array brackets -->
<xsql:set-page-param name="names" value="Tom Jones,Jane York"/>

Sets a parameter named names to the literal string "Tom Jones,Jane York". For convenience, you can optionally force the string to be tokenized by including the new treat-list-as-array="yes" attribute on the <xsql:set-page-param> or <xsql:set-session-param> actions. The result will be to assign a comma-delimited string of the tokenized values to the parameter. For example, the action:

<!-- Note, param name does NOT contain array brackets -->
<xsql:set-page-param name="names" value="Tom Jane Joe"
                     treat-list-as-array="yes"/>

sets the names parameter to the literal string "Tom,Jane,Joe".

As a further convenience, when you are setting the value of a simple string-valued parameter and you are tokenizing the value using treat-list-as-array="yes", you can include the quote-array-values="yes" attribute to have the comma-delimited values be surrounded by single-quotes. So, an action like this:

<!-- Note, param name does NOT contain array brackets -->
<xsql:set-page-param name="names" value="Tom Jones,Jane York,Jimmy"
                     treat-list-as-array="yes"
                     quote-array-values="yes"/>

assigns the literal string value "'Tom Jones','Jane York','Jimmy'" to the names parameter.

Binding Array-Valued Parameters in SQL and PL/SQL Statements

Anywhere in XSQL Pages where string-valued scalar bind variables are supported, you may also bind array-valued parameters by simply using the array-parameter name (for example, myparam[]) in the list of parameter names that you supply for the bind-params attribute.

This makes it very easy to process array-valued parameters in SQL statements and in PL/SQL procedures. Array-valued parameters are bound as a nested table object type named XSQL_TABLE_OF_VARCHAR that you must create in your current schema using the DDL statement:

CREATE TYPE xsql_table_of_varchar AS TABLE OF VARCHAR2(2000);

While the type must have this exact name, XSQL_TABLE_OF_VARCHAR, you can change the dimension of the VARCHAR2 string if desired. Of course, you have to make it as long as any string value you expect to handle in your array-valued string parameters.

Consider the following PL/SQL stored procedure:

FUNCTION testTableFunction(p_name  XSQL_TABLE_OF_VARCHAR,
                           p_value XSQL_TABLE_OF_VARCHAR)
RETURN VARCHAR2 IS
  lv_ret     VARCHAR2(4000);
  lv_numElts INTEGER;
BEGIN
  IF p_name IS NOT NULL THEN
    lv_numElts := p_name.COUNT;
    FOR j IN 1..lv_numElts LOOP
      IF (j > 1) THEN
        lv_ret := lv_ret||':';
      END IF;
      lv_ret := lv_ret||p_name(j)||'='||p_value(j);
    END LOOP;
  END IF;
  RETURN lv_ret;
END;

The following page illustrates how to bind two array-valued parameters in a SQL statement that uses this PL/SQL function taking XSQL_TABLE_OF_VARCHAR-typed arguments.

<page xmlns:xsql="urn:oracle-xsql" connection="demo"
      someNames="aa,bb,cc" someValues="11,22,33">
  <xsql:query bind-params="someNames[] someValues[]">
    select testTableFunction(?,?) as example from dual
  </xsql:query>
</page>

This produces a resulting XML data page of:

<page someNames="aa,bb,cc" someValues="11,22,33">
  <ROWSET>
    <ROW num="1">
      <EXAMPLE>aa=11:bb=22:cc=33</EXAMPLE>
    </ROW>
  </ROWSET>
</page>

illustrating that the array-valued someNames[] and someValues[] parameters were bound as table collection types and the values were iterated over and concatenated together to produce the "aa=11:bb=22:cc=33" string value as the function's return value.

You can mix any number of regular parameters and array-valued parameters in your bind-params string. Just use the array-bracket notation for the ones you want to be bound as arrays.


Note:

If you try the example earlier and you have not created the XSQL_TABLE_OF_VARCHAR type as illustrated earlier, you will receive an error like this:
<page someNames="aa,bb,cc" someValues="11,22,33">
  <xsql-error code="17074" action="xsql:query">
    <statement>
     select testTableFunction(?,?) as example from dual
    </statement>
    <message>
      invalid name pattern: SCOTT.XSQL_TABLE_OF_VARCHAR
    </message>
  </xsql-error>
</page>

Since the array parameters are bound as nested table collection types, you can use the TABLE() operator in combination with the CAST() operator in SQL to treat the nested table bind variable value as a table of values to query against. This can be quite a powerful technique to use in sub-select clauses of a SQL statement (but it's not limited to this). The following page illustrates using an array-valued parameter containing employee id's to restrict the rows queried from the familiar EMPLOYEES table in the HR schema.

<page xmlns:xsql="urn:oracle-xsql" connection="hr">
  <xsql:set-page-param name="someEmployees[]" value="196,197"/>
  <xsql:query bind-params="someEmployees[]">
    select first_name||' '||last_name as name, salary
      from employees
     where employee_id in (
        select * from TABLE(CAST( ? as xsql_table_of_varchar))
     )
   </xsql:query>
</page>

This produces a result like:

<page>
  <ROWSET>
    <ROW num="1">
      <NAME>Alana Walsh</NAME>
      <SALARY>3100</SALARY>
    </ROW>
    <ROW num="2">
      <NAME>Kevin Feeny</NAME>
      <SALARY>3000</SALARY>
    </ROW>
  </ROWSET>
</page>

These examples have shown using bind-params with <xsql:query>, but these new features work for <xsql:dml>, <xsql:include-owa>, <xsql:ref-cursor-function>, and any other actions that accept SQL or PL/SQL statements as part of their functionality.

Finally, some users might ask, "Why doesn't XSQL support using PL/SQL index-by tables instead of nested table collection types for binding string-array values?" The simple answer is that PL/SQL index-by-tables do not work with the JDBC Thin driver. They only work using the OCI JDBC driver. By using the nested table collection type XSQL_TABLE_OF_VARCHAR we can use the array-valued parameters with both the Thin driver and the OCI driver, without losing any of the programming flexibility of working with the array of values in PL/SQL.

Supplying Multi-Valued Parameters on the Command Line

If you use the oracle.xml.xsql.XSQLCommandLine command-line utility to run XSQL pages, you can supply multi-valued parameters to the XSQL page processor by simply including the same parameter name on the command line multiple times like this:

java oracle.xml.xsql.XSQLCommandLine SomePage.xsql user=Steve user=Paul user=Mary

This will result in having the user[] array-valued parameter set as a request parameter to the value {"Steve","Paul","Mary"}.

Supplying Multi-Valued Parameters Programmatically with XSQLRequest

The XSQLRequest programmatic API to the XSQL Page engine already takes a java.util.Dictionary of named parameters. Typically users have used a Hashtable and called its put(name,value) method to add String-valued parameters to the request. To add multi-valued parameters, simply put a value of type String[] instead of type String.

Conditionally Executing Actions or Including Content with <xsql:if-param>

The <xsql:if-param> action enables you to conditionally include the elements and actions (or actions) that are nested inside it if some condition is true. If the condition evaluates to true, then all nested XML content and actions are included in the page. If the condition evaluates to false, then none of the nested XML content or actions are included (and hence none of the nested actions is executed).

You specify which parameter value will be evaluated by supplying the required name attribute. Both simple parameter names as well as array-parameter names are supported.

In addition to the name attribute, you must also pick exactly one of the following five attributes to indicate how the parameter value (or values, in the array case) is tested:

  1. exists="yes" or exists="no"

    If you use exists="yes", then this tests whether the named parameter exists and has a non-empty value. For an array-valued parameter, it tests whether the array-parameter exists, and has at least one non-empty element. If you use exists="no", then evaluates to true if the parameter does not exist, of if it exists but has an empty value. For an array-valued parameter, it evaluates to true if the parameter does not exist, or if all of the array elements are empty.

  2. equals="stringValue"

    This tests whether the named parameter equals the string value provided. By default the comparison is an exact string match. For an array-valued parameter, it tests whether any element in the array has the indicated value.

  3. not-equals="stringValue"

    This tests whether the named parameter does not equal the string value provided. For an array-valued parameter, evaluates to true if none of the elements in the array has the indicated value.

  4. in-list="comma-or-space-separated-list"

    This tests whether the named parameter matches any of the strings in the provided list. The value of the in-list parameter is tokenized into an array using commas as the delimiter if any commas are detected in the string, otherwise using space as the delimiter. For an array-valued parameter, it tests whether any element in the array matches some element in the list.

  5. not-in-list="comma-or-space-separated-list"

    This tests whether the named parameter does not match any of the strings in the provided list. The value of the not-in-list parameter is tokenized into an array using commas as the delimiter if any commas are detected in the string, otherwise using space as the delimiter. For an array-valued parameter, it tests whether none of the elements in the array matches any element in the list.

For the equals, not-equals, in-list, and not-in-list tests, by default the comparison is an exact string match. If you want a case-insensitive match, supply the additional ignore-case="yes" attribute as well.

As with other XSQL actions, all of the attributes of the <xsql:if-param> action can contain lexical substitution parameter expressions (for example, {@paramName}) if needed.

Note that any XML content and XSQL action elements (or XSQL action elements) can be nested inside an <xsql:if-param>, including other <xsql:if-param> elements if needed.

For example, to test whether two different conditions are true, you can use nested <xsql:if-param> elements like this:

<!-- 
| Set page param 'foo' to value "bar" if parameter 'a'
| exists, and if parameter 'b' has value equal to "X"
+-->
<xsql:if-param name="a" exists="yes">
  <xsql:if-param name="b" equals="X">
    <xsql:set-page-param name="foo" value="bar"/>
  </xsql:if-param>
</xsql:if-param>

Note:

If the parameter being tested does not exist, the test evaluates to false.

Optionally Setting an Error Parameter on Any Built-in Action

It is often convenient to know whether an action encountered a non-fatal error during its execution. For example, an attempt to insert a row or call a stored procedure can fail with a database exception which will get included into your XSQL data page as an <xsql-error> element.

Now you can optionally have any built-in XSQL action set a page-private parameter of your choice when that action reports a non-fatal error by using the error-param attribute on your action.

For example, to have the parameter named "dml-error" set if the statement inside the <xsql:dml> action encounters a database error, use an action like this:

<xsql:dml error-param="dml-error" bind-params="val">
  insert into yourtable(somecol) values(?)
</xsql:dml>

If the execution of this action encounters an error, then the page-private parameter named dml-error will be set to the string "Error".

If the execution of the action is successful, the error parameter is not assigned any value. In the example earlier, this means that if the page-private parameter dml-error already exists, it will retain its current value. If it does not exist, it will continue to not exist.

By using this new error parameter in combination with <xsql:if-param> you can achieve conditional behavior in your XSQL page template, depending on the success or failure of certain actions. For example, assuming your connection definition sets the AUTOCOMMIT flag to false on the connection named "demo" in the XSQL configuration file (by default, named XSQLConfig.xml), then the following page illustrates how you might rollback the changes made by a previous action if a subsequent action encounters an error.

<!-- NOTE: Connection "demo" must not set to autocommit! -->
<page connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:dml error-param="dml-error" bind-params="val">
    insert into yourtable(somecol) values(?)
  </xsql:dml>
  <!-- This second statement will commit if it succeeds -->
  <xsql:dml commit="yes" error-param="dml-error" bind-params="val2">
    insert into anothertable(anothercol) values(?)
  </xsql:dml>
  <xsql:if-param name="dml-error" exists="yes">
    <xsql:dml>rollback</xsql:dml>
  </xsql:if-param>
</page>

If you've written any custom action handlers and your custom actions call reportMissingAttribute(), reportError(), or reportErrorIncludingStatement() to report non-fatal action errors, then they will automatically pickup this new feature as well.

Aggregating Information Using <xsql:include-xsql>

The <xsql:include-xsql> action makes it very easy to include the results of one XSQL page into another page. This enables you to easily aggregate content from a page that you've already built and find another purpose for it. The examples that follow illustrate two of the most common uses of <xsql:include-xsql>.

Assume you have an XSQL page that lists discussion forum categories:

<!-- Categories.xsql -->
<xsql:query connection="forum" xmlns:xsql="urn:oracle-xsql">
  SELECT name
    FROM categories
    ORDER BY name
</xsql:query>

You can include the results of this page into a page that lists the ten most recent topics in the current forum like this:

<!-- TopTenTopics.xsql -->
<top-ten-topics connection="forum" xmlns:xsql="urn:oracle-xsql">
  <topics>
    <xsql:query max-rows="10">
      SELECT subject FROM topics ORDER BY last_modified DESC
    </xsql:query>
  </topics>
  <categories>
    <xsql:include-xsql href="Categories.xsql"/>
  </categories>
</top-ten-topics>

You can use <xsql:include-xsql> to include an existing page to apply an XSLT stylesheet to it as well. So, if we have two different XSLT stylesheets:

  • cats-as-html.xsl, which renders the topics in HTML, and

  • cats-as-wml.xsl, which renders the topics in WML

Then one approach for catering to two different types of devices is to create different XSQL pages for each device. We can create:

<?xml version="1.0"?>
<!-- HTMLCategories.xsql -->
<?xml-stylesheet type="text/xsl" href="cats-as-html.xsl"?>
<xsql:include-xsql href="Categories.xsql" xmlns:xsql="urn:oracle-xsql"/>

which aggregates Categories.xsql and applies the cats-as-html.xsl stylesheet, and another page:

<?xml version="1.0"?>
<!-- WMLCategories.xsql -->
<?xml-stylesheet type="text/xsl" href="cats-as-html.xsl"?>
<xsql:include-xsql href="Categories.xsql" xmlns:xsql="urn:oracle-xsql"/>

which aggregates Categories.xsql and applies the cats-as-wml.xsl stylesheet for delivering to wireless devices. In this way, we've re-purposed the reusable Categories.xsql page content in two different ways.

If the page being aggregated contains an <?xml-stylesheet?> processing instruction, then that stylesheet is applied before the result is aggregated, so using <xsql:include-xsql> you can also easily chain the application of XSLT stylesheets together.

When one XSQL page aggregates another page's content using <xsql:include-xsql> all of the request-level parameters are visible to the "nested" page. For pages processed by the XSQL Servlet, this also includes session-level parameters and cookies, too. As you expect, none of the aggregating page's page-private parameters are visible to the nested page.

Table 8-9 lists the attributes supported by this action. Required attributes are in bold.

Table 8-9 Attributes for <xsql:include-xsql>

Attribute Name Description
href = "string"
Relative or absolute URL of XSQL page to be included.
error-param = "string"
Name of a page-private parameter that has to be set to the string 'Error' if a non-fatal error occurs while processing this action. Valid value is any parameter name.
reparse = "boolean"
Indicates whether output of included XSQL page has to be reparsed before it is included. Useful if included XSQL page is selecting the text of an XML document fragment that the including page wants to treat as elements.Valid values are yes and no. The default value is no.

Including XMLType Query Results

Oracle9i introduced the XMLType for use with storing and querying XML-based database content. You can exploit database XML features to produce XML for inclusion in your XSQL pages using one of two techniques:

  • <xsql:query> handles any query including columns of type XMLType, however it handles XML markup in CLOB/VARCHAR2 columns as literal text.

  • <xsql:include-xml> parses and includes a single CLOB or String-based XML document retrieved from a query

The difference between the two approaches lies in the fact that the <xsql:include-xml> action parses the literal XML appearing in a CLOB or String-value to turn it on the fly into a tree of elements and attributes. On the other hand, using the <xsql:query> action, XML markup appearing in CLOB or String valued-columns is left as literal text.

Another difference is that while <xsql:query> can handle query results of any number of columns and rows, the <xsql:include-xml> is designed to work on a single column of a single row. Accordingly, when using <xsql:include-xml>, the SELECT statement that appears inside it returns a single row containing a single column. The column can either be a CLOB or a VARCHAR2 value containing a well-formed XML document. The XML document will be parsed and included into your XSQL page.

The following example uses nested xmlagg() functions to aggregate the results of a dynamically-constructed XML document containing departments and nested employees into a single XML "result" document, wrapped in a <DepartmentList> element:

<xsql:query connection="hr" xmlns:xsql="urn:oracle-xsql">
  select XmlElement("DepartmentList",
           XmlAgg(
             XmlElement("Department", 
               XmlAttributes(department_id as "Id"),
               XmlForest(department_name as "Name"),
               (select XmlElement("Employees",
                         XmlAgg( 
                           XmlElement("Employee",
                             XmlAttributes(employee_id as "Id"),
                             XmlForest(first_name||' '||last_name as "Name",
                                       salary   as "Salary",
                                       job_id   as "Job")
                           )
                         )
                       )
                 from employees e 
                where e.department_id = d.department_id
               )
             )
           )
         ) as result
   from departments d
  order by department_name
</xsql:query>

Considering another example, suppose you have a number of <Movie> XML documents stored in a table of XmlType called MOVIES. Each document might look something like this:

<Movie Title="The Talented Mr.Ripley" RunningTime="139" Rating="R">
<Director>
<First>Anthony</First>
<Last>Minghella</Last>
</Director>
<Cast>
<Actor Role="Tom Ripley">
<First>Matt</First>
<Last>Damon</Last>
</Actor>
<Actress Role="Marge Sherwood">
<First>Gwyneth</First>
<Last>Paltrow</Last>
</Actress>
<Actor Role="Dickie Greenleaf">
<First>Jude</First>
<Last>Law</Last>
<Award From="BAFTA" Category="Best Supporting Actor"/>
</Actor>
</Cast>
</Movie>

You can use the built-in Oracle XPath query features to extract an aggregate list of all cast members who have received Oscar awards from any movie in the database using a query like this:

 select xmlelement("AwardedActors",
           xmlagg(extract(value(m),
                  '/Movie/Cast/*[Award[@From="Oscar"]]')))
  from movies m

To include this query result of XMLType into your XSQL page, simply paste the query inside an <xsql:query> element, and make sure you include an alias for the query expression (for example "as result" following):

<xsql:query connection="orcl92" xmlns:xsql="urn:oracle-xsql">
  select xmlelement("AwardedActors",
           xmlagg(extract(value(m),
                  '/Movie/Cast/*[Award[@From="Oscar"]]'))) as result
    from movies m
</xsql:query>

Note that again we use the combination of xmlelement() and xmlagg() to have the
 database aggregate all of the XML fragments identified by the query into
  single, well-formed XML document. The combination of xmlelement() and xmlagg()
 work together to produce a well-formed result like this:
<AwardedActors>
  <Actor>...</Actor>
  <Actress>...</Actress>
</AwardedActors>

Notice that you can use the standard XSQL Pages bind variable capabilities in the middle of an XPath expression, too, if you concatenate the bind variable into the expression. For example, to parameterize the value "Oscar" into a parameter named award-from, you can use an XSQL Page like this:

<xsql:query connection="orcl92" xmlns:xsql="urn:oracle-xsql"
            award-from="Oscar" bind-params="award-from">
  /* Using a bind variable in an XPath expression */
  select xmlelement("AwardedActors",
           xmlagg(extract(value(m),
                  '/Movie/Cast/*[Award[@From="'|| ? ||'"]]'))) as result
    from movies m
</xsql:query>

Handling Posted Information

In addition to simplifying the assembly and transformation of XML content, the XSQL Pages framework makes it easy to handle posted XML content as well. Built-in actions simplify the handling of posted information from both XML document and HTML forms, and allow that information to be posted directly into a database table using the underlying facilities of the Oracle XML SQL Utility.

The XML SQL Utility provides the ability to data database inserts, updates, and deletes based on the content of an XML document in canonical form with respect to a target table or view. For a given database table, the canonical XML form of its data is given by one row of XML output from a SELECT * FROM tablename query against it. Given an XML document in this canonical form, the XML SQL Utility can automate the insert, update, and delete for you. By combining the XML SQL Utility with an XSLT transformation, you can transform XML in any format into the canonical format expected by a given table, and then ask the XML SQL Utility to insert, update, delete the resulting canonical XML for you.

The following built-in XSQL actions make exploiting this capability easy from within your XSQL pages:

  • <xsql:insert-request>

    Insert the optionally transformed XML document that was posted in the request into a table.Table 8-10 lists the required and optional attributes supported by this action.

  • <xsql:update-request>

    Update the optionally transformed XML document that was posted in the request into a table or view. Table 8-11 lists the required and optional attributes supported by this action.

  • <xsql:delete-request>

    Delete the optionally transformed XML document that was posted in the request from a table or view. Table 8-12 lists the required and optional attributes supported by this action.

  • <xsql:insert-param>

    Insert the optionally transformed XML document that was posted as the value of a request parameter into a table or view. Table 8-13 lists the required and optional attributes supported by this action.

If you target a database view with your insert, then you can create INSTEAD OF INSERT triggers on the view to further automate the handling of the posted information. For example, an INSTEAD OF INSERT trigger on a view can use PL/SQL to check for the existence of a record and intelligently choose whether to do an INSERT or an UPDATE depending on the result of this check.

Table 8-10 Attributes for <xsql:insert-request>

Attribute Name Description
table = "string"
Name of the table, view, or synonym to use for inserting the XML information.
transform = "URL"
Relative or absolute URL of the XSLT transformation to use to transform the document to be inserted into canonical ROWSET/ROW format.
columns = "string"
Space-delimited or comma-delimited list of one or more column names whose values will be inserted. If supplied, then only these columns will be inserted. If not supplied, all columns will be inserted, with NULL values for columns whose values do not appear in the XML document.
commit = "boolean"
If set to yes, calls commit on the current connection after a successful execution of the insert. Valid values are yes and no. The default value is yes.
commit-batch-size = "integer"
If a positive, nonzero number N is specified, then after each batch of N inserted records, a commit will be issued. Default batch size is zero (0) if not specified, meaning not to commit interim batches.
date-format = "string"
Date format mask to use for interpreting date field values in XML being inserted. Valid values are those documented for the java.text.SimpleDateFormat class.
error-param = "string"
Name of a page-private parameter that must be set to the string 'Error' if a non-fatal error occurs while processing this action. Valid value is any parameter name.

Table 8-11 Attributes for <xsql:update-request>

Attribute Name Description
table = "string"
Name of the table, view, or synonym to use for inserting the XML information.
key-columns = "string"
Space-delimited or comma-delimited list of one or more column names whose values in the posted XML document will be used to identify the existing rows to update.
transform = "URL"
Relative or absolute URL of the XSLT transformation to use to transform the document to be inserted into canonical ROWSET/ROW format.
columns = "string"
Space-delimited or comma-delimited list of one or more column names whose values will be updated. If supplied, then only these columns will be updated. If not supplied, all columns will be updated, with NULL values for columns whose values do not appear in the XML document.
commit = "boolean"
If set to yes, calls commit on the current connection after a successful execution of the update. Valid values are yes and no. The default value is yes.
commit-batch-size = "integer"
If a positive, nonzero number N is specified, then after each batch of N inserted records, a commit will be issued. Default batch size is zero (0) if not specified, meaning not to commit interim batches.
date-format = "string"
Date format mask to use for interpreting date field values in XML being inserted. Valid values are those documented for the java.text.SimpleDateFormat class.
error-param = "string"
Name of a page-private parameter that must be set to the string 'Error' if a non-fatal error occurs while processing this action. Valid value is any parameter name.

Table 8-12 Attributes for <xsql:delete-request>

Attribute Name Description
table = "string"
Name of the table, view, or synonym to use for inserting the XML information.
key-columns = "string"
Space-delimited or comma-delimited list of one or more column names whose values in the posted XML document will be used to identify the existing rows to update.
transform = "URL"
Relative or absolute URL of the XSLT transformation to use to transform the document to be inserted into canonical ROWSET/ROW format.
commit = "boolean"
If set to yes, calls commit on the current connection after a successful execution of the delete. Valid values are yes and no. The default value is yes.
commit-batch-size = "integer"
If a positive, nonzero number N is specified, then after each batch of N inserted records, a commit will be issued. Default batch size is zero (0) if not specified, meaning not to commit interim batches.
error-param = "string"
Name of a page-private parameter that must be set to the string 'Error' if a non-fatal error occurs while processing this action. Valid value is any parameter name.

Table 8-13 Attributes for <xsql:insert-param>

Attribute Name Description
name = "string"
Name of the parameter whose value contains XML to be inserted.
table = "string"
Name of the table, view, or synonym to use for inserting the XML information.
transform = "URL"
Relative or absolute URL of the XSLT transformation to use to transform the document to be inserted into canonical ROWSET/ROW format.
columns = "string"
Space-delimited or comma-delimited list of one or more column names whose values will be inserted. If supplied, then only these columns will be inserted. If not supplied, all columns will be inserted, with NULL values for columns whose values do not appear in the XML document.
commit = "boolean"
If set to yes, calls commit on the current connection after a successful execution of the insert. Valid values are yes and no. The default value is yes.
commit-batch-size = "integer"
If a positive, nonzero number N is specified, then after each batch of N inserted records, a commit will be issued. Default batch size is zero (0) if not specified, meaning not to commit interim batches.
date-format = "string"
Date format mask to use for interpreting date field values in XML being inserted. Valid values are those documented for the java.text.SimpleDateFormat class.
error-param = "string"
Name of a page-private parameter that must be set to the string 'Error' if a non-fatal error occurs while processing this action. Valid value is any parameter name.

Understanding Different XML Posting Options

There are three different ways that the XSQL pages framework can handle posted information.

  1. A client program can send an HTTP POST message that targets an XSQL page, whose request body contains an XML document and whose HTTP header reports a ContentType of "text/xml".

    In this case, you can use the <xsql:insert-request>, <xsql:update-request>, or the <xsql:delete-request> action and the content of the posted XML will be insert, updated, or deleted in the target table as indicated. If you transform the posted XML document using an XSLT transformation, the posted XML document is the source document for this transformation.

  2. A client program can send an HTTP GET request for an XSQL page, one of whose parameters contains an XML document.

    In this case, you can use the <xsql:insert-param> action and the content of the posted XML parameter value will be inserted in the target table as indicated. If you transform the posted XML document using an XSLT transformation, the XML document in the parameter value is the source document for this transformation.

  3. A browser can submit an HTML form with method="POST" whose action targets an XSQL page. In this case, by convention the browser sends an HTTP POST message whose request body contains an encoded version of all of the HTML form's fields and their values with a ContentType of "application/x-www-form-urlencoded"

    In this case, there request does not contain an XML document, but instead an encoded version of the form parameters. However, to make all three of these cases uniform, the XSQL page processor will (on demand) materialize an XML document from the set of form parameters, session variables, and cookies contained in the request. Your XSLT transformation then transforms this dynamically-materialized XML document into canonical form for insert, update, or delete using <xsql:insert>, <xsql:update-request>, or <xsql:delete-request> respectively.

When working with posted HTML forms, the dynamically materialized XML document will have the following form:

<request>
  <parameters>
    <firstparamname>firstparamvalue</firstparamname>
     ... 
    <lastparamname>lastparamvalue</lastparamname>
  </parameters>
  <session>
    <firstparamname>firstsessionparamvalue</firstparamname>
      ...
    <lastparamname>lastsessionparamvalue</lastparamname>
  </session>
  <cookies>
    <firstcookie>firstcookievalue</firstcookiename>
       ... 
    <lastcookie>firstcookievalue</lastcookiename>
  </cookies>
</request>

If multiple parameters are posted with the same name, then they will automatically be "row-ified" to make subsequent processing easier. This means, for example, that a request which posts or includes the following parameters:

  • id = 101

  • name = Steve

  • id = 102

  • name = Sita

  • operation = update

Will create a "row-ified" set of parameters like:

<request>
  <parameters>
    <row>
      <id>101</id>
      <name>Steve</name>
    </row>
    <row>
      <id>102</id>
      <name>Sita</name>
    </row>
    <operation>update</operation>
  </parameters>
      ...
</request>

Since you will need to provide an XSLT stylesheet that transforms this materialized XML document containing the request parameters into canonical format for your target table, it might be useful to build yourself an XSQL page like this:

<!-- 
 | ShowRequestDocument.xsql
 | Show Materialized XML Document for an HTML Form
 +-->
<xsql:include-request-params xmlns:xsql="urn:oracle-xsql"/>

With this page in place, you can temporarily modify your HTML form to post to the ShowRequestDocument.xsql page, and in the browser you will see the "raw" XML for the materialized XML request document which you can save out and use to develop the XSLT transformation.

Using Custom XSQL Action Handlers

When you need to perform tasks that are not handled by the built-in action handlers, the XSQL Pages framework allows custom actions to be invoked to do virtually any kind of job you need done as part of page processing. Custom actions can supply arbitrary XML content to the data page and perform arbitrary processing. See Writing Custom XSQL Action Handlers later in this chapter for more details on writing custom action handlers in Java. Here we explore how to make use of a custom action handler, once it's already created.

To invoke a custom action handler, use the built-in <xsql:action> action element. It has a single, required attribute named handler whose value is the fully-qualified Java class name of the action you want to invoke. The class must implement the oracle.xml.xsql.XSQLActionHandler interface. For example:

<xsql:action handler="yourpackage.YourCustomHandler"/>

Any number of additional attribute can be supplied to the handler in the normal way. For example, if the yourpackage.YourCustomHandler is expecting a attributes named param1 and param2, you use the syntax:

<xsql:action handler="yourpackage.YourCustomHandler" param1="xxx" param2="yyy"/>

Some action handlers, perhaps in addition to attributes, may expect text content or element content to appear inside the <xsql:action> element. If this is the case, simply use the expected syntax like:

<xsql:action handler="yourpackage.YourCustomHandler" param1="xxx" param2="yyy">
   Some Text Goes Here
</xsql:action>

or this:

<xsql:action handler="yourpackage.YourCustomHandler" param1="xxx" param2="yyy">
  <some>
    <other/>
    <elements/>
    <here/>
  </some>   
</xsql:action>

Description of XSQL Servlet Examples

Figure 8-5 lists the XSQL Servlet example applications supplied with the software in the ./demo directory.

Table 8-14 XSQL Servlet Examples

Demonstration Name Description
Hello World

./demo/helloworld

Simplest possible XSQL page.

Do You XML Site ./demo/doyouxml XSQL page which shows how a simple, data-driven Web site can be built using an XSQL page which makes clever use of SQL, XSQL-substitution variables in the queries, and XSLT for formatting the site.

Demonstrates using substitution parameters in both the body of SQL query statements within <xsql:query> tags, as well as within the attributes to <xsql:query> tags to control things like how many records to display and to skip (for "paging" through query results in a stateless way).

Employee Page

./demo/emp

XSQL page showing XML data from the HR schema's EMPLOYEES table, using XSQL page parameters to control what employees are returned and what column(s) to use for the database sort.

Uses an associated XSLT Stylesheet for format the results as an HTML Form containing the emp.xsql page as the form action so the user can refine their search criteria.

Insurance Claim Page

./demo/insclaim

Demonstrates a number of sample queries over the richly-structured, Insurance Claim object view. The insclaim.sql sets up the INSURANCE_CLAIM_VIEW object view and populates some sample data.
Invalid Classes Page ./demo/classerr XSQL Page which uses invalidclasses.xsl to format a "live" list of current Java class compilation errors in your schema. The accompanying SQL script sets up the XSQLJavaClassesView object view used by the demo. The master/detail information from the object view is formatted into HTML by the invalidclasses.xsl stylesheet in the server.
Airport Code Validation ./demo/airport XSQL page returns a "datagram" of information about airports based on their three-letter codes and uses <xsql:no-rows-query> as alternative queries when initial queries return no rows. After attempting to match the airport code passed in, the XSQL page tries a fuzzy match based on the airport description.

airport.htm page demonstrates how to use the XML results of airport.xsql page from a Web page using JavaScript to exploit built-in XML Document Object Model (DOM) functionality in Internet Explorer 5.0.

When you enter the three-letter airport code on the Web page, a JavaScript fetches the XML datagram from XSQL Servlet over the Web corresponding to the code you entered. If the return indicates no match, the program collects a "picklist" of possible matches based on information returned in the XML "datagram" from XSQL Servlet

Airport Code Display ./demo/airport Demonstrates using the same XSQL page as the previous example but supplying an XSLT Stylesheet name in the request. This causes the airport information to be formatted as an HTML form instead of being returned as raw XML.
Airport Code Display ./demo/airport Demonstrates returning Airport information as a SOAP Service.
Emp/Dept Object Demo

./demo/empdept

Demonstrates using an object view to group master/detail information from two existing flat tables like EMP and DEPT. The empdeptobjs.sql script creates the object view (along with INSTEAD OF INSERT triggers allowing the master/detail view to be used as an insert target of xsql:insert-request).

The empdept.xsl stylesheet illustrates an example of the simple form of an XSLT stylesheet that can look just like an HTML page without the extra xsl:stylesheet or xsl:transform at the top. This is part of the XSLT 1.0 specification called using a Literal Result Element as Stylesheet. It also demonstrates how to generate an HTML page that includes the <link rel="stylesheet"> to allow the generated HTML to fully leverage CSS for centralized HTML style information, found in the coolcolors.css file.

Adhoc Query Visualization

./demo/adhocsql

Demonstrates passing the entire SQL query and XSLT Stylesheet to use as parameters to the server.

XML Document Demo ./demo/document Demonstrates inserting XML documents into relational tables. The docdemo.sql script creates a user-defined type called XMLDOCFRAG containing an attribute of type CLOB.

Try inserting the text of the document in ./xsql/demo/xml99.xml and providing the name xml99.xsl as the stylesheet, as well as ./xsql/demo/JDevRelNotes.xml with the stylesheet relnotes.xsl.

The docstyle.xsql page illustrates an example of the <xsql:include-xsql> action element to include the output of the doc.xsql page into its own page before transforming the final output using a client-supplied stylesheet name.

The demo uses the client-side XML features of Internet Explorer 5.0 to check the document for well-formedness before allowing it to be posted to the server.

XML Insert Request Demo ./demo/insertxml Demonstrates posting XML from a client to an XSQL Page that handles inserting the posted XML information into a database table using the <xsql:insert-request> action element. The demo is setup to accept XML documents in the moreover.com XML-based news format.

In this case, the program doing the posting of the XML is a client-side Web page using Internet Explorer 5.0 and the XMLHttpRequest object from JavaScript. If you look at the source for the insertnewsstory.xsql page, you'll see it's specifying a table name and an XSLT Transform name. The moreover-to-newsstory.xsl stylesheet transforms the incoming XML information into the canonical format that the OracleXMLSave utility knows how to insert.

Try copying and pasting the example <article> element several times within the <moreovernews> element to insert several new articles in one shot.

The newsstory.sql script shows how INSTEAD OF triggers can be used on the database views into which you ask XSQL Pages to insert to the data to customize how incoming data is handled, default primary key values, and so on.

SVG Demo

./demo/svg

The deptlist.xsql page displays a simple list of departments with hyperlinks to the SalChart.xsql page. The SalChart.xsql page queries employees for a given department passed in as a parameter and uses the associated SalChart.xsql stylesheet to format the result into a Scalable Vector Graphics drawing, a bar chart comparing salaries of the employees in that department.
PDF Demo

./demo/fop

emptable.xsql page displays a simple list of employees. The emptable.xsl stylesheet transforms the datapage into the XSL-FO Formatting Objects which, combined with the built-in FOP serializer, render the results in Adobe PDF format.

Setting Up the Demo Data

To set up the demo data do the following:

  1. Change directory to the ./demo directory.

  2. In this directory, run SQLPLUS. Connect to your database as CTXSYS/CTXSYS — the schema owner for Oracle Text (Intermedia Text) packages — and issue the command

    GRANT EXECUTE ON ctx_ddl TO scott;
    
    
  3. Connect to your database as SYSTEM/MANAGER and issue the command:

    GRANT QUERY REWRITE TO scott;
    
    

    This allows SCOTT to create a function-based index that one of the demos uses to perform case-insensitive queries on descriptions of airports.

  4. Connect to your database as SCOTT/TIGER.

  5. Run the script install.sql in the ./demo directory. This script runs all SQL scripts for all the demos.

    install.sql
    @@insclaim/insclaim.sql
    @@document/docdemo.sql
    @@classerr/invalidclasses.sql
    @@airport/airport.sql
    @@insertxml/newsstory.sql
    @@empdept/empdeptobjs.sql
    

  1. Change directory to ./doyouxml subdirectory, and run the following:

    imp scott/tiger file=doyouxml.dmp 
    
    

    to import sample data for the "Do You XML? Site" demo.

  1. To experience the Scalable Vector Graphics (SVG) demonstration, install an SVG plug-in into your browser, such as Adobe SVG Plug-in.

Advanced XSQL Pages Topics

These sections discuss XSQL Pages advanced topics.

Using a Custom XSQL Configuration File Name

By default, the XSQL Pages framework expects its configuration file to be named XSQLConfig.xml. When going between development, test, and production environments, you might want to easily switch between different versions of an XSQL configuration file. To override the name of the configuration file the XSQL page processor will read, do one of the following:

Set the Java system property xsql.config. The simplest way is to specify a Java VM command-line flag like -Dxsql.config=MyConfigFile.xml by defining a servlet initialization parameter xsql.config

This is accomplished by adding an <init-param> element to your web.xml file as part of the <servlet> tag that defines the XSQL Servlet as follows:

     :
  <servlet>
    <servlet-name>XSQL</servlet-name>
    <servlet-class>oracle.xml.xsql.XSQLServlet</servlet-class>
    <init-param>
      <param-name>xsql.config</param-name>
      <param-value>MyConfigFile.xml</param-value>
      <description>
         Please Use MyConfigFile.xml instead of XSQLConfig.xml
      </description>
    </init-param>
  </servlet>
     :

Of course, the servlet initialization parameter is only applicable to the servlet-based use of the XSQL Pages engine. When using the XSQLCommandLine or XSQLRequest programmatic interfaces, use the System parameter instead.


Note:

The config file is always read from the CLASSPATH. For example, if you specify a custom configuration parameter file named MyConfigFile.xml, then the XSQL page processor will attempt to read the XML file as a resource from the CLASSPATH. In a J2EE-style servlet environment, that means you must put your MyConfigFile.xml into the .\WEB-INF\classes directory (or some other top-level directory that will be found on the CLASSPATH). If both the servlet initialization parameter and the System parameter are provided, the servlet initialization parameter value is used.

Understanding Client Stylesheet-Override Options

If the current XSQL page being requested allows it, you can supply an XSLT stylesheet URL in the request to override the default stylesheet that is used, or to apply a stylesheet where none is applied by default. The client-initiated stylesheet URL is provided by supplying the xml-stylesheet parameter as part of the request. The valid values for this parameter are:

  • Any relative URL, interpreted relative to the XSQL page being processed

  • Any absolute URL using the http protocol scheme, provided it references a trusted host (as defined in the XSQL configuration file, by default named XSQLConfig.xml)

  • The literal value none

This last value, xml-stylesheet=none, is particularly useful during development to temporarily "short-circuit" the XSLT stylesheet processing to see what XML datagram your stylesheet is actually seeing. This can help understand why a stylesheet might not be producing the expected results.

Client-override of stylesheets for an XSQL page can be disallowed either by:

  • Setting the allow-client-style configuration parameter to no in the XSQL configuration file, or

  • Explicitly including an allow-client-style="no" attribute on the document element of any XSQL page

If client-override of stylesheets has been globally disabled by default in the XSQL configuration file, any page can still enable client-override explicitly by including an allow-client-style="yes" attribute on the document element of that page.

Controlling How Stylesheets Are Processed

Here are some points to consider:

Controlling the Content Type of the Returned Document

Setting the content type of the information you serve is very important. It allows the requesting client to correctly interpret the information that you send back.If your stylesheet uses an <xsl:output> element, the XSQL Page Processor infers the media type and encoding of the returned document from the media-type and encoding attributes of <xsl:output>.

For example, the following stylesheet uses the media-type="application/vnd.ms-excel" attribute on <xsl:output> to transform the results of an XSQL page containing a standard query over the HR schema's employees table into Microsoft Excel spreadsheet format.

<?xml version="1.0"?>
<!-- empToExcel.xsl -->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="html" media-type="application/vnd.ms-excel"/>
  <xsl:template match="/">
   <html>
     <table>
       <tr><th>Id</th><th>Email</th><th>Salary</th></tr>
       <xsl:for-each select="ROWSET/ROW">
         <tr>
           <td><xsl:value-of select="EMPLOYEE_ID"/></td>
           <td><xsl:value-of select="EMAIL"/></td>
           <td><xsl:value-of select="SALARY"/></td>
         </tr>
       </xsl:for-each>
     </table>
   </html>
  </xsl:template>
</xsl:stylesheet>

An XSQL page that makes use of this stylesheet looks like this:

<?xml version="1.0"?>
<?xml-stylesheet href="empToExcel.xsl" type="text/xsl"?>
<xsql:query connection="hr" xmlns:xsql="urn:oracle-xsql">
  select EMPLOYEE_ID, EMAIL, SALARY from employees order by salary desc
</xsql:query>

Assigning the Stylesheet Dynamically

As we've seen, if you include an <?xml-stylesheet?> processing instruction at the top of your .xsql file, it will be considered by the XSQL page processor for use in transforming the resulting XML datagram. For example:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="emp.xsl"?>
<page connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:query>
    SELECT * FROM employees ORDER BY salary DESC
  </xsql:query>
</page>

uses the emp.xsl stylesheet to transform the results of the employees query in the server tier, before returning the response to the requestor. The stylesheet is accessed by the relative or absolute URL provided in the href pseudo-attribute on the <?xml-stylesheet?> processing instruction.

By including one or more parameter references in the value of the href pseudo-attribute, you can dynamically determine the name of the stylesheet. For example, this page selects the name of the stylesheet to use from a table by assigning the value of a page-private parameter using a query.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="{@sheet}.xsl"?>
<page connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:set-page-param bind-params="UserCookie" name="sheet">
    SELECT stylesheet_name
      FROM user_prefs
     WHERE username = ?
  </xsql:set-page-param>
  <xsql:query>
    SELECT * FROM employees ORDER BY salary DESC
  </xsql:query>
</page>

Processing Stylesheets in the Client

Some browsers like Microsoft's Internet Explorer 5.0 and higher support processing XSLT stylesheets in the client. These browsers recognize the stylesheet to be processed for an XML document in the same way that a server-side XSQL page does, using an <?xml-stylesheet?> processing instruction. This is not a coincidence. The use of <?xml-stylesheet?> for this purpose is part of the W3C Recommendation from June 29, 1999 entitled "Associating Stylesheets with XML Documents, Version 1.0"

By default, the XSQL page processor performs XSLT transformations in the server, however by adding on additional pseudo-attribute to your <?xml-stylesheet?> processing instruction in your XSQL page — client="yes" — the page processor will defer the XSLT processing to the client by serving the XML datagram "raw", with the current <?xml-stylesheet?> at the top of the document.

One important point to note is that Internet Explorer 5.0 shipped in late 1998, containing an implementation of the XSL stylesheet language that conformed to a December 1998 Working Draft of the standard. The XSLT 1.0 Recommendation that finally emerged in November of 1999 had significant changes from the earlier working draft version on which IE5 is based. This means that IE5 browsers understand a different "dialect" of XSLT than all other XSLT processors — like the Oracle XSLT processor — which implement the XSLT 1.0 Recommendation syntax.

Toward the end of 2000, Microsoft released version 3.0 of their MSXML components as a Web-downloadable release. This latest version does implement the XSLT 1.0 standard, however in order for it to be used as the XSLT processor inside the IE5 browser, the user must go through additional installation steps. There is no way for a server to detect that the IE5 browser has installed the latest XSLT components, so until the Internet Explorer 6.0 release emerges, which will contain the latest components by default and which will send a detectable and different User-Agent string containing the 6.0 version number, stylesheets delivered for client processing to IE5 browsers have to use the earlier IE5-"flavor" of XSL.

What we need is a way to request that an XSQL page use different stylesheets depending on the User-Agent making the request. Luckily, the XSQL Pages framework makes this easy and we learn how in the next section.

Providing Multiple, UserAgent-Specific Stylesheets

You can include multiple <?xml-stylesheet?> processing instructions at the top of an XSQL page and any of them can contain an optional media pseudo-attribute. If specified, the media pseudo-attribute's value is compared case-insensitively with the value of the HTTP header's User-Agent string. If the value of the media pseudo-attribute matches a part of the User-Agent string, then the processor selects the current <?xml-stylesheet?> processing instruction for use, otherwise it ignores it and continues looking. The first matching processing instruction in document order will be used. A processing instruction without a media pseudo-attribute matches all user agents so it can be used as the fallback/default.

For example, the following processing instructions at the top of an .xsql file...

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" media="lynx" href="doyouxml-lynx.xsl" ?>
<?xml-stylesheet type="text/xsl" media="msie 5" href="doyouxml-ie.xsl" ?>
<?xml-stylesheet type="text/xsl" href="doyouxml.xsl" ?>
<page xmlns:xsql="urn:oracle-xsql" connection="demo">
 ...

will use doyouxml-lynx.xsl for Lynx browsers, doyouxml-ie.xsl for Internet Explorer 5.0 or 5.5 browsers, and doyouxml.xsl for all others.

Table 8-15 summarizes all of the supported pseudo-attributes allowed on the <?xml-stylesheet?> processing instruction.

Table 8-15 Pseudo-Attributes for <?xml-stylesheet?>

Attribute Name Description
type = "string"
Indicates the MIME type of the associated stylesheet. For XSLT stylesheets, this attribute must be set to the string text/xsl.

This attribute may be present or absent when using the serializer attribute, depending on whether an XSLT stylesheet has to execute before invoking the serializer, or not.

href = "URL"
Indicates the relative or absolute URL to the XSLT stylesheet to be used. If an absolute URL is supplied that uses the http protocol scheme, the IP address of the resource must be a trusted host listed in the XSQL configuration file (by default, named XSQLConfig.xml).
media = "string"
This attribute is optional. If provided, its value is used to perform a case-insensitive match on the User-Agent string from the HTTP header sent by the requesting device. The current <?xml-stylesheet?> processing instruction will only be used if the User-Agent string contains the value of the media attribute, otherwise it is ignored.
client = "boolean"
If set to yes, caused the XSQL page processor to defer the processing of the associated XSLT stylesheet to the client. The "raw" XML datagram will be sent to the client with the current <?xml-stylesheet?> processing instruction at the top of the document. The default if not specified is to perform the transform in the server.
serializer = "string"
By default, the XSQL page processor uses the:
  • XML DOM serializer if no XSLT stylesheet is used

  • XSLT processor's serializer, if XSLT stylesheet is used

Specifying this pseudo-attribute indicates that a custom serializer implementation must be used instead.

Valid values are either the name of a custom serializer defined in the <serializerdefs> section of the XSQL configuration file (by default, named XSQLConfig.xml), or the string java:fully.qualified.Classname. If both an XSLT stylesheet and the serializer attribute are present, then the XSLT transform is performed first, then the custom serializer is invoked to render the final result to the OutputStream or PrintWriter.


Using XSQL Configuration File to Tune Your Environment

You can use the XSQL configuration file (by default, named XSQLConfig.xml) to tune your XSQL pages environment. Table 8-16 defines all of the parameters that can be set.

Table 8-16 XSQL Configuration File Settings

Configuration Setting Name Description
XSQLConfig/servlet/output-buffer-size
Sets the size (in bytes) of the buffered output stream. If your servlet engine already buffers I/O to the Servlet Output Stream, then you can set to 0 to avoid additional buffering.

Default value is 0. Valid value is any non-negative integer.

XSQLConfig/servlet/suppress-mime-charset/media-type
The XSQL Servlet sets the HTTP ContentType header to indicate the MIME type of the resource being returned to the request. By default, the XSQL Servlet includes the optional character set information in the MIME type. For a particular MIME type, you can suppress the inclusion of the character set information by including a <media-type> element, with the desired MIME type as its contents.

You may list any number of <media-type> elements.

Valid value is any string.

XSQLConfig/processor/character-set-conversion/default-charset
By default, the XSQL page processor does character set conversion on the value of HTTP parameters to compensate for the default character set used by most servlet engines. The default base character set used for conversion is the Java character set 8859_1 corresponding to IANA's ISO-8859-1 character set. If your servlet engine uses a different character set as its base character set you can now specify that value here.

To suppress character set conversion, specify the empty element <none/> as the content of the <default-charset> element, instead of a character set name. This is useful if you are working with parameter values that are correctly representable using your servlet default character set, and eliminates a small amount of overhead associated with performing the character set conversion.

Valid values are any Java character set name, or the element <none/>.

XSQLConfig/processor/reload-connections-on-error
Connection definitions are cached when the XSQL Page Processor is initialized. Set this setting to yes to cause the processor to reread the XSQLConfig.xml file to reload connection definitions if an attempt is made to request a connection name that's not in the cached connection list. The yes setting is useful during development when you might be adding new <connection> definitions to the file while the servlet is running. Set to no to avoid reloading the connection definition file when a connection name is not found in the in-memory cache.

Default is yes. Valid values are yes and no.

XSQLConfig/processor/default-fetch-size
Sets the default value of the row fetch size for retrieving information from SQL queries from the database. Only takes effect if you are using the Oracle JDBC Driver, otherwise the setting is ignored. Useful for reducing network round-trips to the database from the servlet engine running in a different tier.

Default is 50. Valid value is any nonzero positive integer.

XSQLConfig/processor/page-cache-size
Sets the size of the XSQL cache for XSQL page templates. This determines the maximum number of XSQL pages that will be cached. Least recently used pages get "bumped" out of the cache if you go beyond this number.

Default is 25. Valid value is any nonzero positive integer.

XSQLConfig/processor/stylesheet-cache-size
Sets the size of the XSQL cache for XSLT stylesheets. This determines the maximum number of stylesheets that will be cached. Least recently used stylesheets get "bumped" out of the cache if you go beyond this number.

Default is 25. Valid value is any nonzero positive integer.

XSQLConfig/processor/stylesheet-pool/initial
Each cached stylesheet is actually a pool of cached stylesheet instances to improve throughput. Sets the initial number of stylesheets to be allocated in each stylesheet pool.

Default is 1. Valid value is any nonzero positive integer.

XSQLConfig/processor/stylesheet-pool/increment
Sets the number of stylesheets to be allocated when the stylesheet pool must grow due to increased load on the server.

Default is 1. Valid value is any nonzero positive integer.

XSQLConfig/processor/stylesheet-pool/timeout-seconds
Sets the number of seconds of inactivity that must transpire before a stylesheet instance in the pool will be removed to free resources as the pool tries to "shrink" back to its initial size.

Default is 60. Valid value is any nonzero positive integer.

XSQLConfig/processor/connection-pool/initial
The XSQL page processor's default connection manager implements connection pooling to improve throughput. This setting controls the initial number of JDBC connections to be allocated in each connection pool.

Default is 2. Valid value is any nonzero positive integer.

XSQLConfig/processor/connection-pool/increment
Sets the number of connections to be allocated when the connection pool must grow due to increased load on the server.

Default is 1. Valid value is any nonzero positive integer.

XSQLConfig/processor/connection-pool/timeout-seconds
Sets the number of seconds of inactivity that must transpire before a JDBC connection in the pool will be removed to free resources as the pool tries to "shrink" back to its initial size.

Default is 60. Valid value is any nonzero positive integer.

XSQLConfig/processor/connection-pool/dump-allowed
Determines whether a diagnostic report of connection pool activity can be requested by passing the dump-pool=y parameter in the page request.

Default is no. Valid value is yes or no.

XSQLConfig/processor/connection-manager/factory
Specifies the fully-qualified Java class name of the XSQL connection manager factory implementation. If not specified, this setting defaults to oracle.xml.xsql.XSQLConnectionManagerFactoryImpl.

Default is oracle.xml.xsql.XSQLConnectionManagerFactoryImpl. Valid value is any class name that implements the oracle.xml.xsql.XSQLConnectionManagerFactory interface.

XSQLConfig/processor/owa/fetch-style
Sets the default OWA Page Buffer fetch style used by the <xsql:include-owa> action.Valid values are CLOB or TABLE, and the default if not specified is CLOB.

If set to CLOB, the processor uses temporary CLOB to retrieve the OWA page buffer.

If set to TABLE the processor uses a more efficient approach that requires the existence of the Oracle user-defined type named XSQL_OWA_ARRAY which must be created by hand using the DDL statement:

CREATE TYPE xsql_owa_array AS TABLE OF VARCHAR2(32767)

XSQLConfig/processor/timing/page
Determines whether a the XSQL page processor adds an xsql-timing attribute to the document element of the page whose value reports the elapsed number of milliseconds required to process the page.

Default is no. Valid value is yes or no.

XSQLConfig/processor/timing/action
Determines whether a the XSQL page processor adds comment to the page just before the action element whose contents reports the elapsed number of milliseconds required to process the action.

Default is no. Valid value is yes or no.

XSQLConfig/processor/logger/factory
Specifies the fully-qualified Java class name of a custom XSQL logger factory implementation. If not specified, then no logger is used.

Valid value is any class name that implements the oracle.xml.xsql.XSQLLoggerFactory interface.

XSQLConfig/processor/error-handler/class
Specifies the fully-qualified Java class name of a custom XSQL error handler to be the default error handler implementation. If not specified, then the default error handler is used.

Valid value is any class name that implements the oracle.xml.xsql.XSQLErrorHandler interface.

XSQLConfig/processor/xml-parsing/preserve-whitespace
Determines whether the XSQL page processor preserves whitespace when parsing XSQL page templates and XSLT stylesheets.

The default value is true. Valid values are true or false. Changing the default to false can slightly speed up the processing of XSQL pages and stylesheets since ignoring whitespace while parsing is faster than preserving it.

XSQLConfig/processor/security/stylesheet/defaults/allow-client-style
While developing an application, it is frequently useful to take advantage of the XSQL page processor's for each request stylesheet override capability by providing a value for the special xml-stylesheet parameter in the request. One of the most common uses is to provide the xml-stylesheet=none combination to temporarily disable the application of the stylesheet to "peek" underneath at the raw XSQL data page for debugging purposes.

When development is completed, you can explicitly add the allow-client-style="no" attribute to the document element of each XSQL page to prohibit client overriding of the stylesheet in the production application. However, using this configuration setting, you can globally change the default behavior for allow-client-style in a single place.

Note that this only provides the default setting for this behavior. If the allow-client-style="yes|no" attribute is explicitly specified on the document element for a given XSQL page, its value takes precedence over this global default.

Valid values are yes and no.

XSQLConfig/processor/security/stylesheet/trusted-hosts/host
XSLT stylesheets can invoke extension functions. In particular, the Oracle XSLT processor — which the XSQL page processor uses to process all XSLT stylesheets — supports Java extension functions. Typically your XSQL pages will refer to XSLT stylesheets using relative URL's The XSQL page processor enforces that any absolute URL to an XSLT stylesheet that is processed must be from a trusted host whose name is listed here in the configuration file.

You may list any number of <host> elements inside the <trusted-hosts> element. The name of the local machine, localhost, and 127.0.0.1 are considered trusted hosts by default.

Valid values are any hostname or IP address.

XSQLConfig/http/proxyhost
Sets the name of the HTTP proxy server to use when processing URLs with the http protocol scheme.

Valid value is any hostname or IP address.

XSQLConfig/http/proxyport
Sets the port number of the HTTP proxy server to use when processing URLs with the http protocol scheme.

Valid value is any nonzero integer.

XSQLConfig/connectiondefs/connection
Defines a "nickname" and the JDBC connection details for a named connection for use by the XSQL page processor.

You may supply any number of <connection> element children of <connectiondefs>. Each connection definition must supply a name attribute, and may supply appropriate children elements <username>, <password>, <driver>, <dburl>, and <autocommit>.

XSQLConfig/connectiondefs/connection/username
Defines the username for the current connection.
XSQLConfig/connectiondefs/connection/password
Defines the password for the current connection.
XSQLConfig/connectiondefs/connection/dburl
Defines the JDBC connection URL for the current connection.
XSQLConfig/connectiondefs/connection/driver
Specifies the fully-qualified Java class name of the JDBC driver to be used for the current connection. If not specified, defaults to oracle.jdbc.driver.OracleDriver.
XSQLConfig/connectiondefs/connection/autocommit
Explicitly sets the Auto Commit flag for the current connection. If not specified, connection uses JDBC driver's default setting for Auto Commit.
XSQLConfig/serializerdefs/serializer
Defines a named custom serializer implementation.

You may supply any number of <serializer> element children of <serializerdefs>. Each must specify both a <name> and a <class> child element.

XSQLConfig/serializerdefs/serializer/name
Defines the name of the current custom serializer definition.
XSQLConfig/connectiondefs/connection/class
Specifies the fully-qualified Java class name of the current custom serializer. The class must implement the oracle.xml.xsql.XSQLDocumentSerializer interface.

Using the FOP Serializer to Produce PDF Output

Using the XSQL Pages framework's support for custom serializers, the oracle.xml.xsql.serializers.XSQLFOPSerializer is provided for integrating with the Apache FOP processor (http://xml.apache.org/fop). The FOP processor renders a PDF document from an XML document containing XSL Formatting Objects (http://www.w3.org/TR/xsl).

For example, given the following XSLT stylesheet, EmpTableFO.xsl:

<?xml version="1.0"?>
<fo:root xmlns:fo="http://www.w3.org/1999/XSL/Format" xsl:version="1.0"
         xmlns:xsl="http://www.w3.org/1999/XSL/Transform">


  <!-- defines the layout master -->
  <fo:layout-master-set>
    <fo:simple-page-master master-name="first" 
                           page-height="29.7cm" 
                           page-width="21cm" 
                           margin-top="1cm" 
                           margin-bottom="2cm" 
                           margin-left="2.5cm" 
                           margin-right="2.5cm">
      <fo:region-body margin-top="3cm"/>
    </fo:simple-page-master>
  </fo:layout-master-set>

  <!-- starts actual layout -->
  <fo:page-sequence master-reference="first">

  <fo:flow flow-name="xsl-region-body">

      <fo:block font-size="24pt" line-height="24pt" font-weight="bold" start-indent="15pt">
        Total of All Salaries is $<xsl:value-of select="sum(/ROWSET/ROW/SAL)"/>
      </fo:block>

      <!-- Here starts the table -->
      <fo:block border-width="2pt">
        <fo:table>
          <fo:table-column column-width="4cm"/>
          <fo:table-column column-width="4cm"/>
          <fo:table-body font-size="10pt" font-family="sans-serif">
            <xsl:for-each select="ROWSET/ROW">
              <fo:table-row line-height="12pt">
                <fo:table-cell>
                  <fo:block><xsl:value-of select="ENAME"/></fo:block>
                </fo:table-cell>
                <fo:table-cell>
                  <fo:block><xsl:value-of select="SAL"/></fo:block>
                </fo:table-cell>
              </fo:table-row>
            </xsl:for-each>
          </fo:table-body>
        </fo:table>
      </fo:block>
    </fo:flow>
  </fo:page-sequence>
</fo:root>


Note:

To use the XSQL FOP Serializer, you need to add these additional Java archives to your server's CLASSPATH:
  • xsqlserializers.jar - supplied with Oracle XSQL

  • fop.jar - from Apache, version 0.20.3 or higher

  • batik.jar - from the FOP distribution

  • avalon-framework-4.0.jar - from FOP distribution

  • logkit-1.0.jar - from FOP distribution


For reference, in case you might want to customize the implementation, the source code for the FOP Serializer provided in this release looks like this:

package oracle.xml.xsql.serializers;
import org.w3c.dom.Document;
import org.apache.log.Logger;
import org.apache.log.Hierarchy;
import org.apache.fop.messaging.MessageHandler;
import org.apache.log.LogTarget;
import oracle.xml.xsql.XSQLPageRequest;
import oracle.xml.xsql.XSQLDocumentSerializer;
import org.apache.fop.apps.Driver;
import org.apache.log.output.NullOutputLogTarget;
/**
 * Tested with the FOP 0.20.3RC release from 19-Jan-2002
 */
public class XSQLFOPSerializer implements XSQLDocumentSerializer {
  private static final String PDFMIME = "application/pdf";
  public void serialize(Document doc, XSQLPageRequest env) throws Throwable {
    try { 
      // First make sure we can load the driver
      Driver FOPDriver = new Driver();
      // Tell FOP not to spit out any messages by default.
      // You can modify this code to create your own FOP Serializer
      // that logs the output to one of many different logger targets
      // using the Apache LogKit API
      Logger logger=Hierarchy.getDefaultHierarchy().getLoggerFor("XSQLServlet");
      logger.setLogTargets(new LogTarget[]{new NullOutputLogTarget()});
      FOPDriver.setLogger(logger);
      // Some of FOP's messages appear to still use MessageHandler.
      MessageHandler.setOutputMethod(MessageHandler.NONE);
      // Then set the content type before getting the reader
      env.setContentType(PDFMIME);
      FOPDriver.setOutputStream(env.getOutputStream());
      FOPDriver.setRenderer(FOPDriver.RENDER_PDF); FOPDriver.render(doc);
    }
    catch (Exception e) {
      // Cannot write PDF output for the error anyway.
      // So maybe this stack trace will be useful info
      e.printStackTrace(System.err);
    }
  }
}

Using XSQL Page Processor Programmatically

The XSQLRequest class, enables you to utilize the XSQL page processor "engine" from within your own custom Java programs. Using the API is simple. You construct an instance of XSQLRequest, passing the XSQL page to be processed into the constructor as one of the following:

  • String containing a URL to the page

  • URL object for the page

  • In-memory XMLDocument

Then you invoke one of the following methods to process the page:

  • process() to write the result to a PrintWriter or OutputStream, or

  • processToXML() to return the result as an XML Document

If you want to use the built-in XSQL Connection Manager — which implements JDBC connection pooling based on XSQL configuration file definitions — then the XSQL page is all you need to pass to the constructor. Optionally, you can pass in a custom implementation for the XSQLConnectionManagerFactory interface as well, if you want to use your own connection manager implementation.

Note that the ability to pass the XSQL page to be processed as an in-memory XML Document object means that you can dynamically generate any valid XSQL page for processing using any means necessary, then pass the page to the XSQL engine for evaluation.

When processing a page, there are two additional things you may want to do as part of the request:

  • Pass a set of parameters to the request

    You accomplish this by passing any object that implements the Dictionary interface, to the process() or processToXML() methods. Passing a HashTable containing the parameters is one popular approach.

  • Set an XML document to be processed by the page as if it were the "posted XML" message body

    You can do this using the setPostedDocument() method on the XSQLRequest object.

Here is a simple example of processing a page using XSQLRequest:

import oracle.xml.xsql.XSQLRequest;
import java.util.Hashtable;
import java.io.PrintWriter;
import java.net.URL;
public class XSQLRequestSample {
  public static void main( String[] args) throws Exception {
     // Construct the URL of the XSQL Page
   URL pageUrl = new URL("file:///C:/foo/bar.xsql");
   // Construct a new XSQL Page request
   XSQLRequest req = new XSQLRequest(pageUrl);
   // Setup a Hashtable of named parameters to pass to the request
   Hashtable params = new Hashtable(3);
   params.put("param1","value1");
   params.put("param2","value2");
   /* If needed, treat an existing, in-memory XMLDocument as if
   ** it were posted to the XSQL Page as part of the request
   req.setPostedDocument(myXMLDocument);
   **
   */
   // Process the page, passing the parameters and writing the output
   // to standard out.
   req.process(params,new PrintWriter(System.out)
                        ,new PrintWriter(System.err));
  }
}

Writing Custom XSQL Action Handlers

When the task at hand requires custom processing, and none of the built-in actions does exactly what you need, you can augment your repertoire by writing your own actions that any of your XSQL pages can use.

The XSQL page processor at its very core is an engine that processes XML documents containing "action elements". The page processor engine is written to support any action that implements the XSQLActionHandler interface. All of the built-in actions implement this interface.

The XSQL Page Processor processes the actions in a page in the following way. For each action in the page, the engine:

  1. Constructs an instance of the action handler class using the default constructor

  2. Initializes the handler instance with the action element object and the page processor context by invoking the method init(Element actionElt,XSQLPageRequest context)

  3. Invokes the method that allows the handler to handle the action handleAction (Node result)

For built-in actions, the engine knows the mapping of XSQL action element name to the Java class that implements the action's handler. Table 8-17, "Built-In XSQL Elements and Action Handler Classes" lists that mapping explicitly for your reference. For user-defined actions, you use the built-in:

<xsql:action handler="fully.qualified.Classname" ... />

action whose handler attribute provides the fully-qualified name of the Java class that implements the custom action handler.

Table 8-17 Built-In XSQL Elements and Action Handler Classes

XSQL Action Element Handler Class in oracle.xml.xsql.actions Description
<xsql:query>
XSQLQueryHandler Execute an arbitrary SQL statement and include its result in canonical XML format.
<xsql:dml>
XSQLDMLHandler Execute a SQL DML statement or a PL/SQL anonymous block.
<xsql:set-stylesheet-param>
XSQLStylesheetParameterHandler Set the value of a top-level XSLT stylesheet parameter.
<xsql:insert-request>
XSQLInsertRequestHandler Insert the XML document (or HTML form) posted in the request into a database table or view.
<xsql:include-xml>
XSQLIncludeXMLHandler Include arbitrary XML resources at any point in your page by relative or absolute URL.
<xsql:include-request-params>
XSQLIncludeRequestHandler Include all request parameters as XML elements in your XSQL page.
<xsql:include-posted-xml>
XSQLIncludePostedXMLHandler  
<xsql:include-xsql>
XSQLIncludeXSQLHandler Include the results of one XSQL page at any point inside another.
<xsql:include-owa>
XSQLIncludeOWAHandler Include the results of executing a stored procedure that makes use of the Oracle Web Agent (OWA) packages inside the database to generate XML.
<xsql:action>
XSQLExtensionActionHandler Invoke a user-defined action handler, implemented in Java, for executing custom logic and including custom XML information into your XSQL page.
<xsql:ref-cursor-function>
XSQLRefCursorFunctionHandler Includes the canonical XML representation of the result set of a cursor returned by a PL/SQL stored function.
<xsql:include-param>
XSQLGetParameterHandler Include a parameter and its value as an element in your XSQL page.
<xsql:if-param>
XSQLIfParamHandler Conditionally include XML content and other XSQL actions (or other XSQL actions).
<xsql:set-session-param>
XSQLSetSessionParamHandler Set an HTTP-Session level parameter.
<xsql:set-page-param>
XSQLSetPageParamHandler Set an HTTP-Session level parameter. Set a page-level (local) parameter that can be referred to in subsequent SQL statements in the page.
<xsql:set-cookie>
XSQLSetCookieHandler Set an HTTP Cookie.
<xsql:insert-param>
XSQLInsertParameterHandler Inserts the XML document contained in the value of a single parameter.
<xsql:update-request>
XSQLUpdateRequestHandler Update an existing row in the database based on the posted XML document supplied in the request.
<xsql:delete-request>
XSQLDeleteRequestHandler Delete an existing row in the database based on the posted XML document supplied in the request.
<xsql:if-param>
 
Includes nested actions and literal XML content (or literal XML content) if some condition based on a parameter value is true.

All the demos are listed at http://localhost/xsql/index.html.

Writing your Own Action Handler

To create a custom Action Handler, you need to provide a class that implements the oracle.xml.xsql.XSQLActionHandler interface. Most custom action handlers extend oracle.xml.xsql.XSQLActionHandlerImpl that provides a default implementation of the init() method and offers a set of useful helper methods that will prove very useful.

When an action handler's handleAction method is invoked by the XSQL page processor, the action implementation gets passed the root node of a DOM Document Fragment to which the action handler appends any dynamically created XML content that is returned to the page.

The XSQL Page Processor conceptually replaces the action element in the XSQL page template with the content of this Document Fragment. It is completely legal for an Action Handler to append nothing to this document fragment, if it has no XML content to add to the page.

While writing you custom action handlers, several methods on the XSQLActionHandlerImpl class are worth noting because they make your life a lot easier. Table 8-18 lists the methods that will likely come in handy for you.

Table 8-18 Helpful Methods on oracle.xml.xsql.SQLActionHandlerImpl

Method Name Description
getActionElement
Returns the current action element being handled
getActionElementContent
Returns the text content of the current action element, with all lexical parameters substituted appropriately.
getPageRequest
Returns the current XSQL page processor context. Using this object you can then do things like:
  • setPageParam()

    Set a page parameter value

  • getPostedDocument()/setPostedDocument()

    Get or set the posted XML document

  • translateURL()

    Translate a relative URL to an absolute URL

  • getRequestObject()/setRequestObject()

    Get or set objects in the page request context that can be shared across actions in a single page.

  • getJDBCConnection()

    Gets the JDBC connection in use by this page (possible null if no connection in use).

  • getRequestType()

    Detect whether you are running in the "Servlet", "Command Line" or "Programmatic" context. For example, if the request type is "Servlet" then you can cast the XSQLPageRequest object to the more specific XSQLServletPageRequest to access addition Servlet-specific methods like getHttpServletRequest, getHttpServletResponse, and getServletContext

getAttributeAllowingParam
Retrieve the attribute value from an element, resolving any XSQL lexical parameter references that might appear in the attribute's value. Typically this method is applied to the action element itself, but it is also useful for accessing attributes of any of its sub-elements. To access an attribute value without allowing lexical parameters, use the standard getAttribute() method on the DOM Element interface.
appendSecondaryDocument
Append the entire contents of an external XML document to the root of the action handler result content.
addResultElement
Simplify appending a single element with text content to the root of the action handler result content.
firstColumnOfFirstRow
Return the first column value of the first row of a SQL statement passed in. Requires the current page to have a connection attribute on its document element, or an error is returned.
bindVariableCount
Returns the number of tokens in the space-delimited list of bind-params, indicating how many bind variables are expected to be bound to parameters.
handleBindVariables
Manage the binding of JDBC bind variables that appear in a prepared statement with the parameter values specified in the bind-params attribute on the current action element. If the statement already is using a number of bind variables prior to call this method, you can pass the number of existing bind variable "slots" in use as well.
reportErrorIncludingStatement
Report an error, including the offending (SQL) statement that caused the problem, optionally including a numeric error code.
reportFatalError
Report a fatal error.
reportMissingAttribute
Report an error that a required action handler attribute is missing using the standard <xsql-error> element.
reportStatus
Report action handler status using the standard <xsql-status> element.
requiredConnectionProvided
Checks whether a connection is available for this request, and outputs an "errorgram" into the page if no connection is available.
variableValue
Returns the value of a lexical parameter, taking into account all scoping rules which might determine its default value.

The following example shows a custom action handler MyIncludeXSQLHandler that leverages one of the built-in action handlers and then uses arbitrary Java code to modify the resulting XML fragment returned by that handler before appending its result to the XSQL page:

import oracle.xml.xsql.*;
import oracle.xml.xsql.actions.XSQLIncludeXSQLHandler;
import org.w3c.dom.*;
import java.sql.SQLException;
public class MyIncludeXSQLHandler extends XSQLActionHandlerImpl {
  XSQLActionHandler nestedHandler = null;
  public void init(XSQLPageRequest req, Element action) {
    super.init(req, action);
    // Create an instance of an XSQLIncludeXSQLHandler
    // and init() the handler by passing the current request/action
    // This assumes the XSQLIncludeXSQLHandler will pick up its
    // href="xxx.xsql" attribute from the current action element.
    nestedHandler = new XSQLIncludeXSQLHandler();
    nestedHandler.init(req,action);
  }
public void handleAction(Node result) throws SQLException {
   DocumentFragment df=result.getOwnerDocument().createDocumentFragment();
   nestedHandler.handleAction(df);
   // Custom Java code here can work on the returned document fragment
   // before appending the final, modified document to the result node.
   // For example, add an attribute to the first child
   Element e = (Element)df.getFirstChild();
   if (e != null) {
         e.setAttribute("ExtraAttribute","SomeValue");
   }
   result.appendChild(df);
    }
}

If you create custom action handlers that need to work differently based on whether the page is being requested through the XSQL Servlet, the XSQL Command-line Utility, or programmatically through the XSQLRequest class, then in your Action Handler implementation you can call getPageRequest() to get a reference to the XSQLPageRequest interface for the current page request. By calling getRequestType() on the XSQLPageRequest object, you can see if the request is coming from the "Servlet", "Command Line", or "Programmatic" routes respectively. If the return value is "Servlet", then you can get access to the HTTP Servlet request, response, and servlet context objects by doing:

XSQLServletPageRequest xspr = (XSQLServletPageRequest)getPageRequest();
if (xspr.getRequestType().equals("Servlet")) {
  HttpServletRequest     req  = xspr.getHttpServletRequest();
  HttpServletResponse   resp  = xspr.getHttpServletResponse();
  ServletContext        cont  = xspr.getServletContext();
  // do something fun here with req, resp, or cont however
  // writing to the response directly from a handler will
  // produce unexpected results. Allow the XSQL Servlet
  // or your custom Serializer to write to the servlet
  // response output stream at the write moment later when all
  // action elements have been processed.
}

Using Multi-Valued Parameters in Custom XSQL Actions

The base class for custom XSQL actions, XSQLActionHandlerImpl supports working with array-named lexical parameter substitution and array-named bind variables as well as simple-valued parameters. If your custom actions are use methods like getAttributeAllowingParam(), getActionElementContent(), or handleBindVariables() methods from this base class, you pickup the multi-valued parameter functionality for free in your custom actions.

Use the getParameterValues() method on the XSQLPageRequest interface to explicitly get a parameter value as a String[]. The helper method variableValues() in XSQLActionHandlerImpl makes it easy to use this functionality from within a custom action handler if you need to do so programmatically.

Writing Custom XSQL Serializers

You can provide a user-defined serializer class to programmatically control how the final XSQL datapage's XML document is serialized to a text or binary stream. A user-defined serializer must implement the oracle.xml.xsql.XSQLDocumentSerializer interface which comprises the single method:

void serialize(org.w3c.dom.Document doc, XSQLPageRequest env) throws Throwable;

In this release, DOM-based serializers are supported. A future release may support SAX2-based serializers as well. A custom serializer class is expected to perform the following tasks in the correct order:

  1. Set the content type of the serialized stream before writing any content to the output PrintWriter (or OutputStream).

    You set the type by calling setContentType() on the XSQLPageRequest that is passed to your serializer. When setting the content type, you can either set just a MIME type like this:

    env.setContentType("text/html");
    
    

    or a MIME type with an explicit output encoding character set like this:

    env.setContentType("text/html;charset=Shift_JIS");
    
    
  2. Call getWriter() or getOutputStream() — but not both! — on the XSQLPageRequest to get the appropriate PrintWriter or OutputStream respectively to use for serializing the content.

For example, the following custom serializer illustrates a simple implementation which simply serializes an HTML document containing the name of the document element of the current XSQL data page:

package oracle.xml.xsql.serializers;
import org.w3c.dom.Document;
import java.io.PrintWriter;
import oracle.xml.xsql.*;

public class XSQLSampleSerializer implements XSQLDocumentSerializer {
  public void serialize(Document doc, XSQLPageRequest env) throws Throwable {
    String encoding = env.getPageEncoding();  // Use same encoding as XSQL page
                                              // template. Set to specific
                                              // encoding if necessary
    String mimeType = "text/html"; // Set this to the appropriate content type
    // (1) Set content type using the setContentType on the XSQLPageRequest
    if (encoding != null && !encoding.equals("")) {
      env.setContentType(mimeType+";charset="+encoding);
    }
    else {
      env.setContentType(mimeType);
    }
    // (2) Get the output writer from the XSQLPageRequest
    PrintWriter e = env.getWriter();
    // (3) Serialize the document to the writer
    e.println("<html>Document element is <b>"+
              doc.getDocumentElement().getNodeName()+
              "</b></html>");
  }
}

There are two ways to use a custom serializer, depending on whether you need to first perform an XSLT transformation before serializing or not. To perform an XSLT transformation before using a custom serializer, simply add the serializer="java:fully.qualified.ClassName" in the <?xml-stylesheet?> processing instruction at the top of your page like this:

<?xml version="1.0?>
<?xml-stylesheet type="text/xsl" href="mystyle.xsl"
                 serializer="java:my.pkg.MySerializer"?>

If you only need the custom serializer, simply leave out the type and href attributes like this:

<?xml version="1.0?>
<?xml-stylesheet serializer="java:my.pkg.MySerializer"?>

You can also assign a short name to your custom serializers in the <serializerdefs> section of the XSQL configuration file (by default, named XSQLConfig.xml) and then use the nickname (case-sensitive) in the serializer attribute instead to save typing. For example, if you have the following in the XSQL configuration file:

<XSQLConfig>
  <!--and so on. -->
  <serializerdefs>
    <serializer>
      <name>Sample</name>
      <class>oracle.xml.xsql.serializers.XSQLSampleSerializer</class>
    </serializer>
    <serializer>
      <name>FOP</name>
      <class>oracle.xml.xsql.serializers.XSQLFOPSerializer</class>
    </serializer>
  </serializerdefs>
</XSQLConfig>

then you can use the nicknames "Sample" and "FOP" (or "FOP") as shown in the following examples:

<?xml-stylesheet type="text/xsl" href="emp-to-xslfo.xsl" serializer="FOP"?>

or

<?xml-stylesheet serializer="Sample"?>

The XSQLPageRequest interface supports both a getWriter() and a getOutputStream() method. Custom serializers can call getOutputStream() to return an OutputStream instance into which binary data (like a dynamically produced GIF image, for example) can be serialized. Using the XSQL Servlet, writing to this output stream results in writing the binary information to the servlet output stream.

For example, the following serializer illustrates an example of writing out a dynamic GIF image. In this example the GIF image is a static little "ok" icon, but it shows the basic technique that a more sophisticated image serializer needs to use:

package oracle.xml.xsql.serializers;
import org.w3c.dom.Document;
import java.io.*;
import oracle.xml.xsql.*;

public class XSQLSampleImageSerializer implements XSQLDocumentSerializer {
   // Byte array representing a small "ok" GIF image
   private static byte[] okGif =
     {(byte)0x47,(byte)0x49,(byte)0x46,(byte)0x38,
      (byte)0x39,(byte)0x61,(byte)0xB,(byte)0x0,
      (byte)0x9,(byte)0x0,(byte)0xFFFFFF80,(byte)0x0,
      (byte)0x0,(byte)0x0,(byte)0x0,(byte)0x0,
      (byte)0xFFFFFFFF,(byte)0xFFFFFFFF,(byte)0xFFFFFFFF,(byte)0x2C,
      (byte)0x0,(byte)0x0,(byte)0x0,(byte)0x0,
      (byte)0xB,(byte)0x0,(byte)0x9,(byte)0x0,
      (byte)0x0,(byte)0x2,(byte)0x14,(byte)0xFFFFFF8C,
      (byte)0xF,(byte)0xFFFFFFA7,(byte)0xFFFFFFB8,(byte)0xFFFFFF9B,
      (byte)0xA,(byte)0xFFFFFFA2,(byte)0x79,(byte)0xFFFFFFE9,
      (byte)0xFFFFFF85,(byte)0x7A,(byte)0x27,(byte)0xFFFFFF93,
      (byte)0x5A,(byte)0xFFFFFFE3,(byte)0xFFFFFFEC,(byte)0x75,
      (byte)0x11,(byte)0xFFFFFF85,(byte)0x14,(byte)0x0,
      (byte)0x3B};

  public void serialize(Document doc, XSQLPageRequest env) throws Throwable {
    env.setContentType("image/gif");
    OutputStream os = env.getOutputStream();
    os.write(okGif,0,okGif.length);
    os.flush();
  }
}

Using the XSQL Command-line utility, the binary information is written to the target output file. Using the XSQLRequest programmatic API, two constructors exist that allow the caller to supply the target OutputStream to use for the results of page processing.

Note that your serializer must either call getWriter() (for textual output) or getOutputStream() (for binary output) but not both. Calling both in the same request will raise an error.

Using a Custom XSQL Connection Manager for JDBC Datasources

As an alternative to defining your named connections in the XSQL configuration file, you may use one of the two provided XSQLConnectionManager implementations that let you use your servlet container's JDBC Datasource implementation and related connection pooling features.

This XSQL Pages release comes with two of these alternative connection manager implementations:

  • oracle.xml.xsql.XSQLDatasourceConnectionManager

    Consider using this alternative connection manager if your servlet container's datasource implementation does not use the Oracle JDBC driver under the covers. Certain features of the XSQL Pages system will not be available when you are not using an Oracle JDBC driver, like <xsql:ref-cursor-function> and <xsql:include-owa>.

  • oracle.xml.xsql.XSQLOracleDatasourceConnectionManager

    Consider using this alternative connection manager when you know that your datasource implementation returns JDBC PreparedStatement and CallableStatement objects that implement the oracle.jdbc.PreparedStatement and oracle.jdbc.CallableStatement interfaces respectively. The Oracle Application Server has a datasource implementation that does this.

When using either of these alternative connection manager implementations, the value of the connection attribute in your XSQL page template is the JNDI name used to lookup your desired datasource. For example, the value of the connection attribute might look something like:

  • jdbc/scottDS

  • java:comp/env/jdbc/MyDatasource

Remember that if you are not using the default XSQL Pages connection manager, then any connection pooling functionality that you need must be provided by the alternative connection manager implementation. In the case of the earlier two options that are based on JDBC Datasources, you are relying on properly configuring your servlet container to supply the connection pooling. See your servlet container's documentation for instructions on how to properly configure the datasources to offer pooled connections.

Writing Custom XSQL Connection Managers

You can provide a custom connection manager to replace the built-in connection management mechanism. To provide a custom connection manager implementation, you must provide:

  1. A connection manager factory object that implements the oracle.xml.xsql.XSQLConnectionManagerFactory interface.

  2. A connection manager object that implements the oracle.xml.xsql.XSQLConnectionManager interface.

Your custom connection manager factory can be set to be used as the default connection manager factory by providing the class name in the XSQL configuration file (by default, named XSQLConfig.xml) in the section:

<!--
     | Set the name of the XSQL Connection Manager Factory
     | implementation. The class must implement the
     | oracle.xml.xsql.XSQLConnectionManagerFactory interface.
     | If unset, the default is to use the built-in connection
     | manager implementation in 
     | oracle.xml.xsql.XSQLConnectionManagerFactoryImpl
+-->
  <connection-manager>
      <factory>oracle.xml.xsql.XSQLConnectionManagerFactoryImpl</factory>
  </connection-manager>

In addition to specifying the default connection manager factory, a custom connection factory can be associated with any individual XSQLRequest object using APIs provided.

The responsibility of the XSQLConnectionManagerFactory is to return an instance of an XSQLConnectionManager for use by the current request. In a multithreaded environment like a servlet engine, it is the responsibility of the XSQLConnectionManager object to insure that a single XSQLConnection instance is not used by two different threads. This can be assured by marking the connection as "in use" for the span of time between the invocation of the getConnection() method and the releaseConnection() method. The default XSQL connection manager implementation automatically pools named connections, and adheres to this thread-safe policy.

If your custom implementation of XSQLConnectionManager implements the optional oracle.xml.xsql.XSQLConnectionManagerCleanup interface as well, then your connection manager will be given a chance to cleanup any resources it has allocated. For example, if your servlet container invokes the destroy() method on the XSQLServlet servlet, which can occur during online administration of the servlet for example, this will give the connection manager a chance to clean up resources as part of the servlet destruction process.

Providing a Custom XSQLErrorHandler Implementation

You may want to control how serious page processor errors (like a connection's being unavailable) are reported to users. Writing a class that implements the oracle.xml.xsql.XSQLErrorHandler interface enables you to do this. The interface contains the single method:

public interface XSQLErrorHandler {
  public void handleError( XSQLError err, XSQLPageRequest env);
}

You can provide a class that implements the XSQLErrorHandler interface to customize how the XSQL page processor writes out any page processor error messages. The new XSQLError object encapsulates the error information and provides access to the error code, formatted error message, and so on.

For example, here is a sample implementation of XSQLErrorHandler:

package example;
import oracle.xml.xsql.*;
import java.io.*;
/**
 * Example of a custom XSQLErrorHandler implementation
 */
public class MyErrorHandler implements XSQLErrorHandler {
  public void logError( XSQLError err, XSQLPageRequest env) {
    // Must set the content type before writing anything out
    env.setContentType("text/html");
    PrintWriter pw = env.getErrorWriter();
    pw.println("<H1>ERROR</H1><hr>"+err.getMessage());    
  }
}

You can control which custom XSQLErrorHandler implementation gets used in two distinct ways:

  1. You can define the name of a custom XSQLErrorHandler implementation class in the XSQL configuration file (by default, named XSQLConfig.xml) by providing the fully-qualified class name of your error handler class as the value of the /XSQLConfig/processor/error-handler/class entry.

  2. If the Page Processor can load this class and it correctly implements the XSQLErrorHandler interface, then this class is used as a singleton and replaces the default implementation globally, wherever page processor errors are reported.

  3. You can override the error writer on a for each page basis using the new, optional errorHandler (or xsql:errorHandler) attribute on the document element of your page.

  4. The value of this attribute is the fully-qualified class name of a class that implements the XSQLErrorHandler interface. This class will be used to report the errors for just this page and the class is instantiated on each page request by the page engine.

You can use a combination of both approaches if needed.

Providing a Custom XSQL Logger Implementation

You can optionally register custom code to handle the logging of the start and end of each XSQL page request. Your custom logger code must provide an implementation of the two interfaces oracle.xml.xsql.XSQLLoggerFactory and oracle.xml.xsql.XSQLLogger.

The XSQLLoggerFactory interface contains the single method:

public interface XSQLLoggerFactory {
  public XSQLLogger create( XSQLPageRequest env);
}

You can provide a class that implements the XSQLLoggerFactory interface to decide how XSQLLogger objects are created (or reused) for logging. The XSQL Page processor holds a reference to the XSQLLogger object returned by the factory for the duration of a page request and uses it to log the start and end of each page request by invoking the logRequestStart() and logRequestEnd() methods on it.

The XSQLLogger interface looks like this:

public interface XSQLLogger {
   public void logRequestStart(XSQLPageRequest env) ;
   public void logRequestEnd(XSQLPageRequest env);
}

The following two classes illustrate a trivial implementation of a custom logger. First is the XSQLLogger implementation which notes the time the page request started and then logs the page request end by printing the name of the page request and the elapsed time to System.out:

package example;
import oracle.xml.xsql.*;
public class SampleCustomLogger implements XSQLLogger  {
  long start = 0;
  public void logRequestStart(XSQLPageRequest env) {
    start = System.currentTimeMillis();
  }
  public void logRequestEnd(XSQLPageRequest env) {
    long secs = System.currentTimeMillis() - start;
    System.out.println("Request for " + env.getSourceDocumentURI()
                        + " took "+ secs + "ms");
  }
}

Next, the factory implementation:

package example;
import oracle.xml.xsql.*;
public class SampleCustomLoggerFactory implements XSQLLoggerFactory {
  public XSQLLogger create(XSQLPageRequest env) {
    return new SampleCustomLogger();
  }
}

To register a custom logger factory, edit the XSQLConfig.xml file and provide the name of your custom logger factory class as the content to the /XSQLConfig/processor/logger/factory element like this:

<XSQLConfig>
    :
  <processor>
         :
      <logger>
         <factory>example.SampleCustomLoggerFactory</factory>
      </logger>
         :
   </processor>
</XSQLConfig>

By default, this <logger> section is commented out, and there is no default logger.

Formatting XSQL Action Handler Errors

Errors raised by the processing of any XSQL Action Elements are reported as XML elements in a uniform way so that XSL Stylesheets can detect their presence and optionally format them for presentation.

The action element in error will be replaced in the page by:

<xsql-error action="xxx"> 

Depending on the error the <xsql-error> element contains:

  • A nested <message> element

  • A <statement> element with the offending SQL statement

Displaying Error Information on Screen

Here is an example of an XSLT stylesheet that uses this information to display error information on the screen:

<xsl:if test="//xsql-error">
     <table style="background:yellow">
        <xsl:for-each select="//xsql-error">
           <tr>
            <td><b>Action</b></td>
            <td><xsl:value-of select="@action"/></td>
            </tr>
            <tr valign="top">
            <td><b>Message</b></td>
            <td><xsl:value-of select="message"/></td>
           </tr>
          </xsl:for-each>
     </table>
</xsl:if>

XSQL Servlet Limitations and Hints

XSQL Servlet has the following limitations:

HTTP Parameters with Multibyte Names

HTTP parameters with multibyte names, for example, a parameter whose name is in Kanji, are properly handled when they are inserted into your XSQL page using <xsql:include-request-params>. An attempt to refer to a parameter with a multibyte name inside the query statement of an <xsql:query> tag will return an empty string for the parameter's value.

As a workaround use a non-multibyte parameter name. The parameter can still have a multibyte value which can be handled correctly.

CURSOR() Function in SQL Statements

If you use the CURSOR() function in SQL statements you may get an "Exhausted ResultSet" error if the CURSOR() statements are nested and if the first row of the query returns an empty result set for its CURSOR() function.

Hints for Using the XSQL Servlet

This section lists XSQL Servlet hints.

Specifying a DTD While Transforming XSQL Output to a WML Document

There is a way to specify a particular DTD while transforming XSQL output to a WML document for a wireless application.

The way you do it is using a built-in facility of the XSLT stylesheet called <xsl:output>. Here is an example:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output type="xml" doctype-system="your.dtd"/>
  <xsl:template match="/">
  </xsl:template>
    ...
    ...
</xsl:stylesheet>

This will produce an XML result with the following code in it:

<!DOCTYPE xxxx SYSTEM "your.dtd">

where "your.dtd" can be any valid absolute or relative URL.

Writing XSQL Servlet Conditional Statements

It is possible to write conditional statements in an XSQL file such as:

<xsql:choose>
    <xsql:when test="@security='admin'">
      <xsql:query>
          SELECT ....
      </xsql:query>
   </xsq:when>
   <xsql:when test="@security='user'">
      <xsql:query>
          SELECT ....
      </xsql:query>
   </xsql:when>
</xsql:if>

Use <xsql:ref-cursor-function> to call a PL/SQL procedure that conditionally returns a REF CURSOR to the appropriate query.

Using a Value Retrieved in One Query in Another Query's Where Clause

If you have two queries in an XSQL file, you can use the value of a select list item of the first query in the second query, using page parameters:

<page xmlns:xsql="urn:oracle-xsql" connection="demo">
  <!-- Value of page param "xxx" will be first column of first row -->
  <xsql:set-page-param name="xxx">
    select one from table1 where ...
  </xsl:set-param-param>
  <xsql:query bind-params="xxx">
     select col3,col4 from table2
    where col3 = ?
  </xsql:query>
</page>

Using the XSQL Servlet with Non-Oracle Databases

The XSQL Servlet can connect to any database that has JDBC support. Just indicate the appropriate JDBC driver class and connection URL in the XSQL configuration file (by default, named XSQLConfig.xml) connection definition. Of course, object/relational functionality only works when using Oracle with the Oracle JDBC driver.

Handling Multi-Valued HTML Form Parameters

There is a way to handle multi-valued HTML <form> parameters which are needed for <input name="choices" type="checkbox">.

Use the parameter array notation on your parameter name (for example, choices[]) to refer to the array of values from the selected check boxes.

For example, if you have a multi-valued parameter named guy, then you can use the array-parameter notation in an XSQL page like this:

<page xmlns:xsql="urn:oracle-xsql">
  <xsql:set-page-param name="guy-list" value="{@guy[]}"
                       treat-list-as-array="yes"/>
  <xsql:set-page-param name="quoted-guys" value="{@guy[]}"
                       treat-list-as-array="yes" quote-array-values="yes"/>
  <xsql:include-param name="guy-list"/>
  <xsql:include-param name="quoted-guys"/>
  <xsql:include-param name="guy[]"/>
</page>

If this page is requested with the URL following, containing multiple parameters of the same name to produce a multi-valued attribute:

http://yourserver.com/page.xsql?guy=Curly&guy=Larry&guy=Moe

then the page returned will be:

<page>
  <guy-list>Curly,Larry,Moe</guy-list>
  <quoted-guys>'Curly','Larry','Moe'</quoted-guys>
  <guy>
    <value>Curly</value>
    <value>Larry</value>
    <value>Moe</value>
  </guy>
</page>

You can also use the value of the multi-valued page parameter preceding nonzero in a SQL statement by using the following code:

<page connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:set-page-param name="quoted-guys" value="{@guy[]}"
                       treat-list-as-array="yes" quote-array-values="yes"/>
  <xsql:query>
    SELECT * FROM sometable WHERE name IN ({@quoted-guys})
  </xsql:query>
</page>

Running the XSQL Servlet with Oracle Release 7.3

Make sure you're using the JDBC driver, which can connect to an Oracle release 7.3 database with no problems.

Out Variable is not Supported in <xsql:dml>

You cannot set parameter values by binding them in the position of OUT variables in this release using <xsql:dml>. Only IN parameters are supported for binding. You can create a wrapper procedure that constructs XML elements using the HTTP package and then your XSQL page can invoke the wrapper procedure using <xsql:include-owa> instead.

For an example, suppose you had the following procedure:

CREATE OR REPLACE PROCEDURE addmult(arg1        NUMBER,
                                    arg2        NUMBER,
                                    sumval  OUT NUMBER,
                                    prodval OUT NUMBER) IS
BEGIN
  sumval := arg1 + arg2;
  prodval := arg1 * arg2;
END;

You can write the following procedure to wrap it, taking all of the IN arguments that the procedure preceding expects, and then encoding the OUT values as a little XML datagram that you print to the OWA page buffer:

CREATE OR REPLACE PROCEDURE addmultwrapper(arg1 NUMBER, arg2 NUMBER) IS
  sumval  NUMBER;
  prodval NUMBER;
  xml     VARCHAR2(2000);
BEGIN
  -- Call the procedure with OUT values
  addmult(arg1,arg2,sumval,prodval);
  -- Then produce XML that encodes the OUT values
  xml := '<addmult>'||
         '<sum>'||sumval||'</sum>'||
         '<product>'||prodval||'</product>'||
         '</addmult>';
  -- Print the XML result to the OWA page buffer for return
  HTP.P(xml);
END;

This way, you can build an XSQL page like this that calls the wrapper procedure:

<page connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:include-owa bind-params="arg1 arg2">
    BEGIN addmultwrapper(?,?); END;
  </xsql:include-owa>
</page>

This allows a request like the following:

http://yourserver.com/addmult.xsql?arg1=30&arg2=45

to return an XML datagram that reflects the OUT values like this:

<page>
  <addmult><sum>75</sum><product>1350</product></addmult>
</page>

Receiving "Unable to Connect" Errors

Suppose that you are unable to connect to a database and get errors running a program like the helloworld.xsql sample:

Oracle XSQL Servlet Page Processor 9.0.0.0.0 (Beta)
XSQL-007: Cannot acquire a database connection to process page.
Connection refused(DESCRIPTION=(TMP=)(VSNNUM=135286784)(ERR=12505)
(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))

If you get this far, it's actually attempting the JDBC connection based on the <connectiondef> information for the connection named demo, assuming you did not modify the helloworld.xsql demo page.

By default the XSQLConfig.xml file comes with the entry for the demo connection that looks like this:

<connection name="demo">
  <username>scott</username>
  <password>tiger</password>
  <dburl>jdbc:oracle:thin:@localhost:1521:ORCL</dburl>
  <driver>oracle.jdbc.driver.OracleDriver</driver>
</connection>

The error you're getting is likely because of one of the following reasons:

  1. Your database is not on the localhost machine.

  2. Your database SID is not ORCL.

  3. Your TNS Listener Port is not 1521.

Make sure those values are appropriate for your database and you have no problems.

Using Other File Extensions Besides .xsql

The .xsql extension is just the default extension used to recognize XSQL pages. You can modify your servlet engine's configuration settings to associate any extension you like with the oracle.xml.xsql.XSQLServlet servlet class using the same technique that was used to associate the *.xsql extension with it.

Receiving "No Posted Document to Process" when you Try to Post XML

When trying to post XML information to an XSQL page for processing, it must be sent by the HTTP POST method. This can be an HTTP POST-ed HTML Form or an XML document sent by HTTP POST. If you try to use HTTP GET instead, there is no posted document, and hence you get this error. Use HTTP POST instead to have the correct behavior.

XSQL Supports SOAP

Your page can access contents of the inbound SOAP message using the <xsql:set-page-param> action's xpath="XpathExpression" attribute. Alternatively, your customer action handlers can gain direct access to the posted SOAP message body by calling getPageRequest().getPostedDocument(). To create the SOAP response body to return to the client, you can either use an XSLT stylesheet or a custom serializer implementation to write out the XML response in an appropriate SOAP-encoded format.

See the supplied AirportSOAP demo that comes with the XSQL Pages framework for an example of using an XSQL page to implement a SOAP-based Web Service.

Passing the Connection for XSQL

Reference an XSQL parameter in your page's connection attribute, making sure to define an attribute of the same name to serve as the default value for the connection name. For example:

<xsql:query conn="testdb" connection="{@conn}" xmlns:xsql="urn:oracle-xsql">
  ...
</xsql:query> 

If you retrieve this page without any parameters, the value of the conn parameter will be testdb, so the page will use the connection named testdb defined in the XSQL configuration file (by default, named XSQLConfig.xml). If instead you request the page with conn=proddb, then the page will use the connection named proddb instead.

Controlling How Database Connections and Passwords Are Stored

If you need a more sophisticated set of username and password management than the one that is provided by default in XSQL using the XSQL configuration file.

You can completely redefine the way the XSQL Page Processor handles database connections by creating your own implementation of the XSQLConnectionManager interface. To achieve this, you need to write a class that implements the oracle.xml.xsql.XSQLConnectionManagerFactory interface and a class that implements the oracle.xml.xsql.XSQLConnectionManager interface, then change the name of the XSQLConnectionManagerFactory class to use in your XSQL configuration file. Once you've done this, your connection management scheme will be used instead of the XSQL Pages default scheme.

Accessing Authentication Information in a Custom Connection Manager

If you want to use the HTTP authentication mechanism to get the username and password to connect to the database. It is possible to get this kind of information in a custom connection manager's getConnection() method.

The getConnection() method is passed an instance of the XSQLPageRequest interface. From it, you can get the HTTP Request object by:

  1. Testing the request type to make sure it's "Servlet"

  2. Casting XSQLPageRequest to XSQLServletPageRequest

  3. Calling getHttpServletRequest() on the result of (2)

You can then get the authentication information from that HTTP Request object.

Retrieving the Name of the Current XSQL Page

There is a way for an XSQL page to access its own name in a generic way at runtime in order to construct links to the current page. You can use a helper method like this to retrieve the name of the page inside a custom action handler:

// Get the name of the current page from the current page's URI
  private String curPageName(XSQLPageRequest req) {
    String thisPage = req.getSourceDocumentURI();;
    int pos = thisPage.lastIndexOf('/');
    if (pos >=0) thisPage = thisPage.substring(pos+1);
    pos = thisPage.indexOf('?');
    if (pos >=0) thisPage = thisPage.substring(0,pos-1);
    return thisPage;
  }

Resolving Errors When Using the FOP Serializer

You can format XML into PDF using Formatting Object (FOP). If you get an error trying to use the FOP Serializer, typically the problem is that you do not have all of the required JAR files in the CLASSPATH. The XSQLFOPSerializer class resides in the separate xsqlserializers.jar file, and this must be in the CLASSPATH to use the FOP integration.

Then, the XSQLFOPSerializer class itself has dependencies on several libraries from Apache. For example, here is the source code for a FOP Serializer that works with the Apache FOP 0.20.3RC release candidate of the FOP software:

package sample;
import org.w3c.dom.Document;
import org.apache.log.Logger;
import org.apache.log.Hierarchy;
import org.apache.fop.messaging.MessageHandler;
import org.apache.log.LogTarget;
import oracle.xml.xsql.XSQLPageRequest;
import oracle.xml.xsql.XSQLDocumentSerializer;
import org.apache.fop.apps.Driver;
import org.apache.log.output.NullOutputLogTarget;

/**
 * Tested with the FOP 0.20.3RC release from 19-Jan-2002
 */
public class SampleFOPSerializer implements XSQLDocumentSerializer {
  private static final String PDFMIME = "application/pdf";
  public void serialize(Document doc, XSQLPageRequest env) throws Throwable {
    try {
      // First make sure we can load the driver
      Driver FOPDriver = new Driver();
      // Tell FOP not to spit out any messages by default.
      // You can modify this code to create your own FOP Serializer
      // that logs the output to one of many different logger targets
      // using the Apache LogKit API
      Logger logger = Hierarchy.getDefaultHierarchy()
                                  .getLoggerFor("XSQLServlet");
      logger.setLogTargets(new LogTarget[]{new NullOutputLogTarget()});
      FOPDriver.setLogger(logger);
      // Some of FOP's messages appear to still use MessageHandler.
      MessageHandler.setOutputMethod(MessageHandler.NONE);
      // Then set the content type before getting the reader/
      env.setContentType(PDFMIME);
      FOPDriver.setOutputStream(env.getOutputStream());
      FOPDriver.setRenderer(FOPDriver.RENDER_PDF);
      FOPDriver.render(doc);
    }
    catch (Exception e) {
      // Cannot write PDF output for the error anyway.
      // So maybe this stack trace will be useful info
      e.printStackTrace(System.err);
    }
  }
}

This FOP serializer depends on having the following additional Apache JAR files in the CLASSPATH at runtime:

  1. fop.jar - Apache FOP Rendering Engine

  2. batik.jar - Apache Batik SVG Rendering Engine

  3. avalon-framework-4.0.jar - APIs for Apache Avalon Framework

  4. logkit-1.0.jar - APIs for the Apache Logkit

Tuning XSQL Pages for Fastest Performance

The biggest thing that affects the performance is the size of the data you are querying (and of course the pure speed of the queries). Assuming you have tuned your queries and used bind variables instead of lexical bind variables wherever allowed by SQL, then the key remaining tip is to make sure you are only querying the minimum amount of data needed to render the needed result.

If you are querying thousands of rows of data, only to use your XSLT stylesheet to filter the rows to present only 10 of those rows in the browser, then this is a bad choice. Use the database's capabilities to the maximum to filter the rows and return only the 10 rows you care about if at all possible. Think of XSQL as a thin coordination layer between Oracle database and the power of XSLT as a transformation language.

Using XSQL with Other Connection Pool Implementations

You can set up XSQL pages to use connections taken from a connection pool, if for example, you are running XSQL servlet in a WebLogic Web server.

XSQL implements it's own connection pooling so in general you don't have to use another connection pool, but if providing the JDBC connection string of appropriate format is not enough to use the WebLogic pool, then you can create your own custom connection manager for XSQL by implementing the interfaces XSQLConnectionManagerFactory and XSQLConnectionManager.

Including XML Documents Stored in CLOBs in Your XSQL Page

Use <xsql:include-xml> with a query to retrieve the CLOB value.

Combining JavaServer Pages and XSQL in the Same Page

Is it possible to combine XSQL and JavaServer Pages (JSP) tags in the same page or do you use include tags for that?

JSP and XSQL are two different models. JSP is a model that is based on writing streams of characters to an output stream. XSQL is a model that is pure XML and XSLT-based. At the end of the day, some result like HTML or XML comes back to the user, and there really isn't anything that you can implement with XSQL that you cannot implement in JSP by writing code and working with XML documents as streams of characters, doing lots of internal reparsing. XSQL fits the architecture when customers want to cleanly separate the data content (represented in XML) from the data presentation (represented by XSLT stylesheets). Since it specializes in this XML/XSLT architecture, it is optimized for doing that.

You can, for example, use <jsp:include> or <jsp:forward> to have a JSP page include/forward to an XSQL page. This is the best approach.

Choosing a Stylesheet Based on Input Arguments

It is possible to change stylesheets dynamically based on input arguments.

You can achieve this by using a lexical parameter in the href attribute of your xml-stylesheet processing instruction.

<?xml-stylesheet type="text/xsl" href="{@filename}.xsl"?>

The value of the parameter can be passed in as part of the request, or by using the <xsql:set-page-param> you can set the value of the parameter based on a SQL query.

Sorting the Result Within the Page

The following question was presented:

I have a set of 100 records, and I am showing 10 at a time. On each column name I have made a link. When that link is clicked, I want to sort the data in the page alone, based on that column.

If you are writing for IE5 alone and receiving XML data, you can use Microsoft's XSL to sort data in a page. If you are writing for another browser and the browser is getting the data as HTML, then you have to have a sort parameter in XSQL script and use it in ORDER BY clause. Just pass it along with the skip-rows parameter.