There is a maximum number of result sets that a particular stored procedure can return. The number of result sets returned is at most the number of REF CURSOR OUT arguments for the stored procedure. It can return fewer result sets, but it can never return more.
For the system described in Section 3.4.1.2, there is no maximum number of result sets that can be returned. In the case of Model 1 (in Section 3.4.1.1), the maximum number of result sets that a procedure can return is known, and that the driver can return to Heterogeneous Services, is specified in the stored procedure by the number of REF CURSOR OUT arguments. If, when the stored procedure is executed, fewer result sets than the maximum are returned, then the other REF CURSOR OUT arguments are set to NULL.
Another problem for Model 2 database servers is that result sets must be retrieved in the order in which they were placed on the wire by the database. This prevents Heterogeneous Services from running in cursor mode when connecting to these databases. To access result sets returned by these stored procedures, Heterogeneous Services must be in sequential mode.
In sequential mode, the procedure description returned by the driver contains the following:
All the input arguments of the remote stored procedure
None of the output arguments
One OUT argument of type REF CURSOR (corresponding to the first result set returned by the stored procedure)
The client fetches from this REF CURSOR and then calls the virtual package function DBMS_HS_RESULT_SET.GET_NEXT_RESULT_SET to fetch the REF CURSOR corresponding to the next result set. This function call repeats until all result sets are retrieved. The last result set returned will be the OUT arguments of the remote stored procedure.
The primary limitations of sequential mode are:
Result sets returned by a remote stored procedure must be retrieved in the order in which they were placed on the wire.
When a stored procedure is executed, all result sets returned by a previously executed stored procedure are closed (regardless of whether or not the data was retrieved).
See Also:
Your gateway-specific manual for more information about how result sets are supported through the gateway