Re: Can you return a result set from a stored procedure?

If your development environment allows you to access PL/SQL tables you can do the following...
create a package header with a record type and a table type based on the record type. Instantiate a variable in the header with the table type. Code your package body procedures and functions to populate the variable in the header using package.variable.

Each session will have its own copy of the variable. Call the procedure and read the lines from the PL/SQL table. This works great for complex reports. Even if two people run the report at the same time, the result table will be their own.

Daniel
Austin, TX

"John Hamm" <john_at_snapjax.com> wrote in message news:86num4$ela$1_at_news.gate.net...
> I'm trying to return blocks of records from a query, 25 at a time. The
only
> fast way I can think to do this would be to create a cursor in a package
> that runs the query, then create a stored procedure that fetches a record
> from the cursor each time, and run the procedure 25 times. Then, if I
want
> the next 25 record block, I just call it again 25 times, and so on...
>
> But if there was a way to return a recordset in a stored procedrue, I
could
> just use "where rownum < 25", and fetch each time the stored procedure is
> ran...
>
> I know you can do it in SQL Server, it's easy. All you do is:
>
> create procedure test
> ....
> select * from patients
>
> etc.
>
>
Received on Thu Jan 27 2000 - 10:50:04 CST