Serially Reusable Packages

Serially Reusable packages can be used in scenarios where we dont want the packages to store the global variable through the session. The below code demonstrates the use of PRAGMA SERIALLY_REUSABLE clause in packages. More information is available at Oracle Documentation

SQL> create or replace package test_serially_reusable as
2
3 var1 number;
4 var2 number;
5 procedure setvar1(p_num number);
6 procedure setvar2(p_num number);
7 end test_serially_reusable ;
8 /

Package created.

SQL>
SQL> create or replace package body test_serially_reusable as
2
3 procedure setvar1(p_num number) is
4 begin
5 var1:=p_num ;
6 dbms_output.put_line('var1->'||var1);
7 dbms_output.put_line('var2->'||var2);
8 end;
9 procedure setvar2(p_num number) is
10 begin
11 var2:=p_num ;
12 dbms_output.put_line('var1->'||var1);
13 dbms_output.put_line('var2->'||var2);
14 end;
15 end test_serially_reusable ;
16 /

Package body created.

SQL> show err
No errors.
SQL>
SQL> exec test_serially_reusable.setvar1(100);
var1->100
var2->

PL/SQL procedure successfully completed.

SQL>
SQL> exec test_serially_reusable.setvar2(200);
var1->100
var2->200

PL/SQL procedure successfully completed.

SQL>
SQL> create or replace package test_serially_reusable as
2 pragma serially_reusable;
3 var1 number;
4 var2 number;
5 procedure setvar1(p_num number);
6 procedure setvar2(p_num number);
7 end test_serially_reusable ;
8 /

Package created.

SQL>
SQL> create or replace package body test_serially_reusable as
2 pragma serially_reusable;
3 procedure setvar1(p_num number) is
4 begin
5 var1:=p_num ;
6 dbms_output.put_line('var1->'||var1);
7 dbms_output.put_line('var2->'||var2);
8 end;
9 procedure setvar2(p_num number) is
10 begin
11 var2:=p_num ;
12 dbms_output.put_line('var1->'||var1);
13 dbms_output.put_line('var2->'||var2);
14 end;
15 end test_serially_reusable ;
16 /

Package body created.

SQL>
SQL>
SQL> exec test_serially_reusable.setvar1(100);
var1->100
var2->

PL/SQL procedure successfully completed.

SQL>
SQL> exec test_serially_reusable.setvar2(200);
var1->
var2->200

PL/SQL procedure successfully completed.

begin
test_serially_reusable.setvar2(200);
dbms_output.put_line('newvar2->'||test_serially_reusable.var2);
test_serially_reusable.setvar1(400);
dbms_output.put_line('newvar1->'||test_serially_reusable.var1);
dbms_output.put_line('newvar2->'||test_serially_reusable.var2);
end;
/

begin
dbms_output.put_line('newvar1->'||test_serially_reusable.var1);
dbms_output.put_line('newvar2->'||test_serially_reusable.var2);
end;
/

begin
test_serially_reusable.setvar2(500);
dbms_output.put_line('newvar1->'||test_serially_reusable.var1);
dbms_output.put_line('newvar2->'||test_serially_reusable.var2);
test_serially_reusable.setvar1(600);
dbms_output.put_line('newvar1->'||test_serially_reusable.var1);
dbms_output.put_line('newvar2->'||test_serially_reusable.var2);
end;
/


SQL> spool off