seq.nextval as global variable Oracle-CodePudding

I have three procedures in my package pr_1, pr_2, pr_3. I want to use sequences with same number in all procedures regardless execution order.

How can I accomplish this? When I tried set it as global variable in head spec like:

next_seq_val CONSTANT NUMBER := SEQ.NEXTVAL

I got requrired results:

next_seq_val in pr_1: 22 
next_seq_val in pr_2: 22 
next_seq_val is pr_3: 22

but after I execute the package with same procedures again, I still got the same result:

next_seq_val in pr_1: 22 
next_seq_val in pr_2: 22 
next_seq_val is pr_3: 22

and should be 23.

I also tried to use easy function:

 FUNCTION GetSeqValue
      RETURN NUMBER
      IS
    BEGIN
          RETURN your_sequence.NEXTVAL;
    END;

and put it inside procedures, but of course result is:

next_seq_val in pr_1: 22 
next_seq_val in pr_2: 23 
next_seq_val is pr_3: 24

I just need same number for all procedures but I cannot do it as parameter because I do not know in which order the procedures will be executed and it seems that global variable could be place for that. Thanks for any advice.

CodePudding user response：

You are on the right path with a Package just do not put the value in the header. Create access processes in the package and define the global variable in the body but not within any procedure/function. (see demo)

create or replace package seq_val_pkg is
    procedure set_val;     
    function  next_val return integer;
end seq_val_pkg;   

create or replace package body seq_val_pkg is
    g_seq_val integer := null; 
    
    procedure set_val is
    begin
       g_seq_val := your_sequence.nextval; 
    end set_val ; 
     
    function next_val return integer is
    begin          
        return g_seq_val;
    end next_val;
begin
   -- Pacakge initialization code. This runs once when the pacakge is loaded.
   set_val; 
end seq_val_pkg;