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

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

You can also use this 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 this syntax:

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

Examples

The following example 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.

Retrieving Stock Quotes

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

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

The following example specifies that the posted XML document is to 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.

Deleting Rows

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

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

The following example inserts the user name stored in the webuser cookie into a request_log table. Using bind variables guards against SQL injection attacks.

Inserting a User Name into a Table

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

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. Simple parameter names and array-parameter names are supported.

Syntax

The syntax for the action is this, where some_name is the value of the name attribute and test_condition is exactly one of the conditions listed in Table 33-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 choose exactly one of the attributes listed in Table 33-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 the following example.

Testing Conditions

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

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

The following example shows how you can invoke UpdateStatus from an XSQL page. The example uses SQL bind variable instead of lexical substitution to prevent the possibility of SQL injection attacks.

Including XML Content Created by a Stored Procedure

<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 the following example. The XSQL processor returns this 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>

Purpose

Includes an XML representation of the name and value of a single parameter. This technique is useful if an associated XSLT stylesheet must 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

The following example uses XPATH to get the value of a parameter and represent it in XML.

Including an XML Representation of a Parameter Value

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

<productid>12345</productid>

You can use an array parameter name to indicate that the value is to be treated as an array, as shown in this example:

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

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

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

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

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

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 must 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 the following example.

Including Request Parameters

<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 the following example.

Including Request Parameters

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

<xsql:include-request-params/>

Attributes

None.

Examples

The following example shows a sample XSQL page that includes all request parameters in the data page.

Including Request Parameters

<?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. The following example shows a fragment of the stylesheet.

Testing for Conditions in a Stylesheet

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

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 common gateway interface (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 this 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 33-7 lists the attributes supported by this action. Required attributes are in bold.

href="URL"

bind-params = "string"

error-param = "string"

Examples

The following example 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.

Including an XML Document

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

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 page-private parameters of the aggregating page 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 33-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

The following example displays an XSQL page that lists discussion forum categories.

Categories.xsql

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

The following example shows how you can include the results of the page in the previous Categories.xsql example into a page that lists the ten most recent topics in the current forum.

TopTenTopics.xsql

<?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 this 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. The following example shows an XSQL page that aggregates Categories.xsql and applies the cats-as-html.xsl stylesheet.

HTMLCategories.xsql

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

The following example shows an XSQL page that aggregates Categories.xsql and applies the cats-as-html.xsl stylesheet for delivering to wireless devices.

WMLCategories.xsql

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

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

The following example parses and transforms the contents of the HTML form parameter xmlfield for database insert.

Inserting XML Contained in an HTML Form Parameter

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

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

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

Attributes

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

The following example parses and transforms the contents of the posted XML document or HTML Form for insert.

Inserting XML Received in a Parameter

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

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:

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

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

The following example shows a simple XSQL page.

Hello World

<?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 the previous example as hello.xsql and execute it in a browser, the XSQL page processor returns this 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 this 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 the following example.

Nested Structure Example

<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 the following example produces an error because the default column name for the calculated expression is an illegal XML element name.

Query with Error

<?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 the following example.

Query with Column Aliasing

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

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 33-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 the following example.

DynCursor PL/SQL Package

CREATE OR REPLACE PACKAGE DynCursor IS
  TYPE ref_cursor IS REF CURSOR;
  FUNCTION DynamicQuery(id NUMBER) RETURN ref_cursor;
END;
CREATE OR REPLACE PACKAGE BODY DynCursor IS
  FUNCTION DynamicQuery(id NUMBER) RETURN ref_cursor IS
    the_cursor ref_cursor;
  BEGIN
    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 the following example.

Executing a REF CURSOR Function

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

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 effective only 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 application programming interface (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 must 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 this 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 33-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

The following example sets the HTTP cookie to the value of the parameter named choice.

Setting a Cookie to a Parameter Value

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

Table 33-5 sets the HTTP cookie to a value selected from the database.

Setting a Cookie to a Database-Generated Value

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

Table 33-6 sets three cookies based on the result of a single SELECT statement.

Setting Three Cookies

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

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 must 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 this 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 this 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 33-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

The following example sets multiple parameter values based on the results of a single SQL statement.

Setting Multiple Page Parameters

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

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

Setting a Parameter to a Database-Generated Value

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

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 web server controls the session. 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 effective only 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, 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 this 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 33-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

The following example sets multiple session parameter values based on the results of a single SELECT statement.

Setting Session Parameters

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

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 is 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 this 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 33-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

The following example associates 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.

Setting a Stylesheet Parameter

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

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:

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

Attributes

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

The following example parses and transforms the contents of the posted XML document or HTML Form for update.

Updating XML Received in a Parameter

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