Re: Does Oracle support multiple result sets

You can have multiple cursor variables returned from a stored proc but they will be fetched serially, even if you multi-thread from the client.

From Programmer's Guide To The Oracle Call Interface v7.3: "In Oracle Servers through release 7.3, the host can process only one call at a time for a given database connection. Therefore, if multiple threads within an application are sharing the same database connection and can make independent calls, their access to the connection must be serialized. Only one thread at a time may access the connection."

The Oracle 8 docs I have do not make mention of this issue so I am assuming it is the same.

On Thu, 9 Apr 1998 09:22:52 -0400, "Jeff" <jsouthworth_at_dsitopdog.com> wrote:

>So what happens when you issue the 2 selects from within a stored proc? Say
>you want to pass an account number to a stored procedure that pulls info
>from a customer, orders and product table. How would you do this?
>
>Billy Verreynne wrote in message <6gi9se$om7$1_at_hermes.is.co.za>...
>>Jeff wrote in message <6ggef6$s0m$1_at_usenet54.supernews.com>...
>>>Was wondering if Oracle supports multiple result sets? Can I pass an
>>>account number to a stored procedure, then select * from a customer table,
>>>then select * from an orders table. What I'm trying to do is pass an
>>>account number and pull the info from the customer and orders table.
>>
>>
>>Not sure what you mean with "multiple result sets". The SQL language does
>>not support "multiple data sets" resulting from a single SQL statement.
>>
>>Multiple data sets (or tables) can be combined with a JOIN. In this case
>>every invoice row will also contain all the columns from the customer
>table.
>>
>>Another way to combine more than one data set into a single result set via
>>SQL is to UNION the SELECTs. This will however only work if the number of
>>columns and datatypes correspond between the SELECTs sets.
>>
>>What is wrong using two seperate SQL statements, one to supply info from
>the
>>customer table and one to supply the invoices? I fail to see any real
>>technical requirement for trying to do it as a single result set via a
>>stored procedure.
>>
>>regards,
>>Billy
>>
>>
>>
>
>
Received on Fri Apr 10 1998 - 01:59:19 CDT