Re: Stored Proc w/Multiple Result Sets?

Gary Fidler wrote:
>
> I am trying to write a simple stored procedure which returns multiple
> result sets. I have done this on Sybase servers and wish to do the same on
> Oracle 7.*. The server whines about needing an INTO clause.
>
> What am I doing wrong?
>
> My example is:
>
> create or replace procedure username.sp_test
> (
> Arg1 varchar2,
> Arg2 varchar2
> )
> AS
> BEGIN
> select
> field1,
> field2,
> field3
> from
> username.table1
> where
> field1 = Arg1 and
> field2 = Arg2;
>
> select
> field1,
> field2,
> field3,
> field4
> from
> username.table2
> where
> field1 = Arg1 and
> field2 = Arg2;
> END;
>
> Thanks
> Gary

If you are trying to return this result set to a D2K app, or some other stored procedure, than you will need either:

1. an INTO clause with variables to pass back to the calling app
2. a referenced cursor that can be read by the calling app

Option 2 is more elegant and much more flexible. See you PL/SQL User's guide for implementation. They look confusing, but are rather simple when you figure them out.

GOOD LUCK!

-- 
"Came for the party, left on the run"

Robert Christenson
Gazette Technologies
robertoc#spam~be~gone#@fyiowa.infi.net