Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Stored Proc w/Multiple Result Sets?

Re: Stored Proc w/Multiple Result Sets?

From: Dan Clamage <clamage_at_mime.dw.lucent.com>
Date: 1997/09/17
Message-ID: <01bcc37a$3a6f4b50$54110b87@clamagent>#1/1

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)

Received on Wed Sep 17 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US