How do I create a Variable in PL/SQL whose value persists?

Question

I am writing a Procedure in which I need a value in a variable that I can both change and persist.

To be more specific, this needs to be a counter that starts at 10001 and increments everytime the procedure is called. I am not looking to return this value as it will need to be concatenated to several other values and then that will be returned (used for ID generation for another table).

I looked into using bind variables in SQL PLUS that doesn't seem to help. I would prefer not to create a table for this one value. Is there an option that would allow this?

Answer 1

You probably may need a sequence

create sequence proc_seq start with 1;
create or replace procedure testSeq is
begin
  dbms_output.put_line('Seq is ' || proc_seq.nextVal);
end;

Every time you call the procedure, the value will be increased by 1:

SQL> exec testSeq;
Seq is 1

PL/SQL procedure successfully completed.

SQL> exec testSeq;
Seq is 2

PL/SQL procedure successfully completed.

SQL> exec testSeq;
Seq is 3

PL/SQL procedure successfully completed.

Depending on your Oracle version, you may not be able to use the sequence like I did; in this case, you can use a variable to store the value of the sequence, using the sequence in an SQL statement:

create or replace procedure testSeq is
    s number;
begin
  select proc_seq.nextVal into s from dual;
  dbms_output.put_line('Seq is ' || s);
end;