<xsql:ref-cursor-function>

Executes an arbitrary stored function returning a REF CURSOR and includes the result set in canonical XML format.

XSQL Syntax

<xsql:ref-cursor-function
  [ id-attribute="string" ]
  [ id-attribute-column="string" ]
  [ max-rows="integer" ]
  [ null-indicator="boolean" ]
  [ row-element="string" ]
  [ rowset-element="string" ]
  [ skip-rows="integer" ]
  [ tag-case="string" ]
>
PLSQL
</xsql:ref-cursor-function>

Examples

The following example executes the stored function returning REF Cursor and include the results in XML format:

<xsql:ref-cursor-function max-rows="5" tag-case="lower" null-indicator="yes">
  flight_schedule.flights_left_today_for(UPPER('{@airport}'));
</xsql:ref-cursor-function>

The following example executes the stored procedure DynamicQuery which returns a cursor to the query.

<xsql:ref-cursor-function>
  RefCursorExample.DynamicQuery('{@param1}');
</xsql:ref-cursor-function>

Here is the stored procedure DynamicQuery which returns a cursor to the query:

CREATE OR REPLACE PACKAGE RefCursorExample IS
TYPE ref_cursor IS REF CURSOR;
FUNCTION DynamicQuery(userid VARCHAR2) RETURN ref_cursor;
END;
/

CREATE OR REPLACE PACKAGE BODY RefCursorExample IS
FUNCTION DynamicQuery(userid VARCHAR2) RETURN ref_cursor IS
the_cursor ref_cursor;
my_sal NUMBER := 1;
query VARCHAR2(2000);

BEGIN
IF UPPER(userid) = 'STEVE' THEN
query := 'SELECT ename, sal FROM EMP WHERE ROWNUM < 4';
ELSE
query := 'SELECT dname, deptno FROM DEPT WHERE ROWNUM < 2';
END IF;
OPEN the_cursor FOR query; /* USING var, var, var */
RETURN the_cursor;
END;
END;
/

Description

Executes an arbitrary stored function returning a REF CURSOR and includes the result set in canonical XML format. Use the wizard's Query panel to enter the required PL/SQL code or the name of a stored procedure.

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 in that it can hide the query from direct inspection. See the Examples for more information.

Attributes

• max-rows—(Optional, Integer value) Maximum number of rows to fetch, after optionally skipping the number of rows indicated by the skip-rows attribute. The default value is -1 which fetches all rows.
• skip-rows—(Optional, Integer value) Number of rows to skip before fetching rows from the result set. Can be combined with max-rows for stateless paging through query results.
• rowset-element—(Optional, String value) XML element name to use instead of the default ROWSET element name for the entire rowset of query results. Set to the empty-string (" ") to suppress generating a containing ROWSET element.

Note: If you set both row-element and rowset-elementto a blank value (" ") and perform a a query that returns more than one row, then the parser will return an ill-formed document error.

• row-element—(Optional, String value) XML element name to use instead of the default ROW element name for the entire rowset of query results. Set to the empty-string (" ") to suppress generating a containing ROW element for each row in the result set.
• id-attribute—(Optional, String value) XML attribute name to use instead of the default num attribute for uniquely identifying each row in the result set.
• id-attribute-column—(Optional, String value) Column name whose value should be used in each row as the value of the id-attribute. Default is to use the row count.
• null-indicator—(Optional, Boolean value) Indicates whether to signal that a column's value is NULL by including the NULL="Y" attribute on the element for the column. By default, columns with NULL values are omitted from the output. Valid values are yes and no. Default value is no.
• tag-case—(Optional, String value) Valid values are lower and upper. If not specified, the default is to use the case of column names as specified in the query as corresponding XML element names. Default value is lower.