Re: stored procedure variable persistence

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/03/30
Message-ID: <8c0ife$r8d$1@nnrp1.deja.com>#1/1

In article <38e3ef61.1658422534_at_news.erols.com>, jxs_at_wolpoff_nospm_law.com (Jay M. Scheiner) wrote:
> 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.

Packages are the answer. A package will maintain state from call to call.

create package my_pkg
as

   procedure do_something( x out number ); end;
/

create package body my_pkg
as

  global_persistent_counter number default 0;

  procedure do_something( x out number )   is
  begin

      global_persistent_counter := global_persistent_counter+1;
      x := global_persistent_counter;

  end;
end;
/

every time you call that -- it'll return 1 larger number.

> Jay M. Scheiner
> Programmer/Analyst
> Wolpoff & Abramson, LLP
> remove _nospm_ from email address
> Opinions are my own only!
> To email, remove _nospm_ from address.
>

--
Thomas Kyte tkyte_at_us.oracle.com
Oracle Service Industries http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Thu Mar 30 2000 - 00:00:00 CST