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

You may want to try using cursor variables. Instead of sending out the result set (by VALUE), you can open the cursor and pass the cursor variable out. The invoking procedure can then access the values by FETCHING from the cursor variable. This method is by REFERENCE. This is a less cumbersome method of handling a situation like this.
But I am not sure about the ODBC setup.

Try it out and please let me know by e-mail, CM

In article <6gjb1h$g2r$1_at_nnrp1.ni.net>,   "JFF" <jfang_at_cimvision.com> wrote:
>
> 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.
>
>

-----== 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 - 13:16:19 CDT