27 XSQL Pages Reference

<xsql:action>

Purpose

Invokes a user-defined action handler, implemented in Java, for executing custom logic and including custom XML data in a XSQL page. The Java class invoked with this action must implement the oracle.xml.xsql.XSQLActionHandler interface.

Use <xsql:action> to perform tasks that are not handled by the built-in action handlers. Custom actions can supply arbitrary XML content to the data page and perform arbitrary processing.

Usage Notes

The XSQL page processor processes the actions in a page in the following way:

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

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

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

Syntax

The syntax for this action is as follows, where handler is a single, required attribute named whose value is the fully-qualified Java class name of the invoked action, yourpackage is the Java package, and YourCustomHandler is the Java class:

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

Some action handlers expect text content or element content to appear inside the <xsql:action> element. In this case, use syntax such as the following:

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

You can also use the following syntax:

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

Attributes

The only required attribute is handler, but you can supply additional attributes to the handler. For example, if yourpackage.YourCustomHandler is expecting attributes named param1 and param2, then use the following syntax:

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

Examples

Example 27-1 shows an XSQL page that invokes the myactions.StockQuotes Java class. It includes stock quotes from Google for any symbols passed in with the symbol parameter. If this parameter is not supplied, it supplies a default list.

<?xml version="1.0"?>
<page xmlns:xsql="urn:oracle-xsql">
  <xsql:action handler="myactions.StockQuotes"
               symbols="{@symbol}"
               symbol="ORCL,SAP,MSFT,IBM"/>
</page>

<xsql:delete-request>

Purpose

Accepts data posted from an XML document or HTML form and uses the XML SQL Utility (XSU) to delete the content of an XML document in canonical form from a target table or view.

By combining XSU with XSLT, you can transform XML into the canonical format expected by a given table. Afterward, you can use XSU to delete the resulting canonical XML. For a specified database table, the canonical XML form is given by one row of XML output from a SELECT * query against the table.

Syntax

The syntax for this action is as follows, where table_name is the name of a table and key is a list of one or more columns to use as the unique key:

<xsql:delete-request table="table_name" key-columns="key"/>

Attributes

Table 27-3 lists the optional attributes that you can use on the <xsql:delete-request> action. Required attributes are in bold

table = "string"

key-columns = "string string ..."

transform = "URL"

columns = "string"

commit = "boolean"

commit-batch-size = "integer"

date-format = "string"

error-param = "string"

Examples

Example 27-2 specifies that the posted XML document should be transformed with the style.xsl stylesheet and then deleted from the departments table. The departments.department_id column is the primary key for the deletion.

<?xml version="1.0"?>
<xsql:delete-request table="departments"       transform="style.xsl" 
 connection="demo" key-columns="department_id" xmlns:xsql="urn:oracle-xsql"/>

<xsql:dml>

Purpose

Executes a DML or DDL statement or a PL/SQL block. Typically, you use this tag to include statements that would be executed or rolled back together.

This action requires a database connection provided as a connection="connname" attribute on the document element of the XSQL page in which it appears.

Usage Notes

You cannot set parameter values by binding them in the position of OUT variables with <xsql:dml>. Only IN parameters are supported for binding.

Syntax

The syntax for the action is as follows, where DML_DDL_or_PLSQL is a placeholder for a legal DML statement, DDL statement, or PL/SQL block:

<xsql:dml>
  DML_DDL_or_PLSQL
</xsql:dml>

Attributes

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

commit = "boolean"

bind-params = "string"

error-param = "string"

error-statement = "boolean"

Examples

Example 27-3 inserts the username stored in the webuser cookie into a request_log table. Using bind variables guards against SQL injection attacks.

<xsql:dml connection="demo" bind-params="webuser"
           xmlns:xsql="urn:oracle-xsql">
   BEGIN
     INSERT INTO request_log(page,userid)
       VALUES( 'somepage.xsql', ? );
     COMMIT;
   END;
 </xsql:dml>

<xsql:if-param>

Purpose

Enables you to include elements and actions nested inside if a specified condition is true. If the condition is true, then all nested XML content and actions are included in the page. If the condition is false, then none of the nested XML content or actions is included (and thus none of the nested actions is executed).

Specify which parameter value is evaluated by supplying the required name attribute. Both simple parameter names as well as array-parameter names are supported.

Syntax

The syntax for the action is the following, where some_name is the value of the name attribute and test_condition is exactly one of the conditions listed in Table 27-5:

<xsql:if-param name="some_name" test_condition>
   element_or_action
</xsql:if-param>

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

Attributes

In addition to the required name attribute, you must pick exactly one of the attributes listed in Table 27-5 to indicate how the parameter value (or values, in the array case) is tested. As with other XSQL actions, the attributes of the <xsql:if-param> action can contain lexical substitution parameter expressions such as {@paramName}.

exists="yes_or_no"

equals="stringValue"

not-equals="stringValue"

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

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

Examples

To test whether two different conditions are true, you can use nested <xsql:if-param> elements as shown in Example 27-4.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="style.xsl"?>
<page connection="demo" xmlns:xsql="urn:oracle-xsql">
<!-- 
| Set page parameter 'some_param' to value "some_value" if parameter 'a'
| exists, and if parameter 'b' has a value equal to "X"
+-->
  <xsql:if-param name="a" exists="yes">
    <xsql:if-param name="b" equals="X">
      <xsql:set-page-param name="some_param" value="some_value"/>
    </xsql:if-param>
  </xsql:if-param>
  <!-- ... --> 
</page>

<xsql:include-owa>

Purpose

Includes XML content generated by a database stored procedure. This action requires a database connection to be provided by supplying a connection="connname" attribute on the document element of the XSQL page in which it appears.

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

Usage Notes

You can create a wrapper procedure that constructs XML elements with the HTP package. Your XSQL page can invoke the wrapper procedure by using <xsql:include-owa>.

Syntax

The syntax for the action is as follows, where PL/SQL_block is a PL/SQL Block invoking a procedure that uses the HTP or HTF packages:

<xsql:include-owa>
   PL/SQL_block
</xsql:include-owa>

Attributes

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

bind-params = "string"

error-param = "string"

error-statement = "boolean"

Examples

Assume that you write a PL/SQL procedure called UpdateStatus that updates the status of a project. The procedure uses HTP to print an <UpdateStatus> datagram that contains the element <Success/> if no errors occur or one or more <Error> elements if errors occur.

Example 27-5 shows how you can call UpdateStatus from an XSQL page. The example uses SQL bind variable instead of lexical substitution to prevent the possibility of SQL injection attacks.

<xsql:include-owa connection="demo" 
                  bind-params="project status" 
                  xmlns:xsql="urn:oracle-xsql"> 
  UpdateStatus( ?,? ); 
</xsql:include-owa> 

Assume that a user enters an invalid status number for a project into a Web-based form. The form posts the input parameters to an XSQL page as shown in Example 27-5. The XSQL processor returns the following datagram, which an XSLT stylesheet could transform into an HTML error page:

<UpdateStatus>
  <Error Field="status">Status must be 1, 2, 3, or 4</Error>
</UpdateStatus>

<xsql:include-param>

Purpose

Includes an XML representation of the name and value of a single parameter. This technique is useful if an associated XSLT stylesheet needs to refer to parameter values with XPath expressions.

Syntax

The syntax of the action is as follows, where paramname is the name of a parameter:

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

The required name attribute supplies the name of the parameter whose value you want to include.

Attributes

The name attribute is required; there are no optional attributes.

Examples

Example 27-6 uses XPATH to obtain the value of a parameter and represent it in XML.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="style.xsl"?>
<page connection="demo" xmlns:xsql="urn:oracle-xsql" 
                        xmlns:p="http://www.companysite.com/products">
  <xsql:set-page-param name="productid"
                       xpath="/p:Products/productid"/>
  <xsql:include-param name="productid"/>
</page>

The XML fragment included in the datagram will be as follows:

<productid>12345</productid>

Suppose that you use an array parameter name to indicate that you want to treat the value as an array, as in the following example:

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

The XML fragment reflects all of the array values, as shown in the following example:

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

In this array-parameter name scenario, if productid happens to be a single-valued parameter, then the fragment looks identical to a one-element array, as illustrated in the following example:

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

<xsql:include-posted-xml>

Purpose

Includes the posted XML document in the XSQL page. If the user posts an HTML form instead of an XML document, then the XML included is similar to that included by the <xsql:include-request-params> action.

Syntax

The syntax of the action is as follows:

<xsql:include-posted-xml/>

Attributes

None.

Examples

Example 27-7 shows a sample XSQL page that includes a posted XML document.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsql" href="somepage.xsql"?>
<page connection="demo" 
      xmlns:xsql="urn:oracle-xsql">
  <xsql:include-posted-xml/>
</page>

<xsql:include-request-params>

Purpose

Includes an XML representation of all parameters in the request in the datagram. The action element is replaced in the page at page-request time with a tree of XML elements that represents the parameters available to the request.

This technique is useful if an associated XSLT stylesheet needs to refer to request parameter values with XPath expressions.

Usage Notes

When processing pages through the XSQL servlet, the XML included takes the form shown in Example 27-8.

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

When you use the XSQL command-line utility or the XSQLRequest class, the XML takes the form shown in Example 27-11.

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

The technique enables you to distinguish request parameters from session parameters or cookies because its value is a child element of <parameters>, <session>, or <cookies>.

Syntax

The syntax of the action is as follows:

<xsql:include-request-params/>

Attributes

None.

Examples

Example 27-10 shows a sample XSQL page that includes all request parameters in the data page.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsql" href="cookie_condition.xsl"?>
<page connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:include-request-params/>
</page>

The cookie_condition.xsl stylesheet chooses an output format based on whether the siteuser cookie is present. Example 27-11 shows a fragment of the stylesheet.

<xsl:choose>
  <xsl:when test="/page/request/cookies/siteuser">
  ...
  </xsl:when>
  <xsl:otherwise>
  ...
  </xsl:otherwise>
</xsl:choose>

<xsql:include-xml>

Purpose

Includes the XML contents of a local, remote, or database-driven XML resource in your datagram. You can specify the resource by URL or SQL statement. The server can deliver a resource that is a static XML file or dynamically created XML from a programmatic resource such as a servlet or CGI program.

Syntax

The syntax for this action is as follows, where URL is a relative URL or an absolute, HTTP-based URL to retrieve XML from another Web site:

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

Alternatively, you can use the following syntax, where SQL_statement is a SQL SELECT statement selecting a single row containing a single CLOB or VARCHAR2 column value:

<xsql:include-xml>
  SQL_statement
</xsql:include-xml>

The href attribute and SQL statement are mutually exclusive. If you provide one, then the other is not allowed.

Attributes

Table 27-7 lists the attributes supported by this action. Required attributes are in bold.

href="URL"

bind-params = "string"

error-param = "string"

Examples

Example 27-12 includes an XML document retrieved by a database query. The XML content is a CLOB-valued member field of a user-defined type. The XML included must come from a VARCHAR2 or CLOB column, not an XMLType.

<?xml version="1.0"?>
<xsql:include-xml bind-params="id" connection="demo"
                   xmlns:xsql="urn:oracle-xsql">
  SELECT x.document.contents doc FROM xmldoc x
  WHERE x.docid = ?                        
</xsql:include-xml>

<xsql:include-xsql>

Purpose

Includes the XML output of one XSQL page in another page. You can create a page that assembles the contents­—optionally transformed—from other XSQL pages.

Usage Notes

If the aggregated page contains an <?xml-stylesheet?> processing instruction, then this stylesheet is applied before the result is aggregated. Thus, you can use <xsql:include-xsql> to chain XSLT stylesheets.

When one XSQL page aggregates another page by using <xsql:include-xsql>, all request-level parameters are visible to the nested page. For pages processed by the XSQL Servlet, the visible data includes session-level parameters and cookies. None of the aggregating page's page-private parameters are visible to the nested page.

Syntax

The syntax for this action is as follows, where XSQL_page is a relative or absolute URL of an XSQL page to be included:

<xsql:include-xsql href="XSQL_page"/>

Attributes

Table 27-8 lists the attributes supported by this action. Required attributes are in bold; all others are optional.

href="string"

error-param = "string"

reparse = "boolean"

Examples

Example 27-13 displays an XSQL page that lists discussion forum categories.

<?xml version="1.0"?>
<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
  SELECT name
  FROM categories
  ORDER BY name
</xsql:query>

Example 27-14 shows how you can include the results of the page in Example 27-13 into a page that lists the ten most recent topics in the current forum.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="style.xsl"?>
<top-ten-topics connection="demo" xmlns:xsql="urn:oracle-xsql">
  <topics>
    <xsql:query max-rows="10">
      SELECT subject 
      FROM topics 
      ORDER BY last_modified DESC
    </xsql:query>
  </topics>
  <categories>
    <xsql:include-xsql href="Categories.xsql"/>
  </categories>
</top-ten-topics>

You can also use <xsql:include-xsql> to apply an XSLT stylesheet to an included page. Assume that you write the following XSLT stylesheets:

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

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

One approach for catering to two different types of devices is to create different XSQL pages for each device. Example 27-15 shows an XSQL page that aggregates Categories.xsql and applies the cats-as-html.xsl stylesheet.

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

Example 27-16 shows an XSQL page that aggregates Categories.xsql and applies the cats-as-html.xsl stylesheet for delivering to wireless devices.

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

<xsql:insert-param>

Purpose

Inserts the value of a parameter into a table or view. Use this tag when the client is posting a well-formed XML document as text in an HTTP parameter or individual HTML form field.

By combining the XML SQL Utility (XSU) with XSLT, you can transform XML into the canonical format expected by a given table. Afterward, you can use XSU to insert the resulting canonical XML. For a specified database table, the canonical XML form is given by one row of XML output from a SELECT * query against the table.

Syntax

The syntax for this action is as follows, where table_or_view_name is a relative or absolute URL of an XSQL page to be included:

<xsql:insert-param table="table_or_view_name" name="string"/>

Attributes

Table 27-9 lists the optional attributes that you can use on the <xsql:insert-param> action.

name="string"

table="string"

transform = "URL"

columns = "string"

commit = "boolean"

commit-batch-size = "integer"

date-format = "string"

error-param = "string"

Examples

Example 27-17 parses and transforms the contents of the HTML form parameter xmlfield for database insert.

<?xml version="1.0"?>
<xsql:insert-param name="xmlfield" table="image_metadata_table"
transform="field-to-rowset.xsl" connection="demo" xmlns:xsql="urn:oracle-xsql"/>

<xsql:insert-request>

Purpose

Accepts data posted from an XML document or HTML form and uses the XML SQL Utility (XSU) to insert the content of an XML document in canonical form into a target table or view.

If an HTML Form has been posted, then the posted XML document is materialized from HTTP request parameters, cookies, and session variables. The XML document has the following form:

<request>
<parameters>
  <param1>value1</param1>
    :
  </paramN>valueN</paramN>
</parameters>
  :
</request>

By combining XSU with XSLT, you can transform XML into the canonical format expected by a given table. The XSQL engine uses XSU to insert the resulting canonical XML. For a specified database table, the canonical XML form is given by one row of XML output from a SELECT * query against the table.

Usage Notes

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

Syntax

The syntax for this action is as follows:

<xsql:insert-request table="table"/>

Attributes

Table 27-10 lists the optional attributes that you can use on the <xsql:insert-request> action.

table = "string"

transform = "URL"

columns = "string"

commit = "boolean"

commit-batch-size = "integer"

date-format = "string"

error-param = "string"

Examples

Example 27-18 parses and transforms the contents of the posted XML document or HTML Form for insert.

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

<xsql:query>

Purpose

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

Syntax

The syntax for the action is the following:

<xsql:query>
   SELECT_Statement
</xsql:query>

Any legal SQL select statement is permissible as a substitution for the SELECT_Statement placeholder. If the select statement produces no rows, then you can provide a fallback query by including a nested <xsql:no-rows-query> element as follows:

<xsql:query>
  SELECT_Statement
  <xsql:no-rows-query>
    Fallback_SELECT_Statement
  </xsql:no-rows-query>
</xsql:query>

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

Attributes

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

bind-params = "string"

date-format = "string"

error-param = "string"

error-statement = "boolean"

fetch-size = "integer"

id-attribute = "string"

id-attribute-column = "string"

include-schema = "boolean"

max-rows = "integer"

null-indicator = "boolean"

row-element = "string"

rowset-element = "string"

skip-rows = "integer"

tag-case = "string"

Examples

Example 27-20 shows a simple XSQL page.

<?xml version="1.0"?>
<xsql:query connection="xmlbook" xmlns:xsql="urn:oracle-xsql">
   SELECT 'Hello, World!' AS text     FROM DUAL</xsql:query>

If you save Example 27-20 as hello.xsql and execute it in a browser, the XSQL page processor returns the following XML:

<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <TEXT>Hello, World!</TEXT>
   </ROW>
</ROWSET>

By default, the XML produced by a query reflects the column structure of its result set, with element names matching the names of the columns. Columns in the result with the following nested structure produce nested elements that reflect this structure:

• Object types

• Collection types

• CURSOR expressions

The result of a typical query containing different types of columns and returning one row might look like Example 27-20.

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

A <ROW> element repeats for each row in the result set. Your query can use standard SQL column aliasing to rename the columns in the result, which effectively renames the XML elements that are produced. Column aliasing is required for columns whose names otherwise are illegal names for an XML element.

For example, an <xsql:query> action as shown in Example 27-21 produces an error because the default column name for the calculated expression is an illegal XML element name.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="style.xsl"?>
<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
  SELECT TO_CHAR(hire_date,'DD-MON') 
  FROM   employees
</xsql:query>

You can fix the problem by using column aliasing as shown in Example 27-22.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="style.xsl"?>
<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
  SELECT TO_CHAR(hire_date,'DD-MON') AS hiredate FROM   employees
</xsql:query>

<xsql:ref-cursor-function>

Purpose

Executes an arbitrary stored function returning a REF CURSOR and includes the query result set in canonical XML format. This action requires a database connection to be provided by supplying a connection="connname" attribute on the document element of the XSQL page in which it appears.

Use this tag to invoke a stored procedure that determines what the query is and returns a cursor to the query. Used in this way, this tag also provides a weak level of security because it can hide the query from direct inspection.

Syntax

The syntax of the action is as follows, where SCHEMA_NAME represents an optional database schema name, PACKAGE_NAME represents an optional PL/SQL package name, and FUNCTION_NAME (required) specifies the name of a PL/SQL function:

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

Attributes

The optional attributes are the same as for the <xsql:query> action listed in Table 27-11 except that fetch-size is not available for <xsql:ref-cursor-function>.

Examples

By exploiting dynamic SQL in PL/SQL, a function can conditionally construct a dynamic query before a cursor handle to its result set is returned to the XSQL page processor. The return value of the function must be of type REF CURSOR. Consider the PL/SQL package shown in Example 27-23.

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

An <xsql:ref-cursor-function> can include the dynamic results of the REF CURSOR returned by this function as shown in Example 27-24.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="style.xsl"?>
<xsql:ref-cursor-function connection="demo" xmlns:xsql="urn:oracle-xsql"> 
  DynCursor.DynamicQuery(1);
</xsql:ref-cursor-function>

<xsql:set-cookie>

Purpose

Sets an HTTP cookie to a value. By default, the value remains for the lifetime of the current browser, but you can change its lifetime by supplying the optional max-age attribute. The value to be assigned to the cookie can be supplied by a combination of static text and other parameter values, or from the result of a SQL SELECT statement.

Because this feature is specific to the HTTP protocol, this action is only effective if the XSQL page in which it appears is processed by the XSQL servlet. If this action is encountered in an XSQL page processed by the XSQL command-line utility or the XSQLRequest programmatic API, then it does nothing.

Usage Notes

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

If you need to set several cookie values based on the results of a single SQL statement, then do not use the name attribute. Instead, you can use the names attribute and supply a space-or-comma-delimited list of one or more cookie names.

Syntax

The syntax for this action is as follows, where paramname is the name of a parameter:

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

Alternatively, you can use the following syntax, where SQL_statement is a SQL SELECT statement and paramname is the name of a parameter:

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

Either the name or the names attribute is required. The value attribute and the contained SQL statement are mutually exclusive. The number of columns in the select list must match the number of cookies being set or an error message results.

Attributes

Table 27-12 lists the attributes supported by this action. Attributes in bold are required; all others are optional.

name = "string"

names = "string string ..."

bind-params = "string"

domain = "string"

error-param = "string"

ignore-empty-value = "boolean"

immediate = "boolean"

max-age = "integer"

only-if-unset = "boolean"

path = "string"

value = "string"

Examples

Example 27-25 sets the HTTP cookie to the value of the parameter named choice.

<?xml version="1.0"?>
<xsql:set-cookie name="last_selection" 
                 value="{@choice}" xmlns:xsql="urn:oracle-xsql"/>

Example 27-3 sets the HTTP cookie to a value selected from the database.

<?xml version="1.0"?>
<xsql:set-cookie name="shopping_cart_id" bind-params="user"
                 connection="demo"       xmlns:xsql="urn:oracle-xsql">
 SELECT cartmgr.new_cart_id(UPPER(?)) FROM DUAL 
</xsql:set-cookie>

Example 27-4 sets three cookies based on the result of a single SELECT statement.

<?xml version="1.0"?>
<xsql:set-cookie names="paramname1 paramname2 paramname3"
                 connection="demo" xmlns:xsql="urn:oracle-xsql">
  SELECT expression_or_column1, expression_or_column2, expression_or_column3
  FROM table
  WHERE clause_identifying_a_single_row
</xsql:set-cookie>

<xsql:set-page-param>

Purpose

Sets a page-private parameter to a value. The value can be supplied by a combination of static text and other parameter values, or alternatively from the result of a SQL SELECT statement.

Usage Notes

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

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

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

If you need to set multiple session parameter values based on the results of a single SQL statement, instead of using the name attribute, then you can use the names attribute. You can supply a list, delimited by spaces or commas, of one or more session parameter names.

Syntax

The syntax for this action is as follows, where paramname is the name of a parameter and value is a value:

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

Alternatively, you can use the following syntax, where SQL_statement is a SQL SELECT statement and paramname is the name of a parameter:

<xsql:set-page-param nname="paramname">
  SQL_statement
</xsql:set-page-param>

Alternatively, you can use the following syntax, where paramname is the name of a parameter and where expression is an XPath expression:

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

Either the name or the names attribute is required. The value attribute and the contained SQL statement are mutually exclusive.

Attributes

Table 27-13 lists the attributes supported by this action. Attributes in bold are required; all others are optional.

name = "string"

names = "string string ..."

bind-params = "string"

error-param = "string"

ignore-empty-value = "boolean"

quote-array-values = "boolean"

treat-list-as-array = "boolean"

value = "string"

xpath = "XPathExpression"

Examples

Example 27-28 sets multiple parameter values based on the results of a single SQL statement.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="style.xsl"?>
<xsql:set-page-param names="paramname1 paramname2 paramname3"
                     connection="demo" xmlns:xsql="urn:oracle-xsql>
  SELECT expression_or_column1, expression_or_column2, expression_or_column3
  FROM table
  WHERE clause_identifying_a_single_row
</xsql:set-page-param>

Example 27-29 sets the page-level parameter to a value selected from database and then uses it as the value of an xsql:query attribute.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="style.xsl"?>
<page connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:set-page-param name="max-rows-pref">
     SELECT max_rows
     FROM user_profile
     WHERE userid = {@userid}
  </xsql:set-page-param>
  <xsql:query max-rows="{@max-rows-pref}">
    SELECT title, url
    FROM newsstory
    ORDER BY date_entered DESC
  </xsql:query>
</page>

<xsql:set-session-param>

Purpose

Sets an HTTP session-level parameter to a value. The value of the session-level parameter remains for the lifetime HTTP session of the current browser user. The session is controlled by the Web server. The value can be supplied by a combination of static text and other parameter values or from the result of a SQL SELECT statement.

Because this feature is specific to Java servlets, this action is only effective if the XSQL page in which it appears is processed by the XSQL servlet. If this action occurs in an XSQL page processed by the XSQL command-line utility or the XSQLRequest programmatic API, then it does nothing.

Usage Notes

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

To set several session parameter values based on the results of a single SQL statement, do not use the name attribute. Instead, use the names attribute and supply a space-or-comma-delimited list of one or more session parameter names.

Syntax

The syntax for this action is as follows, where paramname is the name of a parameter and where value is a value:

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

Alternatively, you can use the following syntax, where SQL_statement is a SQL SELECT statement and paramname is the name of a parameter:

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

Either the name or the names attribute is required. The value attribute and the contained SQL statement are mutually exclusive.

Attributes

Table 27-14 lists the optional attributes supported by this action. Attributes in bold are required; all others are optional.

name = "string"

names = "string string ..."

bind-params = "string"

error-param = "string"

ignore-empty-value = "boolean"

only-if-unset = "boolean"

quote-array-values = "boolean"

treat-list-as-array = "boolean"

value = "string"

Examples

Example 27-30 sets multiple session parameter values based on the results of a single SELECT statement.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="style.xsl"?>
<page connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:set-session-param names="paramname1 paramname2 paramname3">
    SELECT expression_or_column1, expression_or_column2, expression_or_column3
    FROM   table
    WHERE  clause_identifying_a_single_row
  </xsql:set-session-param>
  <!-- ... -->
</page>

<xsql:set-stylesheet-param>

Purpose

Sets a top-level XSLT stylesheet parameter to a value. The value can be supplied by a combination of static text and other parameter values, or from the result of a SQL SELECT statement. The stylesheet parameter will be set on any stylesheet used during the processing of the current page.

Usage Notes

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

To set several stylesheet parameter values based on the results of a single SQL statement, do not use the name attribute. You can use the names attribute and supply a space-or-comma-delimited list of one or more stylesheet parameter names.

Syntax

The syntax for this action is as follows, where paramname is the name of a parameter and where value is a value:

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

Alternatively, you can use the following syntax, where SQL_statement is a SQL SELECT statement and paramname is the name of a parameter:

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

Either the name or the names attribute is required. The value attribute and the contained SQL statement are mutually exclusive.

Attributes

Table 27-15 lists the optional attributes supported by this action. Attributes in bold are required; all others are optional.

name = "string"

names = "string string ..."

bind-params = "string"

error-param = "string"

ignore-empty-value = "boolean"

value = "string"

Examples

Example 27-31 associate a stylesheet and uses the <xsql:set-stylesheet-param> action element to assign the value of the XSQL page parameter named p_table to the XSLT top-level stylesheet parameter named table.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="style.xsl"?>
<page connname="xmlbook" connection="{@p_connname}">
  <xsql:query null-indicator="yes" xmlns:xsql="urn:oracle-xsql">
  <![CDATA[
    SELECT *
    FROM {@p_table}
    WHERE rownum < 2
  ]]>
  </xsql:query>
  <xsql:set-stylesheet-param name="table" value="{@p_table}"
                             xmlns:xsql="urn:oracle-xsql" />
</page>

<xsql:update-request>

Purpose

Accepts data posted from an XML document or HTML form and uses the XML SQL Utility (XSU) to update the content of an XML document in canonical form from a target table or view.

By combining XSU with XSLT, you can transform XML into the canonical format expected by a given table. Afterward, you can use XSU to update the resulting canonical XML. For a specified database table, the canonical XML form is given by one row of XML output from a SELECT * query against the table.

Syntax

The syntax for this action is as follows:

<xsql:update-request table="table_name"/>

Attributes

Table 27-3 lists the attributes that you can use on the <xsql:update-request> action. Required attributes are in bold.

table = "string"

key_columns = "string string ..."

transform = "URL"

columns = "string"

commit = "boolean"

commit-batch-size = "integer"

date-format = "string"

error-param = "string"

Examples

Example 27-32 parses and transforms the contents of the posted XML document or HTML Form for update.

<?xml version="1.0"?>
<xsql:update-request table="purchase_order"   key-columns="department_id"
                     connection="demo"        transform="doc-to-departments.xsl"
 xmlns:xsql="urn:oracle-xsql/>