Re: stored procedure variable persistence

Jay M. Scheiner <jxs_at_wolpoff_nospm_law.com> wrote in message news:38e3ef61.1658422534_at_news.erols.com...
> How can I carry over a result set/varible from one procedure call to
> another?
>
> Say I have a table with 500 entries, that I want to feed to a program
> outside the Oracle server, that is calling my stored procedure
> repeatedly. The first time, I want to return some info based on row
> 1, next time row 2, etc.
>
> Can I keep an internal variable that will keep track of this? My
> guess is I would get row 1, then row 1, etc. as the procedure was
> called repeatedly.
>
> Should I store a database record that has the last row returned? What
> is the performance penalty here (if I make a table with just 1 row,
> with a key for table name and a number as the only other record) and
> then do a update each time.
> Jay M. Scheiner
> Programmer/Analyst
> Wolpoff & Abramson, LLP
> remove _nospm_ from email address
> Opinions are my own only!
> To email, remove _nospm_ from address.

Create a package with your persistent variable on package level, and the procedure inside the package. The variable will be persistent for the duration of your session.
Hth,

Sybrand Bakker, Oracle DBA Received on Thu Mar 30 2000 - 00:00:00 CST