[DB-SIG] Stored Procedures

[Bob Kline]

On Fri, 14 Sep 2001, Matthew T. Kromer wrote:

> Well, from the point of view of the Oracle API, "results" aren't
> fetched at all to stored procedures, rather, they're bound before
> the procedure executes, ...

That's the way SQL Server does it, too.  But the documentation makes it
clear that you cannot to use the bound variables until after all results
sets have been retrieved.  I don't have the Oracle docs in the office
I'm in right now, but I wouldn't be surprised if that's also true for
Oracle.

> ... so before execution is complete, the results have to be transfered
> to the client and stored in the bound variables.

The second half of your sentence does not necessarily follow from the
first half.  For example, an ODBC driver which is using the protocol on
the wire (TDS, for instance) can tell when there are no more results
sets and then retrieve the output parameter values and procedure return
value from the incoming stream, storing them in the bound variables.

The best that a higher-level API can do, if it cannot assume that an
implementation will always have non-invasive access [1] to the
information that there are no more results sets, is to have the client
code explicitly request the output parameter values.  The driver must
assume in this case that the client code has determined that there are
there are no more results sets (either by prior knowledge or by fetching
the results sets until they have been exhausted).

[1] By "non-invasive" I mean that we cannot expect the driver to fetch
and cache all results set data in order to determine when there are no
more results sets.

-- 
Bob Kline
mailto:bkline@rksystems.com
http://www.rksystems.com