8 XSQL Pages Publishing Framework

What Can I Do with Oracle XSQL Pages?

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

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

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

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

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

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

Description of the illustration xsql5.gif

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

1. Receives a request to process an XSQL template

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

3. Returns this XML "datagram" to the requestor

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

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

• HTML for browser display

• Wireless Markup Language (WML) for wireless devices

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

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

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

• Arbitrary XML-based document formats

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

Where Can I Obtain Oracle XSQL Pages?

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

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

What Is Needed to Run XSQL Pages?

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

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

• Oracle XML SQL Utility (xsu12.jar)

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

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

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

• 
  

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

  
  

Install Your XSQLConfig.xml File in a Safe Directory

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

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

Disable Default Client Stylesheet Overrides

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

Be Alert for the Use of Substitution Parameters

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

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

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

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

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

Producing XML Datagrams from SQL Queries

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

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

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

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

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

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

Description of the illustration xsql1.gif

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

<?xml version="1.0"?>

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

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

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

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

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

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

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

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

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

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

Transforming XML Datagrams into an Alternative XML Format

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

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

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

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

Description of the illustration xsql2.gif

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

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

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

• <arrives> element.

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

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

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

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

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

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

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

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

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

• xsl:version="1.0"

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

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

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

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

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

Description of the illustration xsql3.gif

Transforming XML Datagrams into HTML for Display

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

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

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

You can see that by combining the power of:

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

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

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

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

Using XSQL Pages with Oracle JDeveloper

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

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

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

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

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

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

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

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

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

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

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

Setting the CLASSPATH Correctly in Your Production Environment

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

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

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

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

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

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

• oraclexsql.jar, the XSQL page processor

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

• xsu12.jar, the Oracle XML SQL utility

• classes12.jar, the Oracle JDBC driver

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

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

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

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

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

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

Setting Up the Connection Definitions

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

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

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

1. <username>

2. <password>

3. <dburl>, the JDBC connection string

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

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

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

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

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.

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.

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

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

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

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

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

bind-params = "string"

date-format = "string"

error-param = "string"

error-statement = "boolean"

fetch-size = "integer"

id-attribute = "string"

id-attribute-column = "string"

include-schema = "boolean"

max-rows = "integer"

null-indicator = "boolean"

row-element = "string"

rowset-element = "string"

skip-rows = "integer"

tag-case = "string"

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

commit = "boolean"

bind-params = "string"

error-param = "string"

error-statement = "boolean"

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

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

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

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

bind-params = "string"

error-param = "string"

error-statement = "boolean"

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

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

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

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

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

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

$ xsql DevOpenBugs.xsql dev=smuench prod=817

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

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

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

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

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

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

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

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

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

<example>
  <rowset/>
</example>

<xsql:include-request-params/>

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

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

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

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

<productid>12345</productid>

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

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

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

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

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

bind-params = "string"

error-param = "string"

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

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

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

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

name = "string"

names = "string string ..."

bind-params = "string"

error-param = "string"

ignore-empty-value = "boolean"

treat-list-as-array = "boolean"

iquote-array-values = "boolean"

xpath = "XPathExpression"

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

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

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

name = "string"

names = "string string ..."

bind-params = "string"

error-param = "string"

ignore-empty-value = "boolean"

only-if-unset = "boolean"

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

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

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

name = "string"

names = "string string ..."

bind-params = "string"

domain = "string"

error-param = "string"

ignore-empty-value = "boolean"

max-age = "integer"

only-if-unset = "boolean"

path = "string"

immediate = "boolean"

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

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

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

name = "string"

names = "string string ..."

bind-params = "string"

error-param = "string"

ignore-empty-value = "boolean"

Working with Array-Valued Parameters

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

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

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

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

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

Setting Array-Valued Page or Session Parameters from Strings

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

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

or similarly for session parameters:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE TYPE xsql_table_of_varchar AS TABLE OF VARCHAR2(2000);

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

Consider the following PL/SQL stored procedure:

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

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

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

This produces a resulting XML data page of:

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

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

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

<page someNames="aa,bb,cc" someValues="11,22,33">
  <xsql-error code="17074" action="xsql:query">
    <statement>
     select testTableFunction(?,?) as example from dual
    </statement>
    <message>
      invalid name pattern: SCOTT.XSQL_TABLE_OF_VARCHAR
    </message>
  </xsql-error>
</page>

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

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

This produces a result like:

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

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

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

Supplying Multi-Valued Parameters on the Command Line

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

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

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

Supplying Multi-Valued Parameters Programmatically with XSQLRequest

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

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

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

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

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

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

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

2. equals="stringValue"

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

3. not-equals="stringValue"

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

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

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

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

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

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

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

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

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

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

Optionally Setting an Error Parameter on Any Built-in Action

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

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

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

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

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

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

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

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

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

Aggregating Information Using <xsql:include-xsql>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

href = "string"

error-param = "string"

reparse = "boolean"

Including XMLType Query Results

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Handling Posted Information

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

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

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

• <xsql:insert-request>

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

• <xsql:update-request>

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

• <xsql:delete-request>

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

• <xsql:insert-param>

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

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

table = "string"

transform = "URL"

columns = "string"

commit = "boolean"

commit-batch-size = "integer"

date-format = "string"

error-param = "string"

table = "string"

key-columns = "string"

transform = "URL"

columns = "string"

commit = "boolean"

commit-batch-size = "integer"

date-format = "string"

error-param = "string"

table = "string"

key-columns = "string"

transform = "URL"

commit = "boolean"

commit-batch-size = "integer"

error-param = "string"

name = "string"

table = "string"

transform = "URL"

columns = "string"

commit = "boolean"

commit-batch-size = "integer"

date-format = "string"

error-param = "string"

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

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

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

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>

Setting Up the Demo Data

To set up the demo data do the following:

1. Change directory to the ./demo directory.

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

   GRANT EXECUTE ON ctx_ddl TO scott;
   
   

3. Connect to your database as SYSTEM/MANAGER and issue the command:

   GRANT QUERY REWRITE TO scott;
   
   

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

4. Connect to your database as SCOTT/TIGER.

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

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

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

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

Using a Custom XSQL Configuration File Name

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

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

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

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

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

Understanding Client Stylesheet-Override Options

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

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

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

• The literal value none

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

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

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

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

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

Controlling How Stylesheets Are Processed

Here are some points to consider:

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

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

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

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

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

type = "string"

href = "URL"

media = "string"

client = "boolean"

serializer = "string"

Using XSQL Configuration File to Tune Your Environment

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

XSQLConfig/servlet/output-buffer-size

XSQLConfig/servlet/suppress-mime-charset/media-type

XSQLConfig/processor/character-set-conversion/default-charset

XSQLConfig/processor/reload-connections-on-error

XSQLConfig/processor/default-fetch-size

XSQLConfig/processor/page-cache-size

XSQLConfig/processor/stylesheet-cache-size

XSQLConfig/processor/stylesheet-pool/initial

XSQLConfig/processor/stylesheet-pool/increment

XSQLConfig/processor/stylesheet-pool/timeout-seconds

XSQLConfig/processor/connection-pool/initial

XSQLConfig/processor/connection-pool/increment

XSQLConfig/processor/connection-pool/timeout-seconds

XSQLConfig/processor/connection-pool/dump-allowed

XSQLConfig/processor/connection-manager/factory

XSQLConfig/processor/owa/fetch-style

XSQLConfig/processor/timing/page

XSQLConfig/processor/timing/action

XSQLConfig/processor/logger/factory

XSQLConfig/processor/error-handler/class

XSQLConfig/processor/xml-parsing/preserve-whitespace

XSQLConfig/processor/security/stylesheet/defaults/allow-client-style

XSQLConfig/processor/security/stylesheet/trusted-hosts/host

XSQLConfig/http/proxyhost

XSQLConfig/http/proxyport

XSQLConfig/connectiondefs/connection

XSQLConfig/connectiondefs/connection/username

XSQLConfig/connectiondefs/connection/password

XSQLConfig/connectiondefs/connection/dburl

XSQLConfig/connectiondefs/connection/driver

XSQLConfig/connectiondefs/connection/autocommit

XSQLConfig/serializerdefs/serializer

XSQLConfig/serializerdefs/serializer/name

XSQLConfig/connectiondefs/connection/class

Using the FOP Serializer to Produce PDF Output

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

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

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


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

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

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

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

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

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

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

Using XSQL Page Processor Programmatically

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

• String containing a URL to the page

• URL object for the page

• In-memory XMLDocument

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

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

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

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

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

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

• Pass a set of parameters to the request

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

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

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

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

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

Writing Custom XSQL Action Handlers

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

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

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

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

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

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

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

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

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

<xsql:query>

<xsql:dml>

<xsql:set-stylesheet-param>

<xsql:insert-request>

<xsql:include-xml>

<xsql:include-request-params>

<xsql:include-posted-xml>

<xsql:include-xsql>

<xsql:include-owa>

<xsql:action>

<xsql:ref-cursor-function>

<xsql:include-param>

<xsql:if-param>

<xsql:set-session-param>

<xsql:set-page-param>

<xsql:set-cookie>

<xsql:insert-param>

<xsql:update-request>

<xsql:delete-request>

<xsql:if-param>

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

getActionElement

getActionElementContent

getPageRequest

getAttributeAllowingParam

appendSecondaryDocument

addResultElement

firstColumnOfFirstRow

bindVariableCount

handleBindVariables

reportErrorIncludingStatement

reportFatalError

reportMissingAttribute

reportStatus

requiredConnectionProvided

variableValue

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

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

Using Multi-Valued Parameters in Custom XSQL Actions

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

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

Writing Custom XSQL Serializers

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

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

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

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

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

   env.setContentType("text/html");
   
   

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

   env.setContentType("text/html;charset=Shift_JIS");
   
   

2. Call getWriter() or getOutputStream() — but not both! — on the XSQLPageRequest to get the appropriate PrintWriter or OutputStream respectively to use for serializing the content.

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

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

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

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

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

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

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

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

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

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

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

or

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

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

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

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

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

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

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

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

Using a Custom XSQL Connection Manager for JDBC Datasources

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

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

• oracle.xml.xsql.XSQLDatasourceConnectionManager

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

• oracle.xml.xsql.XSQLOracleDatasourceConnectionManager

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

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

• jdbc/scottDS

• java:comp/env/jdbc/MyDatasource

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

Writing Custom XSQL Connection Managers

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

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

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

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

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

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

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

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

Providing a Custom XSQLErrorHandler Implementation

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

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

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

For example, here is a sample implementation of XSQLErrorHandler:

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

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

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

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

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

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

You can use a combination of both approaches if needed.

Providing a Custom XSQL Logger Implementation

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

The XSQLLoggerFactory interface contains the single method:

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

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

The XSQLLogger interface looks like this:

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

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

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

Next, the factory implementation:

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

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

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

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

Formatting XSQL Action Handler Errors

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

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

<xsql-error action="xxx"> 

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

• A nested <message> element

• A <statement> element with the offending SQL statement

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

HTTP Parameters with Multibyte Names

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

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

CURSOR() Function in SQL Statements

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

Hints for Using the XSQL Servlet

This section lists XSQL Servlet hints.

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

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

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

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

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

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

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

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

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;

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

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

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

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

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

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

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

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

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

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