Re: Returning many values in a PL/SQL function

In article <6ps4m2$m6u_at_edrn.newsguy.com>, Ulf.pettersson_at_ims.se says...
> Hi,
>
> is there a way to return multiple data from a stored procedure ? (with the
> object option in version 8 it works from other PL/SQL procedures) but how do I
> do if I want to read a set of strings from a VB program ? workarounds ?
>
> Please do answer to ulf.pettersson_at_ims.se
>
> /Thanx in advance
>

I have a similar question. I'm porting an application from MS SQL Server, with a front end in Visual Basic, to run on an Oracle database (with the same front end, as far as possible).

In SQL Server, stored procedures can return result sets (like select statements) so I can base a VB/DAO/ADO recordset object on a stored procedure, and then use rs.MoveNext etc to get at the returned data (or even bind the returned recordset to a VB bound grid).

In Oracle, stored procedures, as far as I can see, CANNOT return values in this way. So, I have to recode. The only possible way of doing this which I can see is to create a stored procedure which populates a "temporary" table (I use quotes, because the table will need to be permanently defined, just temporarily *used*) followed by a recordset based on a select on that table.

Frankly, this is a real pain (there are LOTS of stored procedures in the application). Is there no better way (either a better workaround, or a *real* way of returning data from a stored procedure into a recordset?

Thanks,
Paul Moore. Received on Fri Aug 07 1998 - 10:26:33 CDT