Edit

Operations on Functions and Procedures with REF CURSOR Parameters

  • Article

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.

Strongly-typed and weakly-typed REF CURSORs

The Microsoft BizTalk Adapter for Oracle E-Business Suite provides support for strongly-typed and weakly-typed (SYS_REFCURSOR) REF CURSORs that can be passed to PL/SQL procedures and functions as IN and 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 E-Business 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.

See Also

What Operations Can be Performed Using the Adapter?