Re: Does Oracle support multiple result sets

General pattern

create or replac procedure fred( something IN varchar2) is acct1 varchar2(30); acct2 varchar2(30); begin acct1 := something; select whatever into :acct2 from customers where customer.something = acct1; select * from orders where orders.acct = :acct2; end;

This should be a skeleton of a stored procecedure that does multiple selects. It show the principle.

In article <352dc1cc.2393541_at_news2.new-york.net>,   nickman_at_cybernex.net (Nicholas Whitehead) wrote:
>
> 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
> >>
> >>
> >>
> >
> >
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri Apr 10 1998 - 16:46:53 CDT