Skip Headers

Oracle® Application Developer's Guide - XML 10g (9.0.4) Part Number B12099-01 |
Home | Contents | Index

# 10
XSQL Pages Publishing Framework

This chapter contains the following sections:

- XSQL Pages Publishing Framework Overview

  - What Can I Do with Oracle XSQL Pages?

  - Where Can I Obtain Oracle XSQL Pages?

  - What's Needed to Run XSQL Pages?

- Overview of Basic XSQL Pages Features

  - Producing XML Datagrams from SQL Queries

  - Transforming XML Datagrams into an Alternative XML Format

  - Transforming XML Datagrams into HTML for Display

- Setting Up and Using XSQL Pages in Your Environment

  - Using XSQL Pages With Oracle JDeveloper

  - Setting the CLASSPATH Correctly in Your Production Environment

  - Setting Up the Connection Definitions

  - Using the XSQL Command Line Utility

- Overview of All XSQL Pages Capabilities

  - Using All of the Core Built-in Actions

  - Aggregating Information Using <xsql:include-xsql>

  - Handling Posted Information

  - Using Custom XSQL Action Handlers

- Description of XSQL Servlet Examples

  - Setting Up the Demo Data

- Advanced XSQL Pages Topics

  - Understanding Client Stylesheet-Override Options

  - Controlling How Stylesheets are Processed

  - Using XSQLConfig.xml to Tune Your Environment

  - Using the FOP Serializer to Produce PDF Output

  - Using XSQL Page Processor Programmatically

  - Writing Custom XSQL Action Handlers

  - Writing Custom XSQL Serializers

  - Writing Custom XSQL Connection Managers

  - Formatting XSQL Action Handler Errors

- XSQL Servlet Limitations

- Frequently Asked Questions (FAQs) - XSQL Servlet

## 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:

- Assemble dynamic XML "datagrams" based on parameterized SQL queries, and

- Transform these "data pages" to produce a final result in any desired XML, HTML, or text-based format using an associated XSLT transformation.

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 allows you to:

- Present the same information in multiple ways, including tailoring the presentation appropriately to the kind of client device making the request (brower, cellular phone, PDA, etc.).

- Reuse information easily by aggregating existing pages into new ones

- Revise and enhance the presentation independently of the information content being presented.

### 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 10-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 10-1 Understanding the Architecture of the XSQL Pages Framework

Text 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 emails, SQL scripts, Java programs, etc.

- 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.

See Also: Appendix C, "XDK for Java: Specifications and Cheat Sheets" for the XSQL Servlet specifications and cheat sheets XSQL Servlet Release Notes on OTN at: http://otn.oracle.com/tech/xml

### Where Can I Obtain Oracle XSQL Pages?

XSQL Servlet is provided with Oracle and is also available for download from the OTN site: http://otn.oracle.com/tech/xml.

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

### What's 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 and comes bundled with 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.zip`) for maximum functionality and performance.

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

To use the XSQL Pages framework for Web publishing, in addition to the above you'll need a web server that supports Java Servlets. The following is the list of web servers with Servlet capability on which the XSQL Servlet has been tested:

- Oracle9i Internet Application Server v1.x and v2.x

- Oracle9i Oracle Servlet Engine

- Allaire JRun 2.3.3 and 3.0.0

- Apache 1.3.9 or higher with JServ 1.0/1.1 or Tomcat 3.1/3.2 Servlet Engine

- Apache Tomcat 3.1 or 3.2 Web Server + Servlet Engine

- Caucho Resin 1.1

- Java Web Server 2.0

- Weblogic 5.1 Web Server

- NewAtlanta ServletExec 2.2 and 3.0 for IIS/PWS 4.0

- Oracle8i Lite Web-to-Go Server

- Sun JavaServer Web Development Kit (JSWDK) 1.0.1 Web Server

  Note: For security reasons, when installing XSQL Servlet on your production web server, make sure `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.

For details on installing, configuring your environment, and running XSQL Servlet and for additional examples and guidelines, see the XSQL Servlet "Release Notes" on OTN at http://otn.oracle.com/tech/xml

## Overview of Basic XSQL Pages Features

In this section, we'll get 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

- Transforming the XML Datagram into an Alternative XML Format

- Transforming the XML Datagram into HTML for Display

### 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's 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 below, 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 above 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 requestor. This XML-based "datagram" would typically be 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 10-2.

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

Text description of the illustration xsql1.gif

Let's take a closer look at the "anatomy" of 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 above 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 "nickname" 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 `XSQLConfig.xml` configuration file:

<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'll 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 10-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 Descriptor (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's 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 10-3.

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

Text 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 "morph" 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 10-4.

#### Figure 10-4 XSQL Page Results in "Industry Standard" XML Format

Text 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 would then look like the HTML page shown in Figure 10-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 10-5 Using an Associated XSLT Stylesheet to Render HTML

Text 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 (e.g. <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 above is just scratching the surface of what you can do using XSQL pages.

Note: For a detailed introduction to XSLT and a thorough tutorial on how to apply XSLT to many different Oracle database scenarios, see "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 should 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.zip`, the Oracle JDBC driver

In addition, the directory where XSQL Page Processor's configuration file `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 would appear as follows:

    C:\xsql\lib\classes12.zip;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 would appear as follows:

    /web/xsql/lib/classes12.zip:/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's 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's 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 "nickname" for the connection defined in the XSQL configuration file. Connection names are defined in the `<connectiondefs>` section of `XSQLConfig.xml` file 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>true</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. Note that such column aliasing is required for columns whose names would otherwise be an illegal name for an XML element.

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

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

would produce 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(hiredate,'DD-MON') as hiredate FROM EMP</xsql:query>

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

##### Table 10-1 Attributes for <xsql:query>

Attribute Name | Description
--- | ---
bind-params = "string" | Ordered, space-separated 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-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 should 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 10-2 lists the optional attributes that you can use on the <xsql:dml> action.

##### Table 10-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-separated 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-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 allows 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/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 10-1.

For example, consider the PL/SQL package below:

    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
          FOR 'SELECT empno, ename FROM EMP'; -- An EMP Query
       ELSE
         OPEN the_cursor
          FOR 'SELECT dname, deptno FROM DEPT'; -- A DEPT Query
       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 allows 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/or HTF packages

    </xsql:include-owa>

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

##### Table 10-3 Attributes for <xsql:include-owa>

Attribute Name | Description
--- | ---
bind-params = "string" | Ordered, space-separated 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-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 above actions, you can use SQL bind variables. This allows 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, pameter values are bound to the bind variable by specifying the bind-params attribute on the action element. Using the example above, we could 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-separated 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-separated 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 allows 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 could 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 would be 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>

Would return 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

Would return:

<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 would return:

<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 above 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 allows 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 allows 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 would like to include. This action has no optional attributes.

The XML included will have the form:

    <paramname>value1</paramname>

#### The <xsql:include-xml> Action

The `<xsql:include-xml>` action includes the XML contents of a local or remote resource into your datagram. The resource is specified by URL.

The syntax for this action is:

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

The URL can be an absolute, http-based URL to retrieve XML from another web site, or a relative URL to include XML from a file on the file system. The `href` attribute is required, and this action has no other optional attributes.

#### 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>

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.

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

Table 10-4 lists the attributes supported by this action. Attributes in bold are required.

##### Table 10-4 Attributes for <xsql:set-page-param>

Attribute Name | Description
--- | ---
name = "string" | Name of the page-private parameter whose value you want to set.
bind-params = "string" | Ordered, space-separated 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.
ignore-empty-value = "boolean" | Indicates whether the page-level parameter assignment should 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`.

#### 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.

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

Table 10-5 lists the optional attributes supported by this action.

##### Table 10-5 Attributes for <xsql:set-session-param>

Attribute Name | Description
--- | ---
name = "string" | Name of the session-level variable whose value you want to set.
bind-params = "string" | Ordered, space-separated 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.
ignore-empty-value = "boolean" | Indicates whether the page-level parameter assignment should 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`.
only-if-unset = "boolean" | Indicates whether the session variable assignment should only occur 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 it's lifetime can be changed by supplying the optional `max-age` attribute. 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 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.

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

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

##### Table 10-6 Attributes for <xsql:set-cookie>

Attribute Name | Description
--- | ---
name = "string" | Name of the cookie whose value you want to set.
bind-params = "string" | Ordered, space-separated 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 hostname (e.g. `bigserver.yourcompany.com`) of the document creating the cookie.
ignore-empty-value = "boolean" | Indicates whether the page-level parameter assignment should 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`.
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 should only occur 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.

#### 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.

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

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

##### Table 10-7 Attributes for <xsql:set-stylesheet-param>

Attribute Name | Description
--- | ---
name = "string" | Name of the top-level stylesheet parameter whose value you want to set.
bind-params = "string" | Ordered, space-separated 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.
ignore-empty-value = "boolean" | Indicates whether the page-level parameter assignment should 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`.

### 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 allows you to easily aggregate content from a page that you've already built and repurpose it. The examples below 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 aggegrates `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 repurposed 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 would expect, none of the aggregating page's page-private parameters are visible to the nested page.

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

##### Table 10-8 Attributes for <xsql:include-xsql>

Attribute Name | Description
--- | ---
href = "string" | Relative or absolute URL of XSQL page to be included.
reparse = "boolean" | Indicates whether output of included XSQL page should 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`.

### 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/or 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 10-9 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 10-10 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 10-11 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 10-12 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 could 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 10-9 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-separated or comma-separated 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-batch-size = "integer" | If a positive, non-zero 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.

##### Table 10-10 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-separated or comma-separated 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-separated or comma-separated 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-batch-size = "integer" | If a positive, non-zero 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.

##### Table 10-11 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-separated or comma-separated 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-batch-size = "integer" | If a positive, non-zero 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.

##### Table 10-12 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-separated or comma-separated 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-batch-size = "integer" | If a positive, non-zero 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.

#### 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 10-13 lists the XSQL Servlet example applications supplied with the software in the `./demo` directory.

##### Table 10-13 XSQL Servlet Examples

Demonstration Name | Description
--- | ---
Hello World `./demo/helloworld` | Simplest possible XSQL page.
Do You XML Site `./demo/doyouxml` | XSQL page shows how a to build a data-driven web site with an XSQL page. Uses SQL, XSQL-substitution variables in queries, and XSLT to format. Uses substitution parameters in SQL statements in `<xsql:query>` tags, and in attributes to `<xsql:query> t`ags, to control for example how many records to display, or to skip, for paging through query results.
Employee Page `./demo/emp` | XSQL page displays XML data from EMP table, using XSQL page parameters to control employees and data sorting. Uses an associated XSLT Stylesheet to format results as HTML version of emp.xsql page. This is the form action hence you can fine tune your search criteria.
Insurance Claim Page `./demo/insclaim` | Shows sample queries over a structured, Insurance Claim object view. `insclaim.sql` sets up the INSURANCE_CLAIM_VIEW object view and populates it with sample data.
Invalid Classes Page `./demo/classerr` | XSQL Page uses `invalidclasses.xsl` to format a "live" list of current Java class compilation errors in your schema. The .sql script sets up XSQLJavaClassesView object view for the demo. Master/detail information from 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. 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 Airport Code Validation 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.
Emp/Dept Object Demo `./demo/empdept` | How to use an object view to group master/detail information from two existing "flat" tables like EMP and DEPT. `empdeptobjs.sql` script creates the object view and INSTEAD OF INSERT triggers, allowing the use of master/detail view as an insert target of xsql:insert-request. `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. Part of XSLT 1.0 specification called using a Literal Result Element as Stylesheet. Shows 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` | Shows how to pass an SQL query and XSLT Stylesheet to use as parameters to the server. NOTE: Deploying this demo page to your production environment should be given particular consideration because it allows the results of any SQL query in XML format over the Web that your SCOTT user account has access to.
XML Document Demo `./demo/document` | How to insert XML documents into relational tables. `docdemo.sql` script creates a user-defined type called `XMLDOCFRAG` containing an attribute of type CLOB. Insert the text of the document in .`/xsql/demo/xml99.xml` and provide the name`xml99.xsl` as the stylesheet Insert the text of the document in`./xsql/demo/JDevRelNotes.xml` with the stylesheet `relnotes.xsl`. `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. XML Document demo uses client-side XML features of Internet Explorer 5.0 to check the document for well-formedness before it is posted to the server.
XML Insert Request Demo `./demo/insertxml` | Posts XML from a client to an XSQL Page that inserts the posted XML information into a database table using the `<xsql:insert-request>` action element. The demo accepts XML documents in the moreover.com XML-based news format. The program posting the XML is a client-side web page using Internet Explorer 5.0 and the XMLHttpRequest object from JavaScript. The source for `insertnewsstory.xsql` page, specifies a table name and XSLT Transform name. `moreover-to-newsstory.xsl` stylesheet transforms the incoming XML into canonical format that `OracleXMLSave` utility can insert. Copy and paste the example <article> element several times within the <moreovernews> element to insert several new articles in one shot. `newsstory.sql` 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,....
SVG Demo `./demo/svg` | `deptlist.xsql` page displays a simple list of departments with hyperlinks to `SalChart.xsql` page. `SalChart.xsql` page queries employees for a given department passed in as a parameter and uses the 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 on your machine.

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 functional index that one of the demos uses to perform case-insensitive queries on descriptions of airports.

- Connect to your database as SCOTT/TIGER.

- 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

- 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.

- 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

  ### 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 would have been used -- or to apply a stylesheet where none would have been 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 `XSQLConfig.xml` file)

  - 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 `XSQLConfig.xml` 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 XSQLConfig.xml 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

  #### 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 emp 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>EMPNO</th><th>ENAME</th><th>SAL</th></tr>

             <xsl:for-each select="ROWSET/ROW">

               <tr>

                 <td><xsl:value-of select="EMPNO"/></td>

                 <td><xsl:value-of select="ENAME"/></td>

                 <td><xsl:value-of select="SAL"/></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="demo" xmlns:xsql="urn:oracle-xsql">

        select * from emp order by sal 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 emp ORDER BY sal DESC

        </xsql:query>

      </page>

  would use the `emp.xsl` stylesheet to transform the results of the EMP 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 emp ORDER BY sal 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. Unfortunately 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 detectably different User-Agent string containing the 6.0 version number -- stylesheets delivered for client processing to IE5 browsers should 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 10-14 summarizes all of the supported pseudo-attributes allowed on the `<?xml-stylesheet?>` processing instruction.

  ##### Table 10-14 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 should 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 `XSQLConfig.xml` file.
  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 should be used insteaad. Valid values are either the name of a custom serializer defined in the `<serializerdefs>` section of the `XSQLConfig.xml` file, 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 XSQLConfig.xml to Tune Your Environment

  Use the `XSQLConfig.xml` File to tune your XSQL pages environment. Table 10-15 defines all of the parameters that can be set.

  ##### Table 10-15 XSQLConfig.xml Configuation Settings

  Configuration Setting Name
  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 charater 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's 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 roundtrips to the database from the servlet engine running in a different tier. Default is `50`. Valid value is any non-zero 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 non-zero 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 non-zero 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 non-zero 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 non-zero 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 non-zero 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 non-zero 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 non-zero 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 non-zero 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/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/security/stylesheet/defaults/allow-client-style While developing an application, it is frequently useful to take advantage of the XSQL page processor's per-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 could 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 URL's with the http protcol 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 URL's with the http protcol scheme. Valid value is any non-zero 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 Explicity 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`:

      <!-- EmpTableFO.xsl -->

      <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-name="first">

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

            <fo:block font-size="24pt" font-family="Garamond" line-height="24pt"

                      space-after.optimum="3pt" 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>

  you can format the results of a query against the EMP table using the supplied FOP serializer (pre-defined in `XSQLConfig.xml`) with an XSQL page like:

      <?xml version="1.0"?>

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

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

        SELECT ENAME, SAL FROM EMP

          ORDER BY SAL asc

      </xsql:query>

  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.16 or higher `w3c.jar` -- from the FOP distribution's `./lib` directory

  ### Using XSQL Page Processor Programmatically

  The `XSQLRequest` class, allows 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 `XSQLConfig.xml`-based connection 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 10-16 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 10-16 Built-In XSQL Elements and Action Handler Classes

  XSQL Action Element | Handler Class in oracle.xml.xsql.actions
  --- | ---
  <xsql:query> | `XSQLQueryHandler`
  <xsql:dml> | `XSQLDMLHandler`
  <xsql:set-stylesheet-param> | `XSQLStylesheetParameterHandler`
  `<xsql:insert-request>` | `XSQLInsertRequestHandler`
  `<xsql:include-xml>` | `XSQLIncludeXMLHandler`
  `<xsql:include-request-params>` | `XSQLIncludeRequestHandler`
  `<xsql:include-xsql>` | `XSQLIncludeXSQLHandler`
  `<xsql:include-owa>` | `XSQLIncludeOWAHandler`
  `<xsql:action>` | `XSQLExtensionActionHandler`
  `<xsql:ref-cursor-function>` | `XSQLRefCursorFunctionHandler`
  `<xsql:include-param>` | `XSQLGetParameterHandler`
  `<xsql:set-session-param>` | `XSQLSetSessionParamHandler`
  `<xsql:set-page-param>` | `XSQLSetPageParamHandler`
  `<xsql:set-cookie>` | `XSQLSetCookieHandler`
  `<xsql:insert-param>` | `XSQLInsertParameterHandler`
  `<xsql:update-request>` | `XSQLUpdateRequestHandler`
  `<xsql:delete-request>` | `XSQLDeleteRequestHandler`

  #### 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 should 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 should append any dynamically created XML content that should be 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 10-17 lists the methods that will likely come in handy for you.

  ##### Table 10-17 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 subelements. 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-separated 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.

  orac

  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's 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's

        // response output stream at the write moment later when all

        // action elements have been processed.

      }

  ### Writing Custom XSQL Serializers

  You can provide a user-defined serializer class to programmatically control how the final XSQL datapage's XML document should be 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 nickname to your custom serializers in the `<serializerdefs>` section of the `XSQLConfig.xml` file and then use the nickname (case-sensitive) in the serializer attribute instead to save typing. For example, if you have the following in `XSQLConfig.xml`:

      <XSQLConfig>

        <!-- etc. -->

        <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/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's 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 would need 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.

  ### 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 classname in the `XSQLConfig.xml` file 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 API's 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 threadsafe policy.

  ### 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

  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.

  #### Workaround

  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.

  ## Frequently Asked Questions (FAQs) - XSQL Servlet

  ### Specifying a DTD While Transforming XSQL Output to a WML Document

  I am trying to write my own stylesheet for transforming XSQL output to WML and VML format. These programs (mobile phone simulators) need a WML document with a specific DTD assigned.

  Is there any way, I can specify a particular DTD while transforming XSQL's output to a WML document.

  #### Answer

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

      <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 a:

  `<!DOCTYPE xxxx SYSTEM "your.dtd">`

  in the result. "your.dtd" can be any valid absolute or relative URL.

  ### XSQL Servlet Conditional Statements

  Is it possible to write conditional statements in a .xsql file, and if yes what is the syntax to do that?

  For example:

      <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>

  #### Answer

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

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

  I have two queries in an .xsql file.

      <xsql:query>
         select col1,col2
         from table1
      </xsql:query>
      <xsql:query>
         select col3,col4 from table2
         where col3 = {@col1}    => the value of col1 in the previous query
      </xsql:query>

  How can I use, in the second query, the value of a select list item of the first query?

  #### Answer

  You do this with 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>

  ### Working with Non-Oracle Databases

  Can the XSQL Servlet connect to any DB that has JDBC support?

  #### Answer

  Yes. Just indicate the appropriate JDBC driver class and connection URL in the `XSQLConfig.xml` file's connection definition. Of course, object/relational functionality only works when using Oracle with the Oracle JDBC driver.

  ### XSQL Servlet: Access to JServ Process

  I am running the demo helloworld.xsql. Initially I was getting the following error:

      XSQL-007 cannot aquire a database connection to process page

  Now my request times out and I see the following message in the jserv/log/jserv.log file:

      Connections from Localhost/127.0.0.1 are not allowed

  Is this a security issue? Do we have to give explicit permission to process an .xsql page. If so, how do we do that? I am using Apache web server and Apache jserver and Oracle as database. I have Oracle client installed and Tnsnames.ora file configured to get database connection. My XSQconnections.xml file is configured correctly.

  #### Answer

  This looks like a generic JServ problem. You have to make sure that your security.allowedAddresses=property in jserv.properties allows your current host access to the JServ process where Java runs. Can you successfully run *any* JServ Servlet?

  ### XSQL on Oracle8i Lite

  I am trying to use XSQL with Oracle8i Lite (Win 98) and Apache/JServ Webserver. I am getting error message "no oljdbc40 in java.library.path" even though I have set the olite40.jar in my classpath (which contains the POLJDBC driver). Is there anything extra I need to do to run XSQL for Oracle8i Lite.

  #### Answer

  You must include the following instruction in your `jserv.properties` file:

      wrapper.path=C:\orant\bin

  where `C:\orant\bin` is the directory where (by default) the `OLJDBC40.DLL` lives.

  Note that this is not `wrapper.classpath`,it's `wrapper.path`.

  ### Handling Multi-Valued HTML Form Parameters

  Is there any way to handle multi-valued HTML `<form>` parameters which is needed for `<input type="checkbox">`?

  #### Answer

  There is no built-in way, but you could use a custom Action Handler like this:

      // MultiValuedParam: XSQL Action Handler that takes the value of
      // ----------------  a multi-valued HTTP request parameter and
      //                   sets the value of a user-defined page-parameter
      //                   equal to the concatenation of the multiple values
      //                   with optional control over the separator used
      //                   between values and delimiter used around values.
      //                   Subsequent actions in the page can then reference
      //                   the value of the user-defined page-parameter.
      import oracle.xml.xsql.*;
      import javax.servlet.http.*;
      import org.w3c.dom.*;
      public class MultiValuedParam extends XSQLActionHandlerImpl {
        public void handleAction(Node root) {
          XSQLPageRequest req = getPageRequest();
          // Only bother to do this if we're in a Servlet environment
          if (req.getRequestType().equals("Servlet")) {
            Element      actElt = getActionElement();
            // Get name of multi-valued parameter to read from attribute
            String paramName = getAttributeAllowingParam("name",actElt);
            // Get name of page-param to set with resulting value
            String pageParam = getAttributeAllowingParam("page-param",actElt);
            // Get separator string
            String separator = getAttributeAllowingParam("separator",actElt);
            // Get delimiter string
            String delimiter = getAttributeAllowingParam("delimiter",actElt);
            // If the separator is not specified or is blank, use comma
            if (separator == null || separator.equals("")) {
              separator = ",";
            }
            // We're in a Servlet environment, so we can cast
            XSQLServletPageRequest spReq = (XSQLServletPageRequest)req;
            // Get hold of the HTTP Request
            HttpServletRequest httpReq = spReq.getHttpServletRequest();
            // Get the String array of parameter values
            String[] values = httpReq.getParameterValues(paramName);
            StringBuffer str = new StringBuffer();
            // If some values have been returned
            if (values != null) {
              int items = values.length;
              // Append each value to the string buffer
              for (int z = 0; z < items; z++) {
                // Add a separator before all but the first
                if (z != 0) str.append(separator);
                // Add a delimiter around the value if non-null
                if (delimiter != null) str.append(delimiter);
                str.append(values[z]);
                if (delimiter != null) str.append(delimiter);
              }
              // If page-param attribute not provided, default page param name
              if (pageParam == null) {
                pageParam = paramName+"-values";
              }
              // Set the page-param to the concatenated value
              req.setPageParam(pageParam,str.toString());
            }
          }
        }
      }

  Then you can use this custom action in a page like this:

      <page xmlns:xsql="urn:oracle-xsql">
        <xsql:action handler="MultiValuedParam" name="guy" page-param="p1" />

        <xsql:action handler="MultiValuedParam" name="guy" page-param="p2"

                     delimiter="'" />

        <xsql:action handler="MultiValuedParam" name="guy" page-param="p3"

                     delimiter="&quot;" separator=" " />

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

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

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

      </page>

  If this page is requested with the URL below, 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>
        <p1>Curly,Larry,Moe</p1>
        <p2>'Curly','Larry','Moe'</p2>
        <p3>"Curly" "Larry" "Moe"</p3>
      </page>

  Of course you could also use the value of the multi-valued page parameter above in a SQL statement by doing:

      <page connection="demo" xmlns:xsql="urn:oracle-xsql">

  <xsql:action handler="MultiValuedParam" name="guy" page-param="list"
  delimiter="'" />
  <!-- Above custom action sets the value of page param named 'list' -->
  <xsql:query>
  SELECT * FROM sometable WHERE name IN ({@list})
  </xsql:query>
  </page>

  ### XSQL Servlet and Oracle 7.3

  Is there anything that prevents me from running the XSQL Servlet with Oracle 7.3? I know the XML SQL Utility can be used with Oracle 7.3 as long as I use it as a client-side utility.

  #### Answer

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

  ### Out Variable Not Supported in <xsql:dml>

  I using `<xsql:dml>` to call a stored procedure which has one OUT parameter, but I was not able to see any results. The executed code results in the following statement:

      <xsql-status action="xsql:dml" rows="0"/>

  #### Answer

  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 HTP 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 could write the following procedure to "wrap" it, taking all of the IN arguments that the procedure above 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:

      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>

  ### Unable to Connect Errors

  Trying to play with XSQL I'm unable to connect to a database I get errors like this running the `helloworld.xsql` example:

      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))))

  Does this mean that it has actually found the config file? I have a user with scott/tiger setup.

  #### Answer

  Yes. If you get this far, it's actually attempting the JDBC connection based on the `<connectiondef>` info for the connection named "demo", assuming you didn't 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>

  So the error you're getting is likely because:

  1. Your database is not on "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 should be in business.

  ### Using Other File Extensions Besides *.xsql

  I want users to think they are accessing HTML files or XML files with extensions `.html` and `.xml` respectively, however I'd like to use XSQL so serve the HTML and XML to them. Is it possible to have the XSQL Servlet recognize files with an extension of `.html` and/or `.xml` in addition to the default `.xsql` extension?

  #### Answer

  Sure. There is nothing sacred about the `*.xsql` extension, it 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.

  ### Avoiding Errors for Queries Containing XML Reserved Characters

  I have a page like:

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

        SELECT id, REPLACE(company,'&','and') company, balance

          FROM vendors

         WHERE outstanding_balance < 3000

      </xsql:query>

  but when I try to request the page I get an error:

      XSQL-005: XSQL page is not well-formed.

      XML parse error at line 4, char 16

      Expected name instead of '

  What's wrong?

  #### Answer

  The problem is that the ampersand character (`&`) and the less-than sign (`<`) are reserved characters in XML because:

  - The `&` starts the sequence of characters that designates an entity reference like `&#160;` or `&lt;`

  - The `<` starts the sequence of characters that designates an element like `<SomeElement>`

  To include a literal ampersand character or less-than character you need to either encode each one as a entity reference like this:

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

        SELECT id, REPLACE(company,'&amp;','and') company, balance

          FROM vendors

         WHERE outstanding_balance &lt; 3000

      </xsql:query>

  Alternatively, you can surround an entire block of text with a so-called CDATA section that begins with the sequence `<![CDATA[` and ends with a corresponding `]]>` sequence. All text contained in the CDATA section is treated as literal.

  <xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
  <![CDATA[
  SELECT id, REPLACE(company,'&','and') company, balance
  FROM vendors
  WHERE outstanding_balance < 3000
  ]]>
  </xsql:query>

| Copyright © 2001, 2003 Oracle Corporation. All Rights Reserved. |
Home | Contents | Index