Re: Return a dataset from a stored procedure?

Joe Kazimierczyk wrote:
>
> Scott Dayton wrote:
> >
> > Does anyone know how or if a stored procedure or function can return a
> > dataset similar to using select statement.
> >
> > ie.
> > Instead of this
> > SELECT * FROM EMP
> > use this:
> > SELECT * FROM <STORED PROCEDURE>
> > --
>
> Would something like this be what you're looking for?
>
> SELECT <STORED_FUNCTION>(EMPNO) FROM EMP;
>
> _________________
> Joe Kazimierczyk
> kazimiej_at_bms.com

Hi,

Well, it's a goot idea but the function only returns one value, and what Scott is looking for is a dataset, i.e. several values or several date rows just as in a select statement.

The mechanism to do that is called "PL/SQL-Tables as IN/OUT-Parameters". A PL/SQL Table is a table like an ordinary Oracle Table. It can be used in stored procedures or functions as an IN/OUT Parameter. Inside the procedure or function you can select data with SELECT INTO etc. into the PL/SQL-Table and then pass in via OUT-Parameter to the caller of the function.

Unfortunately in the current Oracle Version 7.3 there is no way, to match a PL/SQL-Table to a host language construct such as C or C++ structure arrays. In this case you have to divide the PL/SQL-Table into severale scalar vectors to pass them to host arrays.

If anything is interested in this technique please send directly an email. I'll send back sample code or can give more advise.

Marcus Received on Wed Jul 02 1997 - 00:00:00 CDT