Operations on Functions and Procedures with REF CURSOR Parameters in Oracle Database

A REF CURSOR is a PL/SQL data type that represents a pointer to a server-side result set generated by executing a query. A REF CURSOR type enables input and output streaming of data and is ideal for transferring large amounts of data to and from a PL/SQL code. The Oracle Database adapter provides support for strongly-typed and weakly-typed (SYS_REFCURSOR) REF CURSORs that can be passed to PL/SQL procedures and functions as IN, OUT, or IN OUT parameters.

  • IN REF CURSOR. Adapter clients must use an IN REF CURSOR by supplying a PL/SQL code (as string) that opens REF CURSOR on the Oracle database. The adapter creates variable and sets it the opened REF CURSOR and calls a function or procedure with that variable. Therefore, IN REF CURSOR parameters in PL/SQL stored procedure and functions should be represented as strings that take a PL/SQL code block as input value marking the OUT REF CURSOR variable with a "?".

  • OUT REF CURSOR. OUT REF CURSOR parameters are returned as either strongly-typed or weakly-typed result sets. The type of the result set returned depends on whether the REF CURSOR parameter is declared as a strongly-typed or weakly-typed REF CURSOR in the stored procedure or function definition on the Oracle server.

  • IN OUT REF CURSOR parameters. Because the Oracle Database adapter models IN REF CURSOR parameters as strings and OUT REF CURSOR parameters as complex types, it cannot support a single type for an IN OUT REF CURSOR parameter. For this reason, it treats IN OUT REF CURSOR parameters as two different parameters: an IN parameter in the request message and an OUT parameter in the response message.

    For more information about:

  • Invoking a function or procedure involving REF CURSOR parameters using the BizTalk Server, see Invoke Functions and Procedures with REF CURSORS in Oracle Database using BizTalk Server.

  • Invoking a function or procedure involving REF CURSOR parameters using the WCF service model, see Run Operations Using REF CURSORS in Oracle Database using the WCF Service Model.

  • XML structure for REF CURSORS as supported by the Oracle Database adapter, see Message Schemas for REF CURSORS.

See Also

Connect to Oracle Database using the adapter