Multiple Cursor Return from PL/SQL Procedures

Example

Application

An OraParameter object represents a PL/SQL cursor bind variable in a PL/SQL block. This supports usage of PL/SQL procedures containing more than one cursor variable (i.e. CreatePLSQLDynaset supports only one cursor variable) .

An OraParameter object representing a cursor variable should be assigned type ORATYPE_CURSOR using the ServerType property and should be treated as an output variable only. Upon output, the Value property of OraParameter object of type ORATYPE_CURSOR returns a read-only OraDynaset object. This Dynaset object can be treated as any other dynaset object.

Usage

set mydynaset = OraParameters("EmpCursor").Value

You can set the dynaset options and cache parameters for the Dynaset object created from PL/SQL cursors using the DynasetOption property and DynasetCacheParams of the OraParameter object. You should call the above property and method before executing the PL/SQL procedure.

It is better to use the CreateSQL method for executing PL/SQL procedures containing cursors. Depending upon cursor implementation under a PL/SQL stored procedure, the Refresh method on the OraSQLStmt object can result in modified PL/SQL cursors. In such a case these modified cursors are automatically associated with the already existing dynaset object and no new dynaset object is created.

You cannot set the SQL property of the dynaset object, and will raise an error if you try to do so.

Note: PL/SQL stored procedures containing cursors as table parameters are not supported.

You should call the Remove method on the parameter object. This will help in cleaning the dynaset object and local temporary cache files.

You can attach the dynaset object to the Recordset property of Oracle Data Control.