Re: Stored Proc w/Multiple Result Sets?

Gary Fidler <gfidler_at_aeq.com> wrote in article <5vk51r$521$1_at_news-2.csn.net>...
> 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.

Let's pretend you're doing this on 7.3. One approach would be to pass in a PL/SQL table which is based on a record type. The procedure fills the array and passes it back along with a record count.

A variation is to have a function which returns the PL/SQL table type. The element indexing for this form is pretty weird. See the Oracle docs.

Whatever you're returning, all "rows" returned better have the same layout. I don't believe the function can return a set of rows into a PL/SQL table variable in a function assignment as in:   set_arr := get_set(non_unq_key); -- variable, return type is user-defined record
But you can put this in a loop, manage the array index variable in the loop and query cursor inside the function. If you're running 7.2 or earlier, this is your only option (7.2 doesn't support record-based arrays). For 7.2, you could pass in a bunch of parallel 1-dimentional arrays and let the procedure fill them (similar to as described above)

• Dan Clamage