Re: How to get multiple rows via a stored procedure

Oracle has an "OLE" product that, I believe, will do what you want...but we don't have it and I've never used it.
What I've done in your situation is to create a PACKAGE. A package maintains state between invocations, so you can open a cursor in a package, return a row, fetch the next row in the next call, ... close the cursor after the last call. You do have to make 1 call per row. Of course you could also SELECT the rows into a temporary table, but this isn't very elegent...especially since Oracle provides no support for TEMP tables (Informix does ;) )

-Adrian
AHands_at_sprynet.com

Price Waterhouse LLP wrote:

> Hello,
>
> I am developing an application in VC++ and accessing the Oracle database
> via ODBC. I have to access the data via stored procedure. I created a
> stored procedure in Oracle as:
> Create stored procedure....
> and in the body
> ()
> is empname employee.name%TYPE;
> Begin
> select name into empname from employee;
> end;
>
> since this will return multiple records the sqlplus complains and can't
> execute it and if I try to execute it via ODBC, I get the same error
> message.
>
> Is this possible at all in Oracle. I have to make my application compatible
> with Access and Oracle. This method does work in Access. Although Access
> stored procedure are Predefined queries.
>
> Thanks in advance.
>
> n_akhtar_at_msn.com
Received on Wed Oct 22 1997 - 00:00:00 CDT