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

A copy of this was sent to "Daniel" <delj_at_flash.net> (if that email address didn't require changing) On 27 Jan 2000 11:50:04 EST, you wrote:

>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
>

Perhaps easier (much easier) is to use a ref cursor though. Much less code, lots less overhead.

See http://osi.oracle.com/~tkyte/ResultSets/index.html

You would just array fetch 25 records at a time from the cursor.

>
>"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.
>>
>>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jan 27 2000 - 11:46:50 CST