When you enable the Procedure Returns Results connection option, the driver is able to return 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 permits stored procedures to return ref cursors. 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.
Note: When executing a stored procedure that returns both ref cursors and stored procedures, the driver returns ref cursors first, followed by implicit results.
