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.