Stored Procedure Results

When you enable the Procedure Returns Results connection option, the driver returns result sets from stored procedures/functions. In addition, SQLGetInfo(SQL_MULT_RESULTS_SETS) returns Y and SQLGetInfo(SQL_BATCH_SUPPORT) returns SQL_BS_SELECT_PROC. If this option is enabled and you execute a stored procedure that does not return result sets, you incur a small performance penalty.

This feature requires that stored procedures be in a certain format. First, a package must be created to define all of the cursors used in the procedure; then, the procedure can be created using the new cursor. For example:

Create or replace package GEN_PACKAGE as

CURSOR G1 is select CHARCOL from GTABLE2;

type GTABLE2CHARCOL is ref cursor return G1%rowtype;

end GEN_PACKAGE;

Create or replace procedure GEN_PROCEDURE1 (

rset IN OUT GEN_PACKAGE.GTABLE2CHARCOL, icol INTEGER) as

begin

open rset for select CHARCOL from GTABLE2

where INTEGERCOL <= icol order by INTEGERCOL;

end;

When executing the stored procedures with result sets, do not include the result set arguments (Oracle ref cursors) in the list of procedure parameters. The result set returned through the ref cursor is returned as a normal ODBC result set.

{call GEN_PROCEDURE1 (?)}

where ? is the parameter for the icol argument.

For more information, refer to your Oracle SQL documentation.