Oracle XSQL Pages and the XSQL Servlet

Release Notes

Contents

Note : Before installing XSQL Pages on a production server, please read the section below on Security Considerations for Production XSQL Pages System

As the Internet drives an explosive demand for flexible information exchange, more and more application developers need to put their business data to work over the Web. Developers require standards-based solutions to this problem and SQL, XML, and XSLT are the standards that can get the job done in practice.

SQL is the standard you are already familiar with for accessing appropriate views of business information in your production systems. XML provides an industry-standard, platform-neutral format for representing the results of SQL queries as "datagrams" for exchange, and XSLT defines the industry-standard way to transform XML "datagrams" into target XML, HTML, or Text formats as needed.

By combining the power of SQL, XML, and XSLT in the server with the ubiquitously available HTTP protocol for the transport mechanism you can:

• Receive web-based information requests from any client device on the Web,

• Query an appropriate logical "view" of business data needed by the request,

• Return the "datagram" in XML over the web to the requester, or optionally

• Transform the information flexibly into any XML, HTML, or text format they require.

Of course, Oracle 8i, the Oracle XML Developer's Kit, and the XML SQL Utility for Java provide all of the core technology needed by developers to implement this solution. However it is Oracle XSQL Pages that bring this capability to the "rest of us" by automating the use of these underlying XML technology components to solve the most common cases without programming.

Oracle XSQL Pages are templates that allow anyone familiar with SQL to declaratively:

• Assemble dynamic XML "datapages" based on one or more parametrized SQL queries, and

• Transform the "datapage" to produce a final result in any desired XML, HTML, or Text-based format using an associated XSLT Transformation.

The two key design goals of Oracle XSQL Pages are:

• Make simple things very simple and hard things possible

• Keep the "datapage" cleanly separate from the way that data will be rendered to the requester.

XSQL Pages are simple to build. Just use any text editor to create an XML file that includes <xsql:query> tags wherever you want to include XML-based SQL query results in the template. Associate an XSLT stylesheet to the page by including one extra line at the top of the file: an <?xml-stylesheet?> instruction. Save the file and request it through your browser to get immediate results. Since you can extend the set of actions that can be performed to assemble the "datapage" using the <xsql:action> element, it's possible to cleverly extend the basic simple model to handle harder jobs. Let's start by looking at a simple example of an XSQL Page.

For example, to serve a list of available flights today for any desired destination city from your enterprise database in response to a URL request like:

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

you might write an XSQL Page like:

<?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') Due
      FROM FlightSchedule
     WHERE TRUNC(ArrivalTime) = TRUNC(SYSDATE)
       AND Destination = ?
  ORDER BY ExpectedTime

</xsql:query>

To return the same information in HTML or some alternative XML format that might comply with a particular DTD you've been given, just associate an appropriate with <?xml-stylesheet?> like this:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="FlightList.xsl"?>
<xsql:query connection="demo" bind-params="City" xmlns:xsql="urn:oracle-xsql">

    SELECT Carrier, FlightNumber, Origin, TO_CHAR(ExpectedTime,'HH24:MI') Due
      FROM FlightSchedule
     WHERE TRUNC(ArrivalTime) = TRUNC(SYSDATE)
       AND Destination = ?
  ORDER BY ExpectedTime

</xsql:query>

Once you've built a set of XSQL Pages, you can "exercise" your templates by:

• Requesting the pages over the Web from browsers or client programs after installing the Oracle XSQL Servlet on your favorite Web Server,

• Using the Oracle XSQL Command Line Utility in batch programs,

• Calling the XSQLRequest.process() method from within any Java program

• <jsp:include> ing an XSQL Page into your JSP page, or <jsp:forward> ing to

These release notes explain in detail how to setup the Oracle XSQL Servlet and how to make use of all the time-saving features supported by XSQL Page templates.

Using XSQL Pages, the information you already have in your Oracle database, and the power of SQL, XML, and XSLT, you'll quickly discover that a powerful and flexible world of web data publishing is at your fingertips.

Release 10.0.0.0 adds several new features:

Many users have requested an easy way to work with parameters whose values are arrays of strings. The most common scenario where multi-valued parameters occur is when a user submits an HTML form containing multiple occurrences of input controls that share the same name.

• Working with Array-Valued Parameters

  In addition to the existing support for simple-string values, now 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-separated list of all non-null and non-empty strings in the array in the order that they appear in the array. For example, if you had a page like:

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

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

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

  
  

  Note : It is not supported to use a number inside the array brackets. That is, you can refer to productid or productid[], but not productid[2]. Also, as described above only the request parameters, page-private parameters, and session parameters can use string arrays. The <xsql:set-stylesheet-param> and <xsql:set-cookie> only support working with parameters as simple string values.

  
  

• 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-separated list and tokenized. The resulting string array value will contain these separate tokens. In the examples above, the names[] parameter would be the string array {"Tom", "Jane", "Joe"} and the dates[] parameter would be 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"/>

  would set 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-separated 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 final new 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 new quote-array-values="yes" attribute to have the comma-separated 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 were supported previously, you may now bind array-valued parameters by simply using the array-parameter name 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 PLSQL procedures. Array-valued parameters are bound as an 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);

  
  

  Note : 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 should 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 return.

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

  
  

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

  
  

  Since the array parameters are bound as nested table collection types, you can use the TABLE() operator in combination with the CAST() operator in SQL to treat the nested table bind variable value as a table of values to query against. This can be quite a powerful technique to use in subselect 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 EMP table.

  <page xmlns:xsql="urn:oracle-xsql" connection="demo">
    <xsql:set-page-param name="someEmployees[]" value="7369,7839"/>
    <xsql:query bind-params="someEmployees[]">
      select ename, sal
        from emp
       where empno in (
          select * from TABLE(CAST( ? as xsql_table_of_varchar))
       )
     </xsql:query>
  </page>

  This produces a result like:

  <page>
    <ROWSET>
      <ROW num="1">
        <ENAME>SMITH</ENAME>
        <SAL>800</SAL>
      </ROW>
      <ROW num="2">
        <ENAME>KING</ENAME>
        <SAL>5000</SAL>
      </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 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 OCI8 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 OCI8 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 part of the page processing request.

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

• Using Multi-Valued Parameters in Custom XSQL Actions

  The base class for custom XSQL actions, oracle.xml.xsql.XSQLActionHandlerImpl has been enhanced to support working with array-named lexical parameter substitution and array-named bind variables, so if your custom actions are using methods like getAttributeAllowingParam(), getActionElementContent(), or handleBindVariables() methods from this base class, you pickup this new functionality for free in your custom actions.

  A new method getParameterValues() is added to the XSQLPageRequest interface to explicitly get a parameter value as a String[], and 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.

• New Behavior of <xsql:include-param> for Multi-Valued Parameters

  The <xsql:include-param> method is used to include the value of any parameter into the XML data page that your XSLT stylesheet will "see" for processing. When used with a simple string-valued parameter, an action like:

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

  includes a fragment of XML like this into your data page:

  <productid>1234</productid>

  If you use the new array-name syntax to indicate that you want to treat the parameter as an array-valued parameter, <xsql:include-param> will add all array values to your data page using an XML fragment that looks like this:

  <productid>
    <value>1234</value>
    <value>4567</value>
    <value>890</value>  
  </productid>

  assuming the productid parameter was array-valued with values {"1234","4567","890"} and that you used the array-bracket notation in the action like this:

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

• Conditionally Execute Actions or Include Content with xsql:if-param

  The new <xsql:if-param> action allows you to conditionally include the elements and/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 any nested actions are not 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) should be tested:

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

     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.

  2. equals=" stringValue"

     This tests whether the named parameter equals the string value provided. 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. 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, it tests whether no element 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.

  All of the attributes of the <xsql:if-param> action can contain lexical substitution parameter expressions (e.g. {@ paramName}) if needed.

  Any XML content or XSQL action element can be nested inside an <xsql:if-param> , including other <xsql:if-param> elements if needed.

  
  

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

  
  

• New Commit="No" Flag on Actions That Performed an Implicit Commit

  The <xsql:delete-request> , <xsql:insert-request> , <xsql:insert-request> , and <xsql:insert-parameter> action elements each takes a new optional commit attribute to control whether the action does an implicit commit or not. For backward compatibility, if not specified, these actions perform an implicit commit. To avoid performing this implicit commit, you may now specify commit="no" on any of these actions.

• Optionally Set an Error Param 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 could fail with a database exception. 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 above, this would mean 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 tempate, 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 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 andyour custom actions call reportMissingAttribute(), reportError(), or reportErrorIncludingStatement() to report non-fatal action errors, then they will automatically pickup this new feature as well.

• Use Your Servlet Container's DataSource Implementation

  As an alternative to defining your named connections in the XSQLConfig.xml file, you may now alternatively use the datasources available through your servlet container's implementation of JDBC datasources.

  Two new XSQLConnectionManager implementations are provided:

  1. oracle.xml.xsql.XSQLDatasourceConnectionManager

     Consider using this alternative connection manager implementation 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> .

  2. oracle.xml.xsql.XSQLOracleDatasourceConnectionManager

     Consider using this alternative connection manager implementation 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 Oracle9iAS 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 should be the JNDI name used to lookup your desired datasource. For example, the value of the connection attribute might look something like: "jdbc/scottDS".

• Provide Custom XSQLErrorHandler Implementation

  Many customers have asked for a programmatic way to control how errors are reported to be able to customize the treatment of how page processor errors (like a connection's being unavailable) are reported to users. A new interface is introduced in this release oracle.xml.xsql.XSQLErrorHandler that allows developers to achieve 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, etc.

  For example, here is a sample implementation of XSQLErrorHandler:

  package test;
  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 XSQLConfig.xml file by providing the fully-qualified classname of your error writer class as the value of the XSQLConfig.xml file entry /XSQLConfig/processor/error-handler/class.

     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.

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

     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.

     Or you can use a combination of both approaches.

• Provide Custom XSQLLogger Implementation

  Customers have asked for a way to log XSQL page requests. Two new interfaces are introduced in this release oracle.xml.xsql.XSQLLoggerFactory and oracle.xml.xsql.XSQLLogger that allow developers to achieve this.

  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 should be 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 using it to log the start and end of each page request by invoking the logRequestStart() and logRequestEnd() methods on it.

  Here is the interface for XSQLLogger:

  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 conent to the /XSQLConfig/processor/logger/factory element like this:

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

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

• Override the Default Name of of the XSQLConfig.xml file

  Customers have requested a way to override the default XSQLConfig.xml file name so that they can easily provide different configuration files for test and production environments, for example. This releases introduces two ways to override the name.

  1. By setting the Java System property xsql.config

     The simplest way to accomplish this is to specify a Java VM command-line flag like -Dxsql.config= MyConfigFile.xml

  2. By defining a servlet initialization parameter xsql.config

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

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

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

  
  

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

  
  

• Support for Apache FOP 0.20.3

  If you need to render PDF output from XSQL pages, this release supports working with the 0.20.3 release candidate of Apache FOP. The source code for the FOP Serializer 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);
      }
    }
  }

  
  

  Note : Using Apache FOP requires adding the following additional JAR files to your XSQL Pages runtime CLASSPATH to function correctly: fop.jar (Apache FOP Rendering Engine) avalon-framework-4.0.jar (Base Apache Avalon Framework API's) logkit-1.0.jar (Apache Avalon Framework's LogKit subcomponent) batik.jar (Apache Batik SVG Rendering Engine)

  
  

• Set Preserve Whitespace Config Option

  It is now possible to control whether or not the XSQL Page Processor uses the Oracle XML Parser to parse XSQL page templates and XSLT stylesheets with whitespace preserving mode. The default has always been to preserve whitespace, but it can be slightly faster to parse these documents with whitespace-preserving mode off. For backward compatibility, the default is to preserve whitespace, but to turn off whitespace-preserving mode set the new XSQLConfig.xml file entry /XSQLConfig/processor/xml-parsing/preserve-whitespace to the value false.

• Tags present in stylesheet name are not quoted in the error message

  A potential security issue was reported whereby a malicious user could execute JavaScript in the browser by passing in a value like <script>alert('Bingo.');</script> for the value of the xml-stylesheet parameter. When the XSQL Servlet renders the error page to complain that a stylesheet named "<script>alert('Bingo.');</script>" could not be found, it was not quoting the tags so the <script> tag would be interpreted by the browser. Now any tag maliciously introduced this way are quoted so that they appear as literal text in the error page instead of as an executable script tag. The security issue was minimal since users could always disable client styling on any or all pages to completely ignore the value of the xml-stylesheet parameter, but with this fix, even a page that allows client styling is no longer a victim to this vulnerability.

• <xsql:include-xml> Only Works with Clobs using the Oracle JDBC Driver

  Now any JDBC driver that supports java.sql.Clob can be used to select a Clob-valued XML document for inclusion in the page.

As with any software running on a server, care must be taken to avoid risks of exposing sensitive information to malicious users. This section describes best practice security techniques for using the Oracle XSQL Servlet.

• Install Your XSQLConfig.xml File in a Safe Directory

  The XSQLConfig.xml configuration file contains sensitive database username/password information that must be kept secure on the server. This file should not reside in any directory that is mapped to a virtual path of your Web server, nor in any subdirectory thereof. Its read permissions 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 Overrideability When Your Pages Go Production

  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 if 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 of the Use of Subsitution Parameters

  With power comes responsibility. Any product, like Oracle Reports and XSQL Pages among others, that supports the use of lexical substitution variables in a SQL query can give a developer enough rope to hang himself. 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 should 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 if you deploy a similar kind of page to your product system that 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/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 game in town. Otherwise, true bind variabled are recommended so that any attempt to pass an invalid value will generate an error instead of producing an unexpected result.

This release includes an on-line Help System (built using XSQL Pages, of course) giving helpful information about the syntax and options for each XSQL Action Element. In additional helpful examples are provided illustrating each action. To access the help system, after successfully installing the XSQL Servlet, browse the URL:

http://yourmachine/xsql/index.html

A number of new built-in XSQL Action Elements have been added in this release. The following table documents the new arrivals...

XSQL provides a single way to refer to values that can be specified as:

• HTTP Request Parameters

• HTTP Cookies

• HTTP Session Variables

• Local XSQL Page Parameters

When you reference an parameter like myParam inside the content of an XSQL Action Element, like:

<xsql:query>
   select name from users where userid = {@myParam}
</xsql:query>

or in the attribute value of an XSQL Action Element, like:

<xsql:query max-rows="{@myParam}">
   :
</xsql:query>

the XSQL Page Processor determines the value of the parameter by using the following logic.

If the request is being processed by the XSQL Servlet, then check in the following order if myParam is the name of...

1. An XSQL local page parameter

2. An HTTP Cookie

3. An HTTP Session Variable

4. An HTTP Request Parameter

If the request is being processed by a non-Servlet request method using XSQLCommandLine or the XSQLRequest class, then check in the following order if myParam is the name of...

1. An XSQL local page parameter

2. An XSQL Request parameter

   Provided on the command-line or passed into the XSQLRequest.process() method.

In either case, if none of the attempts produces a matching parameter value, the XSQL Page Processor looks for a "fallback" (a.k.a "default") value for myParam by searching the current Action Element and its ancestor elements in order to find an XML attribute of the same name as the parameter. If such an attribute is found, it's value is used as the value of myParam.

This release contains the following known issues:

• HTTP parameters with multibyte names (e.g. a parameter whose name is in Kanji) are properly handled when they are inserted into your XSQL page using <xsql:include-request-params> , but 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. The workaround is to use a non-multibyte parameter name. The parameter can still properly have a multibyte value which will be handled correctly.

• Using the CURSOR() function in SQL statements can cause an "Exhausted ResultSet" error if CURSOR() statements are nested and outer CURSOR() functions evaluate to an empty rowset.

• When using the Apache JServ servlet engine, the use of relative stylesheet and XSQL page references may not produce the results you expect if you have an elaborate virtual path mapping. Since the Apache JServ engine does not properly implement the Servlet API method getRealPath() that the XSQL Servlet uses to resolve a path relative to the web server's virtual file system, a workaround is implemented for JServ to make relative path references work for the most common cases.

The XSQL Servlet is designed to run on any Java VM, using any JDBC driver, against any database. In practice, we are able to test it against only the most popular configurations of these. In this section we document the supported configurations that have been tested in the Oracle labs.

The XSQL Pages and XSQL Servlet have been tested using:

• JDK 1.1.8

• JDK 1.2.2

• JDK 1.3

These are the only three JDK versions that we know work correctly.

Note : Numerous users have reported problems using XSQL Pages and the XSQL Servlet with JDK 1.1.7 which suffers problems in its character set conversion routines for UTF-8 that make it unusable for processing XSQL Pages.

This XSQL Servlet has been tested with the following servlet engines:

• Oracle9iAS Apache/JServ Servlet Engine

• Oracle9iAS OC4J Servlet Engine

• Allaire JRun 2.3.3 and 3.0.0

• Apache 1.3.9 with JServ 1.0 and 1.1

• Apache 1.3.9 with Tomcat 3.1 or 3.2 Servlet Engine

• Apache Tomcat 3.1 or 3.2 Web Server + Servlet Engine

• Caucho Resin 1.1

• Java Web Server 2.0

• Weblogic 5.1 Web Server

• NewAtlanta ServletExec 2.2 and 3.0 for IIS/PWS 4.0

• Oracle8i Lite Web-to-Go Server

• Oracle8i 8.1.7 Oracle Servlet Engine

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

JavaServer Pages can use <jsp:forward> and/or <jsp:include> to collaborate with XSQL Pages as part of an application. The following JSP platforms have been tested:

• Oracle9iAS Apache/JServ Servlet Engine

• Oracle9iAS OC4J Servlet Engine

• Apache 1.3.9 with Tomcat 3.1 or 3.2 Servlet Engine

• Apache Tomcat 3.1 or 3.2 Web Server + Tomcat 3.1 or 3.2 Servlet Engine

• Caucho Resin 1.1 (Built-in JSP 1.0 Support)

• NewAtlanta ServletExec 2.2 and 3.0 for IIS/PWS 4.0 (Built-in JSP 1.0 Support)

• Oracle8i Lite Web-to-Go Server with Oracle JSP 1.0

• Oracle8i 8.1.7 Oracle Servlet Engine

• Any Servlet Engine with Servlet API 2.1+ and Oracle JSP 1.0

In general, it should work with any servlet engine supporting the Servlet 2.1 Specification or higher, and the Oracle JSP 1.0 reference implementation or functional equivalent from another vendor.

The Oracle XSQL Page processor has been designed to exploit the maximum set of features against the Oracle JDBC drivers, but gracefully degrade to work against any database with a reasonable JDBC driver. While numerous users have reported successfully using XSQL Pages with many other JDBC drivers, the ones that we have tested in-house are:

• Oracle8i 8.1.5 Driver for JDBC 1. x

• Oracle8i 8.1.6 Driver for JDBC 1. x

• Oracle8i 8.1.7 Driver for JDBC 1. x

• Oracle8i Lite 4.0 Driver for JDBC 1.x

• Oracle8i 8.1.6 Driver for JDBC 2.0

• Oracle8i 8.1.7 Driver for JDBC 2.0

• Oracle9i 9.0.1 Driver for JDBC 2.0

Oracle XSQL Pages 10.0.0.0 depends on:

• Oracle XML Parser V2, version 9.0.1 or higher

• Oracle XML SQL Utilities for Java, version 2.0.1 or higher

• A Web Server that supports Java Servlets

• A JDBC driver, like Oracle JDBC or Oracle8i Lite JDBC

For your convenience, all of these dependent libraries are included with the XSQL Servlet distribution when you download it.

In addition to the Oracle XSQL Servlet archive itself in .\lib\oraclexsql.jar, and the optional .\lib\xsqlserializers.jar archive for FOP/PDF integration, the Oracle Technet (OTN) distribution of the XDK includes the following Oracle XDK components on which Oracle XSQL Pages depends:

• Oracle XML Parser V2, .\lib\xmlparserv2.jar

• Oracle XML SQL Utility, .\lib\xsu12.jar

You can download the XSQL Servlet as part of the Oracle XDK for Java download by:

1. Visiting http://otn.oracle.com/tech/xml

2. Clicking on the 'Software' icon at the top of the page:

3. Logging in with your OTN username and password (registration is free if you do not already have an account).

4. Selecting whether you want the NT or Unix download (both contain the same files)

5. Acknowledging the licensing agreement and download survey

6. Clicking on appropriate *.tar.gz or *.zip file.

To extract the contents of the XDK distribution, do the following:

1. Choose a directory under which you would like the .\xdk directory and subdirectories to go. (e.g. C:\)

2. Change directory to C:\, then extract the XSQL downloaded archive file there. For example:

   tar xvfz xdk_xxx.tar.gz

   on Unix, or on Windows:

   pkzip25 -extract -directories xdk_xxx.zip

   using the pkzip25 command-line tool or the WinZip visual archive extraction tool.

The demos are set up to use the SCOTT schema on a database on your local machine (i.e. the machine where the web server is running). If you are running a local database and have a SCOTT account whose password is TIGER, then you are all set. Otherwise, you need to edit the .\xdk\admin\XSQLConfig.xml file to correspond to your appropriate values for username, password, dburl, and driver values for the connection named "demo".

<?xml version="1.0" ?>
<XSQLConfig>
     :
  <connectiondefs>
    <connection name="demo">
      <username>scott</username>
      <password>tiger</password>
      <dburl>jdbc:oracle:thin:@localhost:1521:ORCL</dburl>
      <driver>oracle.jdbc.OracleDriver</driver>
    </connection>
    <connection name="lite">
      <username>system</username>
      <password>manager</password>
      <dburl>jdbc:Polite:POlite</dburl>
      <driver>oracle.lite.poljdbc.POLJDBCDriver</driver>
    </connection>
  </connectiondefs>
      :
</XSQLConfig>

Unix users and any user wanting to install the XSQL Servlet on other web servers should continue with the instructions below depending on the web server you're trying to use. In every case, there are 3 basic steps:

1. Include the list of XSQL Java archives:

   • xsu12.jar- Oracle XML SQL Utility

   • xmlparserv2.jar- Oracle XML Parser for Java V2

   • oraclexsql.jar- Oracle XSQL Pages

   • xsqlserializers.jar- Oracle XSQL Serializers for FOP/PDF Integration

   • ojdbc5.jar- Oracle JDBC Driver

     or the JAR file for the JDBC driver you will be using instead

   as well as the directory where XSQLConfig.xml resides (by default ./xdk/admin) in the server CLASSPATH.

   
   

   Note : In a production system, make sure your XSQLConfig.xml file does not reside under a directory that is broweable from your web server. See Security Considerations for Production XSQL Pages System for more information.

   
   

2. Map the .xsql file extension to the oracle.xml.xsql.XSQLServlet servlet class

3. Map a virtual directory /xsql to the directory where you extracted the XSQL files (to access the on-line help and demos)

You can install the XSQL Servlet in one of two ways:

• You can deploy the xsqldemos.ear file to the OC4J server, or

• You can install the XSQL Servlet as a global application to handle *.xsql files globally, similar to how *.jsp files are globally recognized and handled by the JSP-Runner servlet.

To install xsqldemos.ear...

• Change directory to the ./j2ee/home directory.

• Deploy the ear file with the command (all on one line, replacing admin and welcome with your username and password for OC4J administration):

  java -jar admin.jar ormi://yourserver/ admin welcome
    -deploy -file XDKHome/xdk/demo/java/xsql/xsqldemos.ear -deploymentName xsqldemos

• Bind the web application with the command (all on one line, replace admin and welcome as above, using default-web-site or http-web-site as appropriate):

  java -jar admin.jar ormi://yourserver/ admin welcome
    -bindWebApp xsqldemos xsqldemos http-web-site /xsql

The demos can then be run by browsing:

http:// yourserver: port/xsql/index.html

To install XSQL Servlet in the Oracle9iAS OC4J servlet container as a global application, do the following instead. Assuming your OC4J installation home is C:\j2ee\home, and that you've extracted the XDK distribution into the C:\xdk902 directory, here are the setup steps:

1. Verify that the following jar files are already in your C:\j2ee\home\lib directory (they should come preinstalled):

   • xmlparserv2.jar- Oracle XML Parser for Java V2

   • ojdbc5.jar- Oracle JDBC Driver

2. Copy the following additional jar files from C:\xdk902\lib to C:\j2ee\home\lib...

   • xsu12.jar- Oracle XML SQL Utility

   • oraclexsql.jar- Oracle XSQL Pages

   • xsqlserializers.jar- Oracle XSQL Serializers for FOP/PDF Integration

3. Copy the C:\xdk\admin\XSQLConfig.xml configuration file to the C:\j2ee\home\default-web-app\WEB-INF\classes directory.

4. Edit the C:\j2ee\home\config\global-web-application.xml server configuration file to add a <servlet> and <servlet-mapping> entry as child elements of the <web-app> element as follows:

   <orion-web-app ...etc... >
     :
    etc
     :
     <web-app>
       <servlet>
         <servlet-name>xsql</servlet-name>
         <servlet-class>oracle.xml.xsql.XSQLServlet</servlet-class>
       </servlet>
       <servlet-mapping>
         <servlet-name>xsql</servlet-name>
         <url-pattern>/*.xsql</url-pattern>
       </servlet-mapping>
        :
       etc
        :
     </web-app>
   </web-app>

5. At this point, you can refer to any XSQL page in any virtual path and it will be processed by the XSQL Servlet. If you want to try the XSQL built-in samples, demos, and online help then you need to perform the following additional step to map a virtual path of /xsql/ to the C:\xdk\demo\java\xsql directory.

   Edit the c:\j2ee\home\application-deployments\default\defaultWebApp\orion-web.xml file to add the following <virtual-directory> entry:

   <orion-web-app ...etc...>
       :
      etc
       :
      <virtual-directory
      virtual-path="/xsql"
      real-path="/c:/xdk/xdk/demo/java/xsql/" />
       :
      etc
       :
   </orion-web-app>
   

Then, you can browse the demos using the URL http://yoursever:yourport/xsql/index.html

Note : In a production system, make sure your XSQLConfig.xml file does not reside under a directory that is broweable from your web server. See Security Considerations for Production XSQL Pages System for more information.

Oracle IAS release 1.0 and beyond comes pre-configured to run XSQL Servlet. By default it's Apache JServ servlet engine contains all of the wrapper.classpath entries in jserv.conf to include the necessary Java archives to run XSQL. The XSQLConfig.xml file lives in the ./xdk/admin subdirectory of the IAS installation home.

Note : In a production system, make sure your XSQLConfig.xml file does not reside under a directory that is broweable from your web server. See Security Considerations for Production XSQL Pages System for more information.

1. Setup the Server CLASSPATH Correctly for the XSQL Servlet

   This is done by starting the JRun Administrator, clicking on the General tab, and clicking on the Java subtab as shown below.

   Append the list of JAR files and directory that need to be in the server CLASSPATH for the XSQL Servlet to the existing value in the Java Classpath field. Assuming you installed into C:\, this list looks like:

   • To use the Oracle JDBC 1.x Driver, the list looks like:

     C:\xsql\lib\xsu111.jar;
     C:\xsql\lib\xmlparserv2.jar;
     directory_where_JDBC_Driver_resides\classes111.zip;
     C:\xsql\lib\oraclexsql.jar;
     directory_where_XSQLConfig.xml_resides
                       

     To use Apache FOP for PDF Generation, you need to add:

     C:\xsql\lib\xsqlserializers.jar;
     FOPHOME/build/fop.jar;
     FOPHOME/lib/batik.jar;
     FOPHOME/lib/logkit-1.0.jar;
     FOPHOME/lib/avalon-framework-4.0.jar

   • To use the Oracle JDBC 2.0 Driver, the list looks like:

     C:\xdk902\lib\xsu12.jar;
     C:\xdk902\lib\xmlparserv2.jar;
     directory_where_JDBC_Driver_resides\ojdbc5.jar;
     C:\xdk902\lib\oraclexsql.jar;
     directory_where_XSQLConfig.xml_resides
                       

2. Map the .xsql file extension to the XSQL Servlet

   To do this, select the Services tab in the JRun Administrator and select the appropriate "JRun Servlet Engine for XXX" entry for the Servlet Engine that corresponds to the web server that you are using. In the example below, we'll show configuring the Servlet Engine for the (built-in) JRun Web Server (JWS).

   Then click the Service Config button...

   On the Service Config screen, select the Mappings tab.

   Click the Add button and make an entry for the *.xsql extension, indicating the name of the servlet to invoke of oracle.xml.xsql.XSQLServlet as shown above. Then click Save to save the changes, and Close to dismiss the dialog.

3. Map an /xsql/ virtual directory

   In this step, we want to map the virtual path /xsql/ to C:\xdk902\xdk\demo\java\xsql\ (or wherever you installed the XSQL Servlet files).

   If you are using JRun together with another web server like Apache, IIS, or others, the virtual directory mapping needs to be done using the web server configuration file/utility. If you are using the JRun Web Server, then you can configure this virtual path mapping from the JRun Adminstrator. To do this, select the "jws" service and click on Service Config.

   Click on the Path Settings tab on the Service Config dialog, and click the Add button as show below.

   Make an entry for a virtual path of /xsql/ (trailing slash important!) that maps to a Real Path of C:\xdk902\xdk\demo\java\xsql\ (trailing slash important!), or the appropriate directory into which you installed the XSQL Servlet files. Click Save to save the changes, then Close to dismiss the dialog.

Restart the JRun server and browse the URL:

http://localhost:8000/xsql/index.html

1. Setup the Server CLASSPATH Correctly for the XSQL Servlet

   This is done by editing the JServ configuration file named jserv.properties. Assuming you installed the XSQL Servlet files into C:\, you need to add the following entries:

   • To use the Oracle JDBC 1.x Driver, the entries look like:

     # Oracle XML SQL Utility (XSU)
     wrapper.classpath=C:\xdk902\lib\xsu111.jar
     # Oracle XSQL Servlet
     wrapper.classpath=C:\xdk902\lib\oraclexsql.jar
     # Oracle JDBC (8.1.6) -- JDBC 1.x driver
     wrapper.classpath=directory_where_JDBC_Driver_resides\classes111.zip
     # Oracle XML Parser V2 (with XSLT Engine)
     wrapper.classpath=C:\xdk902\lib\xmlparserv2.jar
     # XSQLConfig.xml File location
     wrapper.classpath=directory_where_XSQLConfig.xml_resides
     # FOR Apache FOP Generation, Add
     # wrapper.classpath=C:\xdk902\lib\xsqlserializers.jar
     # wrapper.classpath=FOPHOME/build/fop.jar
     # wrapper.classpath=FOPHOME/lib/batik.jar
     # wrapper.classpath=FOPHOME/lib/logkit-1.0.jar
     # wrapper.classpath=FOPHOME/lib/avalon-framework-4.0.jar

   • To use the Oracle JDBC 2.0 Driver, the list looks like:

     # Oracle XML SQL Utility (XSU)
     wrapper.classpath=C:\xdk902\lib\xsu12.jar
     # Oracle XSQL Servlet
     wrapper.classpath=C:\xdk902\lib\oraclexsql.jar
     # Oracle JDBC (8.1.6) -- JDBC 2.0 driver
     wrapper.classpath=directory_where_JDBC_Driver_resides\ojdbc5.jar
     # Oracle XML Parser V2 (with XSLT Engine)
     wrapper.classpath=C:\xdk902\lib\xmlparserv2.jar
     # XSQLConfig.xml File location
     wrapper.classpath=directory_where_XSQLConfig.xml_resides
     # FOR Apache FOP Generation, Add
     # wrapper.classpath=C:\xdk902\lib\xsqlserializers.jar
     # wrapper.classpath=FOPHOME/build/fop.jar
     # wrapper.classpath=FOPHOME/lib/batik.jar
     # wrapper.classpath=FOPHOME/lib/logkit-1.0.jar
     # wrapper.classpath=FOPHOME/lib/avalon-framework-4.0.jar
     

2. Map the .xsql file extension to the XSQL Servlet

   To do this, you need to edit the JServ configuration file named jserv.conf (in JServ 1.0 this was named mod_jserv.conf on some platforms). Add the following line:

   # Executes a servlet passing filename with proper extension in PATH_TRANSLATED 
   # property of servlet request.
   # Syntax: ApJServAction [extension] [servlet-uri]
   # Defaults: NONE
   
   ApJServAction .xsql /servlets/oracle.xml.xsql.XSQLServlet

3. Map an /xsql/ virtual directory

   In this step, we want to map the virtual path /xsql/ to C:\xdk902\xdk\demo\java\xsql\ (or wherever you installed the XSQL Servlet files). To do this, you need to edit the Apache configuration file named httpd.conf and add the following line:

   Alias /xsql/ "C:\xdk902\xdk\demo\java\xsql\"

Restart the Apache server and browse the URL:

http://localhost/xsql/index.html

1. Setup the Server CLASSPATH Correctly for the XSQL Servlet

   This is done by editing the Tomcat startup script named tomcat.bat in ./jakarta-tomcat/bin and adding five lines to append the appropriate entries onto the system CLASSPATH before the Tomcat server is started as shown below:

   • To use the Oracle JDBC 1.x Driver, the entries look like:

     rem Set up the CLASSPATH that we need
     
     set cp=%CLASSPATH%
     
     set CLASSPATH=.
     set CLASSPATH=%TOMCAT_HOME%\classes
     set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\webserver.jar
     set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\jasper.jar
     set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\xml.jar
     set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\servlet.jar
     set CLASSPATH=%CLASSPATH%;%JAVA_HOME%\lib\tools.jar
     
     REM Added for Oracle XSQL Servlet
     REM -----------------------------
     set CLASSPATH=%CLASSPATH%;C:\xdk902\lib\xsu111.jar
     set CLASSPATH=%CLASSPATH%;C:\xdk902\lib\oraclexsql.jar
     set CLASSPATH=%CLASSPATH%;C:\xdk902\lib\xmlparserv2.jar
     set CLASSPATH=%CLASSPATH%;directory_where_JDBC_Driver_resides\classes111.zip
     set CLASSPATH=%CLASSPATH%;directory_where_XSQLConfig.xml_resides
     REM FOR Apache FOP Generation, Add
     REM set CLASSPATH=%CLASSPATH%;C:\xdk902\lib\xsqlserializers.jar
     REM set CLASSPATH=%CLASSPATH%;FOPHOME/fop.jar
     REM set CLASSPATH=%CLASSPATH%;FOPHOME/lib/batik.jar

   • To use the Oracle JDBC 2.0 Driver, the list looks like:

     rem Set up the CLASSPATH that we need
     
     set cp=%CLASSPATH%
     
     set CLASSPATH=.
     set CLASSPATH=%TOMCAT_HOME%\classes
     set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\webserver.jar
     set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\jasper.jar
     set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\xml.jar
     set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\servlet.jar
     set CLASSPATH=%CLASSPATH%;%JAVA_HOME%\lib\tools.jar
     
     REM Added for Oracle XSQL Servlet
     REM -----------------------------
     set CLASSPATH=%CLASSPATH%;C:\xdk902\lib\xsu12.jar
     set CLASSPATH=%CLASSPATH%;C:\xdk902\lib\oraclexsql.jar
     set CLASSPATH=%CLASSPATH%;C:\xdk902\lib\xmlparserv2.jar
     set CLASSPATH=%CLASSPATH%;directory_where_JDBC_Driver_resides\ojdbc5.jar
     set CLASSPATH=%CLASSPATH%;directory_where_XSQLConfig.xml_resides
     REM FOR Apache FOP Generation, Add
     REM set CLASSPATH=%CLASSPATH%;C:\xdk902\lib\xsqlserializers.jar
     REM set CLASSPATH=%CLASSPATH%;FOPHOME/build/fop.jar
     REM set CLASSPATH=%CLASSPATH%;FOPHOME/lib/batik.jar
     REM set CLASSPATH=%CLASSPATH%;FOPHOME/lib/logkit-1.0.jar
     REM set CLASSPATH=%CLASSPATH%;FOPHOME/lib/avalon-framework-4.0.jar

2. Map the .xsql file extension to the XSQL Servlet

   Tomcat supports creating any number of configuration "contexts" to better organize the web applications your site needs to support. Each context is mapped to a virtual directory path, and has its own separate servlet configuration information. XSQL Servlet comes with a pre-configured context file to make XSQL Servlet setup easier.

   By default, Tomcat 3.1 and 3.2 come pre-configured with the following contexts (defined by <Context> entries in the ./jakarta-tomcat/conf/server.xml file)...

   • The root context

   • /examples

   • /test

   • /admin

   We could install XSQL Servlet into one of these, but for simplicity we'll create a new context just for the XSQL Servlet that maps to the directory where you installed the XSQL Servlet distribution.

   Edit the ./jakarta-tomcat/conf/server.xml file to add the following <Context> entry with a path="/xsql"...

   <Context path="/test" docBase="webapps/test" debug="0" reloadable="true" /> 
   
   <!--
    |  Define a Servlet context for the XSQL Servlet  
    |  
    |  The XSQL Servlet ships with a .\WEB-INF directory
    |  with its web.xml file pre-configured for C:\xdk902\xdk\demo\java\xsql
    |  installation.
    +-->
   <Context path="/xsql" docBase="C:\xdk902\xdk\demo\java\xsql"/>
   

   Note that the docBase="C:/xsql" points to the physical directory where you installed the XSQL Servlet distribution. You then need to create a WEB-INF subdirectory directory in the C:\xdk902\xdk\demo\java\xsql and save into it the following ./WEB-INF/web.xml file:

   <?xml version = '1.0' encoding = 'UTF-8'?>
   <!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN"
                            "http://java.sun.com/j2ee/dtds/web-app_2_2.dtd">
   <web-app>
      <servlet>
         <servlet-name>oracle-xsql-servlet</servlet-name>
         <servlet-class>oracle.xml.xsql.XSQLServlet</servlet-class>
      </servlet>
       <servlet-mapping>
         <servlet-name>oracle-xsql-servlet</servlet-name>
         <url-pattern> *.xsql </url-pattern>
      </servlet-mapping>
   </web-app>

   
   

   Note : To add the XSQL Servlet to an existing context, add the servlet and servlet-mapping entries that you find in web.xml file above into the web.xml file for the context in question.

   
   

3. Map an /xsql/ virtual directory

   This is already achieved by creating the /xsql context above.

Restart the Tomcat server and browse the URL:

http://localhost:8080/xsql/index.html

Note : If you use Tomcat with an XML Parser (like the Sun Crimson Parser) that only supports DOM Leve 1 interfaces, then you must edit tomcat.bat to insure that the Oracle XML Parser's archive xmlparser.jar comes before the DOM Level 1 parser's archive in the classpath. For example, you could edit tomcat.bat to add the following line: REM NEED TO PUT xmlparserv2.jar FIRST before parser.jar set CP=C:\xdk902\lib\xmlparserv2.jar;%CP% just before the line: echo Using CLASSPATH: %CP% echo. set CLASSPATH=%CP%

1. Setup the Server CLASSPATH Correctly for the XSQL Servlet

   This is done by browsing the url http://localhost/servlet/admin after starting the IIS Server, and clicking the VM Settings link under "Advanced" in the sidebar.

   Add the four archives and one directory as shown above, by adding them one at a time and clicking the Submit button after each new entry.

2. Map the .xsql file extension to the XSQL Servlet

   Click on Configure under the "Servlets" heading in the sidebar to browse the form where you register servlets. Enter a Servlet Name of oraclexsql and a Servlet Class of oracle.xml.xsql.XSQLServlet into the blank form at the top and click Submit. It should then look like the picture below:

   Then, click on Aliases under "Servlets" in the sidebar.

   Add an entry as shown below mapping *.xsql to the servlet "nickname" of oraclexsql you defined above.

3. Map an /xsql/ virtual directory

   Use the IIS Admin console to create an /xsql virtual directory and map it to C:\xdk902\xdk\demo\java\xsql as shown here:

Restart the IIS server and browse the URL:

http://localhost/xsql/index.html

To setup the data for the demos do the following:

1. Change directory to the .\xsql\demo directory on your machine.

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

   GRANT EXECUTE ON CTX_DDL TO SCOTT;

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

   GRANT QUERY REWRITE TO SCOTT;

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

4. Connect to your database as SCOTT/TIGER.

5. Run the script install.sql in the ./xsql/demo directory. This script will, in turn, run all the SQL scripts for all the demos.

6. Change directory to the doyouxml subdirectory, and run the command imp scott/tiger file=doyouxml.dmp to import some sample data for the "Do You XML? Site" demo.

Note : To properly experience the Scalable Vector Graphics demonstration, you need to install an SVG plugin into your browser like the Adobe SVG Plugin.

In addition to "flat" database query results, using the techniques described in this section you can easily produce XML from database query with nested structure by selecting information from structured columns in your XSQL page. Structured columns can be one of three types:

1. Strongly Typed, User-Defined Object

2. Strongly Typed, User-Defined Collection

3. Untyped Collection based on a SQL statement

Since the underlying Oracle XML SQL Utility for Java natively supports all of these combinations for producing richly structure XML from SQL statements that make use of these features, your Oracle XSQL Pages gain this capability for "free". We'll look at two simple examples...

If you have used the object/relational capabilities of Oracle8i to create a user-defined object type called POINT using the command:

CREATE TYPE POINT AS OBJECT (X NUMBER, Y NUMBER);

and have used your new POINT type as the datatype of the ORIGIN column in your LOCATION table with the DDL statement:

CREATE TABLE LOCATION (
  NAME   VARCHAR2(80),
  ORIGIN POINT
);

and have inserted a row into this LOCATION table using an INSERT statement with the POINT() constructor like...

INSERT INTO LOCATION VALUES ( 'Someplace', POINT(11,17) );
COMMIT;

Then, an XSQL page like point.xsql below that does a query over the LOCATION table like...

<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
  SELECT name, origin
    FROM location loc
   WHERE loc.origin.x = {@x-coord}
</xsql:query> 

...when requested using a URL like:

http://yourmachine.com/xsql/demo/point.xsql?x-coord=11

produces the output:

<ROWSET>
   <ROW num="1">
      <NAME>Someplace</NAME>
      <ORIGIN>
         <X>11</X>
         <Y>17</Y>
      </ORIGIN>
   </ROW>
</ROWSET>

This demonstrates how the nested X and Y attributes in the POINT datatype structure of the ORIGIN column appear automatically as nested <X> and <Y> elements in the XML output. This is about the simplest possible example of using a user-defined type to get more richly structured XML output from your object/relational database. See the included Insurance Claim Demo for a much more detailed example combining both object types and object views. Take a look at the the associated SQL script in ./xsql/demo/insclaim/insclaim.sql to see the SQL syntax for creating the object types and object views used by the demo.

Note : See the supplied ./xsql/demo/point/point.sql script to create the example type and table above on your database. Also in the ./xsql/demo directory, see the ./empdept/empdeptobjs.sql, ./classerr/invalidclasses.sql, and ./insclaim/insclaim.sql files for additional object view examples.

If you have not created object types that contain a pre-defined structure, you can still introduce nested structure into your SQL queries using the CURSOR operator allows you to select a nested rowset as a column in the SELECT list of a query. While almost any nested query is legal to include inside the CURSOR operator in the SELECT list, the most useful is a query that selects a nested set of detail rows for the current "master" row.

Taking the familar DEPT and EMP tables as an example, the following XSQL Page contains a query that selects the DNAME column from the DEPT table, and for each row returned a nested rowset of the EMPLOYEES from the EMP table who work in that department:

<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
  SELECT dname,
         CURSOR( SELECT ename,sal
                   FROM emp
                  WHERE emp.deptno = dept.deptno) as employees /* Column Alias */
    FROM dept
   WHERE deptno = {@department}
</xsql:query>

Requesting this

http://yourserver.com/xsql/demo/empdept.xsql?department=10

produces the resulting XML "datapage":

<ROWSET>
   <ROW num="1">
      <DNAME>ACCOUNTING</DNAME>
      <EMPLOYEES>
         <EMPLOYEES_ROW num="1">
            <ENAME>CLARK</ENAME>
            <SAL>2450</SAL>
         </EMPLOYEES_ROW>
         <EMPLOYEES_ROW num="2">
            <ENAME>KING</ENAME>
            <SAL>5000</SAL>
         </EMPLOYEES_ROW>
         <EMPLOYEES_ROW num="3">
            <ENAME>MILLER</ENAME>
            <SAL>1300</SAL>
         </EMPLOYEES_ROW>
      </EMPLOYEES>
   </ROW>
</ROWSET>

Note that the second column in the SELECT statement is the expression using the CURSOR() operator to select the details. Since it is a column like any other, it can be aliased to the column name EMPLOYEES by using the AS COLUMNALIAS syntax as shown above.

Since the EMPLOYEES column is a nested rowset, it appears as a set of <ROW> elements nested within its parent <ROW> . Given the facts that:

• One or more CURSOR operators can be used in the SELECT list of any SQL statement,

• One or more CURSOR operators can be used in the SELECT list of SQL statements that appear inside any CURSOR operator (to any level of nesting),

• The SQL statement that may appear within the CURSOR operator can be virtually any valid SQL statment (including GROUP BY and ORDER BY, etc), and that

• Any SQL statement can be included in an <xsql:query> tag in an XSQL Page

you can quickly see how powerful, structured information can be created on the fly to get almost any structure you are looking for. This allows the processing speed of the database to be exploited for sorting and grouping instead of having to rely on slower techniques that would attempt these operations on flat data from within the XSLT stylesheet.

Of course, by using these query techniques in the <xsql:query> tags of an XSQL Page, you can combine rich master/detail XML "datapages" with powerful database sorting and grouping applied to them by the SQL engine before subsequently applying an XSLT Transformation to the resulting datapage as we learned above to transform the resulting datapage into any "presentation" format you need.

Oracle provides all the ingredients necessary to accomplish this task. The approach you take depends on whether you need to accomplish the insert from within your own Java program, or whether you want a simple, declarative way of inserting the target document.

The Oracle XML SQL Utility for Java contains the OracleXMLQuery class that we used above to get SQL query results out of the database. It also contains a companion class called OracleXMLSave that performs the opposite job of putting XML information back into Oracle tables or views.

The OracleXMLSave class understands how to insert any information which OracleXMLQuery knows how to produce. Said another way, the canonical structure of the output from OracleXMLQuery defines the kinds of structures for input which OracleXMLSave can automatically insert for us.

The fact that OracleXMLSave can only insert XML documents that look like XML documents produced by OracleXMLQuery may at first sound like a drastic limitation. However, this is not the case by any stretch of the imaginition. By taking advantage of an appropriate XSL transformation, virtually any XML document can be transformed to have the canonical format required by OracleXMLSave. This means that given:

• An arbitrary XML document X, and

• A database table T where you want to insert it

You can create an XSL Transformation that transforms the source document X into a target document X2 having precisely the structure needed for automatic insertion into table T.

Let's say that the source document is an XML news feed like what you'll see if you browse the following URL from www.moreover.com ...

http://www.moreover.com/cgi-local/page?index_xml+xml
      

A shortened version of such a resulting XML document looks like this:

<?xml version="1.0"?>
   <!DOCTYPE moreovernews SYSTEM "http://www.moreover.com/xml/moreovernews.dtd">
   <moreovernews>
      <article id="4227581">
         <url>http://d.moreover.com/click/here.pl?x4227575</url>
         <headline_text>Austin: webMethods gets deal with Dell</headline_text>
         <source>dbusiness.com</source>
         <media_type>text</media_type>
         <cluster>XML and metadata news</cluster>
         <tagline>Austin</tagline>
         <document_url>http://washington.dbusiness.com/</document_url>
         <harvest_time>Oct 30 1999  7:08AM</harvest_time>
         <access_registration> </access_registration>
         <access_status> </access_status>
      </article>
      <article id="4212701">
         <url>http://d.moreover.com/click/here.pl?x4212698</url>
         <headline_text>Microsoft continues XML push with resource kit</headline_text>
         <source>InfoWorld</source>
         <media_type>text</media_type>
         <cluster>XML and metadata news</cluster>
         <tagline></tagline>
         <document_url>http://www.infoworld.com/</document_url>
         <harvest_time>Oct 29 1999  7:27AM</harvest_time>
         <access_registration> </access_registration>
         <access_status> </access_status>
      </article>
      <article id="4202251">
         <url>http://d.moreover.com/click/here.pl?x4202247</url>
         <headline_text>IBM Brings XML To MQSeries</headline_text>
         <source>Internet Week</source>
         <media_type>text</media_type>
         <cluster>XML and metadata news</cluster>
         <tagline></tagline>
         <document_url>http://www.internetwk.com/</document_url>
         <harvest_time>Oct 28 1999  4:28PM</harvest_time>
         <access_registration> </access_registration>
         <access_status> </access_status>
      </article>
      <article id="4082434">
         <url>http://d.moreover.com/click/here.pl?x4082432</url>
         <headline_text>XML leader OnDisplay's travel clients praised</headline_text>
         <source>Web Travel News</source>
         <media_type>text</media_type>
         <cluster>XML and metadata news</cluster>
         <tagline></tagline>
         <document_url>http://www.webtravelnews.com/</document_url>
         <harvest_time>Oct 20 1999  7:34AM</harvest_time>
         <access_registration> </access_registration>
         <access_status> </access_status>
      </article>
    </moreovernews>

CREATE TABLE newsstory(
 id     NUMBER PRIMARY KEY,
 title  VARCHAR2(200),
 url    VARCHAR2(200),
 source VARCHAR2(200)
);

And further, let's suppose that you want to insert information from the XML news feed from Moreover.com into this table.

So, we need to produce an XSL transformation that transforms the information in the XML news feed from Moreover.com into the canonical format that OracleXMLSave knows how to insert into the newsstory table.

We start by using an XSQL page to quickly produce one row of canonical query results from the newstory table. We can produce this with the XSQL Page:

<?xml version="1.0"?>
<query connection="demo" max-rows="1">

 SELECT *
   FROM NEWSTORY

</query>

We can either request this page through the webserver, or more conveninently for this job, use the XSQL Command Line Utility to quickly put the XSQL Page's output into a file:

xsql newstory.xsql newsstory.xml

This command processes the newsstory.xsql page above and writes the output to the file named newsstory.xml. The contents of the newsstory.xml file will look like:

<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <ID>1911</ID>
      <TITLE>Some Title</TITLE>
      <URL>http://somemachine/somepage.html</URL>
      <SOURCE>SomeSource</SOURCE>
   </ROW>
</ROWSET>

We can take this one row of canonical output from a "SELECT *" query over the newsstory table and immediately turn it into our "insert transformation" by doing the following steps:

1. Add an xsl namespace declaration to the document element,

2. Remove the num="1" attribute from the <ROW> element,

3. Remove any elements corresponding to columns whose values will be assigned by database triggers, like the ID column will be in this example,

4. Surround the <ROW> element with an <xsl:for-each> that loops over the <article> elements in the source document from Moreover.com, and

5. Replace the literal text between the <TITLE> , <URL> , and <SOURCE> elements with an appropriate <xsl:value-of> element to plug-in the appropriate information from the current <article> we're looping over in the enclosing <xsl:for-each> loop.

The result of applying the changes outlined in the three steps above to the sample output of the:

SELECT * FROM NEWSSTORY

query produces the following XSL Transformation:

<?xml version = '1.0'?>
<ROWSET xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:for-each select="moreovernews/article">
   <ROW>
      <TITLE><xsl:value-of select="headline_text"/></TITLE>
      <URL><xsl:value-of select="url"/></URL>
      <SOURCE>Moreover.com</SOURCE>
   </ROW>
   </xsl:for-each>
</ROWSET>

Which illustrates that we have:

• Added the xsl namespace declaration to the <ROWSET> element:

  <ROWSET xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

• Added the <xsl:for-each> element to loop over each <article> element that is a child of the <moreovernews> element in the source document:

  <xsl:for-each select="moreovernews/article">

  so that for each <article> we find in the source, we create a <ROW> in the target.

• Removed the <ID> element since its value will be assigned by our database trigger that assign new story id's from a database sequence.

• Changed the:

  <TITLE>Some Title</TITLE>
  <URL>http://somemachine/somepage.html</URL>

  tags to:

  <TITLE><xsl:value-of select="headline_text"/></TITLE>
  <URL><xsl:value-of select="url"/></URL>

  to plug-in the value of the current <headline_text> and <url> for the current <article> .

• Hard-coded the value of the <SOURCE> element to Moreover.com to indicate where this news feed came from.

If we rename the now-edited newsstory.xml file to moreover-to-newsstory.xsl, we can use the command-line oraxsl utility to test our transformation:

java oracle.xml.parser.v2.oraxsl moreover.xml moreover-to-newsstory.xsl out.xml

Note : Since the moreover.xml file includes the: <!DOCTYPE moreovernews SYSTEM "http://www.moreover.com/xml/moreovernews.dtd"> declaration with an http:// reference to the MoreoverNews DTD that is outside of Oracle's firewall, you need to provide the following three extra Java VM parameters to allow the Oracle XML Parser to properly find and parse the DTD through our Proxy server: java -DproxySet=true -DproxyHost=proxyServerName -DproxyPort=80 oracle.xml.parser.v2.oraxsl ...

If we use Internet Explorer to browse the resulting out.xml file, we can see the results of the transformation:

If we pass this resulting target document to the OracleXMLSave class, as we'll do below, it will effortlessly insert all of the Moreover.com data into our newsstory table.

The last step before showing how to tie it all together using Java or XSQL Pages is to create the database trigger to automatically assign the value of the ID column on insert. Assuming we have a sequence named newsstory_id_seq lying around, the code looks like:

CREATE TRIGGER newsstory_autoid
BEFORE INSERT ON newsstory FOR EACH ROW
BEGIN
  SELECT newsstory_id_seq.nextval
    INTO :new.id
    FROM dual;
END;

The general steps for inserting XML into the database are:

1. Choose the table or view you want to use for inserting the XML information,

2. Create an XSL Transformation that transforms the inbound document into the canonical format for this table or view,

   We saw a "cookbook" approach above for how to do this easily based on a SELECT * query against the target table or view,

3. Transform the inbound document into the canonical format for the table or view into which you want to insert it, and

4. Let OracleXMLSave insert the transformed document into your table or view.

Oracle XSQL Pages support a simple <xsql:insert-request> tag that automates these four steps for you when you need to post XML documents to be inserted into the database to your webserver over HTTP. Given the name of the table or view to use as the insert target and the name of the XSL transformation to use to transform the inbound document into the canonical format for this table or view, you can add the tag:

<xsql:insert-request 
      table="tableorviewname"
      transform="transformname.xsl"/>

to the top of your XSQL page to perform the four steps above automatically. So, for example, the following XSQL Page would accept information posted through HTTP in the Moreover.com moreovernews format, and insert it into the newsstory table:

<?xml version="1.0?>
<xsql:insert-request xmlns:xsql="urn:oracle-xsql" 
      connection="demo" 
      table="newsstory"
      transform="moreover-to-newsstory.xsl"/>

Running this program retrieves the newsstories and inserts them into our NEWSSTORY table.

Due to the nature of this news feed, news stories stay in the feed for a few days. If we want to avoid inserting the same story over and over again, we can easily do that by making sure we don't insert a story unless its Title and URL are a unique combination in our NEWSSTORY table.

Let's implement this behavior using a database INSTEAD OF INSERT trigger. Creating an INSTEAD OF INSERT trigger allows us to write code which will be executed in the database whenever an INSERT of any kind is performed. In the code of the INSTEAD OF INSERT trigger, we can check for uniqueness of the newsstory and only really insert it if it is unique.

Since INSTEAD OF triggers can only be defined on database views in Oracle8i, we simply need to create the newstoryview as follows:

CREATE VIEW newsstoryview AS
SELECT *
  FROM newsstory

Then we can create the INSTEAD OF INSERT trigger from the SQL*Plus command line using code like:

CREATE OR REPLACE TRIGGER insteadOfIns_newsstoryview
INSTEAD OF INSERT ON newsstoryview FOR EACH ROW
DECLARE
  notThere BOOLEAN := TRUE;
  tmp      VARCHAR2(1);
  CURSOR chk IS SELECT 'x'
                  FROM newsstory
                 WHERE title = :new.title
                  AND url   = :new.url;
BEGIN
  OPEN chk;
  FETCH chk INTO tmp;
  notThere := chk%NOTFOUND;
  CLOSE chk;
  IF notThere THEN
    INSERT INTO newsstory(title,url,source)
         VALUES (:new.title,:new.url,:new.source);
  END IF;
END;

Here we are assuming that "uniqueness" of a story is defined by the combination of its TITLE and its URL columns. To make the check fast, we can create a unique index on the (TITLE,URL) combination with the command:

CREATE UNIQUE INDEX newsstory_unique_title_url on newsstory(title,url);

We've written the body of the trigger in PL/SQL to demonstrate that you can mix and match PL/SQL and Java in this solution, but in Oracle8i we could have also written the INSTEAD OF trigger to call a Java Stored Procedures as well to perform the uniqueness check.

Finally, the only thing left to do is to change the xsql:insert-request action element above to use the NEWSSTORYVIEW instead of the NEWSTORY table by changing the line:

<?xml version="1.0?>
<xsql:insert-request xmlns:xsql="urn:oracle-xsql" 
      connection="demo" 
      table="newsstoryview"
      transform="moreover-to-newsstory.xsl"/>

Now, only unique newstories from the Moreover XML news feed will be inserted.

Note : Using the same INSTEAD OF INSERT trigger technique above, it would be quite straightforward to support automatically updating XML information in the database as well. While OracleXMLSave can only perform insert operations, the INSTEAD OF INSERT trigger on your view could check for existience of a primary key, and if it exists, actually perform an UPDATE instead of an INSERT.

The <xsql:insert-request> tag can be combined with <xsql:query> tags in your XSQL page to first insert any posted XML document (if there is any), then return some data from queries. For example:

<?xml version="1.0"?>
<page connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:insert-request table="newsstoryview" transform="moreover-to-newsstory.xsl"/>
  <lateststories>
    <xsql:query tag-case="lower" max-rows="5" rowset-element="" row-element="story" >
      select * 
        from newsstory
      order by id desc
    </xsql:query>
  </lateststories>
</page>

The XSQL page above inserts any posted XML document containing moreovernews/article elements (as we did programmatically above), and then returns the XML datagram to the requester that looks like this viewed "raw" in the Internet Explorer 5.0 browser:

When XML is posted to a web server through HTTP, it's ContentType is text/xml by convention. Sometimes, it's convenient to accept posted information as a set of HTML <FORM> parameters. When an HTML form is posted, the server receives it with a ContentType of application/x-www-form-urlencoded. When the XSQL Servlet receives an HTTP request with method="POST" from such a form, it internally converts the form parameters into an XML document that looks like:

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

and then allows an <xsql:insert-request> tag to treat this document as the posted input document.

Note : If multiple parameters are posted with the same name, then they will automatically be "row-ified" to make subsequent processing easier. This means, for example, that a request which posts or includes the following parameters: id=101 name=Steve id=102 name=Sita operation=update Will create a "row-ified" set of parameters like: <request> <parameters> <row> <id>101</id> <name>Steve</name> </row> <row> <id>102</id> <name>Sita</name> </row> <operation>update</operation> </parameters> : </request>

Using an <xsql:insert-request> tag like:

<xsql:insert-request 
      table="newsstoryview" 
      transform="request-to-newsstoryview.xsl"/

and by referencing the name of the request-to-newsstoryview.xsl transform that looks like:

<?xml version = '1.0'?>
<ROWSET xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xsl:version="1.0">
   <xsl:for-each select="request/parameters">
   <ROW>
      <TITLE><xsl:value-of select="title_field"/></TITLE>
      <URL><xsl:value-of select="url_field"/></URL>
      <SOURCE>User-Submitted</SOURCE>
   </ROW>
   </xsl:for-each>
</ROWSET>

If the above XSQL page were saved as insertnewsform.xsql, it can be used as the target of an HTML <FORM> element that includes fields named title_field and url_field by simply setting the forms's ACTION="insertnewsform.xsql" attribute. The following newsform.html file demonstrate this:

<html>
<body>
  Insert a new news story...
  <form action="insertnewsform.xsql" method="post">
    <b>Title</b><input type="text" name="title_field" size="30"><br>
    <b>URL</b><input type="text" name="url_field" size="30"><br>
    <br>
    <input type="submit">
  </form>
<body>
</html>

If we let a user fill-out and post the form as-is, they will get raw XML as a response from the insertnewform.xsql page, listing the five most recent news stories entered as show above. However, we can easily improve on that for better usability.

Using the mechanism we learned in the previous section for associating XSL Stylesheets with XSQL Pages, we can include an <?xml-stylesheet?> processing instruction at the top of the insertnewsform.xsql that refers to the lateststories.xsl XSL Stylesheet below:

<html xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <head>
    <title>Latest Stories</title>
  </head>
  <body>
  <h2>Thanks for your Story!</h2>
  Here's a list of the latest stories we've received...
    <table border="0" cellspacing="0">
      <xsl:for-each select="page/lateststories/story">
        <tr>
         <td><a href="{url}"><xsl:value-of select="title"/></a></td>
        </tr>
      </xsl:for-each>
    </table>
  </body>
</html>

This means the insertnewsform.xsql now will look like:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="lateststories.xsl"?>
<page connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:insert-request table="newsstoryview" transform="request-to-newsstory.xsl"/>
  <lateststories>
    <xsql:query tag-case="lower" max-rows="5" rowset-element="" row-element="story" >
      select * 
        from newsstory
      order by id desc
    </xsql:query>
  </lateststories>
</page>

Now when the user browses the newsform.html page and enters her story...

...then rather than seeing the raw XML datagram returned by the insertnewsform.xsql page, this will be transformed using the lateststories.xsl stylesheet so that it shows up in her browser like:

as an HTML page instead of as raw XML.

So in addition to being possible for developers by using Java programs that leverage the Oracle XML SQL Utility directly, we've seen that it's easy to insert XML-based information into Oracle database tables or views without programming using XSQL Pages and XSLT Transformations.

Note : Here we've used simple examples with simple tables, however OracleXMLQuery and OracleXMLSave work well with any kind of richly structured object and with all supported datatypes. This means that, while not shown here, the same techniques described in this paper can be applied used to: Insert richly strutured information into an Object View with nested types and nested collections Insert "fragments" or "chunks" of XML elements and their content (from an entire document to any desired granularity of sub-structure) into a CLOB column or an object type with a CLOB attribute. The ./xsql/demo/empdeptobjs.sql script shows a working example of an a "department" object view ( based over the familiar EMP and DEPT tables) and an accompanying INSTEAD OF INSERT trigger on the object view to allow structured inserting of multiple departments and nested employees.

The following demo files are part of this release. Instructions for installing the demos are included in the Installation section of these release notes.

Description of Demonstrations
Demonstration Name (directory)	Comments
Hello World (helloworld)	Simplest possible XSQL page.
Do You XML Site (doyouxml)	XSQL page which shows how a simple, data-driven web site can be built using an XSQL page which makes clever use of SQL, XSQL-substitution variables in the queries, and XSLT for formatting the site. Demonstrates using substitution parameters in both the body of SQL query statements within <xsql:query> tags, as well as withing the attributes to <xsql:query> tags to control things like how many records to display and to skip (for "paging" through query results in a stateless way).
Employee Page (emp)	XSQL page showing XML data from the EMP table, using XSQL page parameters to control what employees are returned and what column(s) to use for the database sort. Uses an associated XSLT Stylesheet for format the results as an HTML Form containing the emp.xsql page as the form action so the user can refine their search criteria.
Insurance Claim Page (insclaim)	Demonstrates a number of sample queries over the richly-structured, Insurance Claim object view. The insclaim.sql sets up the INSURANCE_CLAIM_VIEW object view and populates some sample data.
Invalid Classes Page (classerr)	XSQL Page which uses invalidclasses.xsl to format a "live" list of current Java class compilation errors in your schema. The accompanying .sql script sets up the XSQLJavaClassesView object view used by the demo. The master/detail information from the object view is formatted into HTML by the invalidclasses.xsl stylesheet in the server.
Airport Code Validation (airport)	XSQL page which returns a "datagram" of information about airports based on their three-letter code. Demonstrates using the <xsql:no-rows-query> to attempt alternative queries when initial queries return no rows. In this case, after attempting an exact match on the airport code passed in, the page tries a fuzzy match on the airport description. The airport.htm page demonstrates how to use the XML results of the airport.xsql page programmatically from within a web page using JavaScript to exploit the built-in XML Document Object Model functionality in the Internet Explorer 5.0 browser. When you type in a three-leter airport code into this web page, some JavaScript under the covers fetches the XML datagram from the XSQL Servlet over the web corresponding to information for the airport code you typed in. If the return indicates that there was no exact match, the builds up a dynamic "picklist" of possible matches based on the information returned in the XML "datagram" from the XSQL Servlet.
Airport Code Display (airport)	Demonstrates using the same XSQL page as the previous example but supplying an XSLT Stylesheet name in the request. This causes the airport information to be formatted as an HTML form instead of being returned as raw XML.
Airport Code Display (airport)	Demonstrates returning Airport information as a SOAP Service.
Emp/Dept Object View Demo (empdept)	Demonstrates using an object view to group master/detail information from two existing "flat" tables like EMP and DEPT. The empdeptobjs.sql script creates the object view (along with INSTEAD OF INSERT triggers allowing the master/detail view to be used as an insert target of xsql:insert-request). The empdept.xsl stylesheet illustrates an example of the "simple form" of an XSLT stylesheet that can look just like an HTML page without the extra xsl:stylesheet or xsl:transform at the top. This is part of the XSLT 1.0 specification called using a Literal Result Element as Stylesheet. It also demonstrates how to generate an HTML page that includes the <link rel="stylesheet"> to allow the generated HTML to fully leverage CSS for centralized HTML style information, found in the coolcolors.css file.
Adhoc Query Visualization (adhocsql)	Demonstrates passing the entire SQL query and XSLT Stylesheet to use as parameters to the server.
XML Document Demo (document)	Demonstrates inserting XML documents into relational tables. The docdemo.sql script creates a user-defined type called XMLDOCFRAG containing an attribute of type CLOB. Try inserting the text of the document in ./xsql/demo/xml99.xml and providing the name xml99.xsl as the stylesheet, as well as ./xsql/demo/JDevRelNotes.xml with the stylesheet relnotes.xsl. The docstyle.xsql page illustrates an example of the <xsql:include-xsql> action element to include the output of the doc.xsql page into its own page before transforming the final output using a client-supplied stylesheet name. The demo uses the client-side XML features of Internet Explorer 5.0 to check the document for well-formedness before allowing it to be posted to the server.
XML Insert Request Demo (insertxml)	Demonstrates posting XML from a client to an XSQL Page that handles inserting the posted XML information into a database table using the <xsql:insert-request> action element. The demo is setup to accept XML documents in the moreover.com XML-based news format. In this case, the program doing the posting of the XML is a client-side web page using Internet Explorer 5.0 and the XMLHttpRequest object from JavaScript. If you look at the source for the insertnewsstory.xsql page, you'll see it's specifying a table name and an XSLT Transform name. The moreover-to-newsstory.xsl stylesheet transforms the incoming XML information into the canonical format that the OracleXMLSave utility knows how to insert. Try copying and pasting the example <article> element several times within the <moreovernews> element to insert several new articles in one shot. The newsstory.sql script shows how INSTEAD OF triggers can be used on the database views into which you ask XSQL Pages to insert to the data to customize how incoming data is handled, default primary key values, etc.
SVG Demo (svg)	The deptlist.xsql page displays a simple list of departments with hyperlinks to the SalChart.xsql page. The SalChart.xsql page queries employees for a given department passed in as a parameter and uses the associated SalChart.xsql stylesheet to format the result into a Scalable Vector Graphics drawing, a bar chart comparing salaries of the employees in that department.

Then, browse the URL http://localhost/xsql/index.html to see a list of all the demos.

Once you've understood what the XSQL Page Processor running inside the XSQL Servlet does, you'll realize that Oracle XSQL Pages is really just a very thin layer of convenience functionality allowing you to leverage the tremendous flexibility and power of SQL, XML, and XSLT to really do the "heavy lifting".

Note : Some of the material included in this document is excerpted from Building Oracle XML Applications by permission of O'Reilly and Associates.