Question: Is there a way to return a record set (result set) from Oracle's procedures????

From: JFF <jfang_at_cimvision.com>
Date: Thu, 9 Apr 1998 13:49:08 -0700
Message-ID: <6gjb1h$g2r$1@nnrp1.ni.net>

Hi,

    I'm trying to return result sets from Oracle's procedures but I'm having a tough time with it.
The way I'm currently doing this is by declaring [OUT] parameters of TABLE type for all the fields I'm returning from my query.

For example:

    type tfField1 is TABLE of char(4);
    type tfField2 is TABLE of char(10);

   CREATE PROCEDURE Test(p_Field1 OUT tfField1, p_Field2 OUT tfField2)     as

        cursor tCur is
            Select Field1, Field2
                From Table1;
        ind    integer;
    begin
        open tCur;
        ind  := 1;
        loop
            fetch tCur into
                p_Field1(ind), pField2(ind);

             exit when tCur%notfound;
             ind := ind + 1;
        end loop;

    end Test;

This method seems awfully inefficient and it's going to be a pain to maintain these procedures. I was wandering if there are other ways to approach this problem. I did see something called the Dynamic PL/SQL which seems like another alternative, but it seems tedious to create and maintain them too.

BTW I'm calling these stored procedures through ODBC.

thanks

    JFF. Received on Thu Apr 09 1998 - 15:49:08 CDT