PRAGMA SERIALLY_REUSABLE in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

PRAGMA SERIALLY_REUSABLE in ORACLE with Examples

In this article, I am going to discuss PRAGMA SERIALLY_REUSABLE in Oracle with Examples. Please read our previous article where we discussed the Persistent State of Packages in Oracle. In the previous article, we created a package that has the g_var variable which is a global variable and in the package body, we have changed the global variable g_var with the new value 80. This happened using the persistent state. 

We will use the same package with PRAGMA SERIALLY_REUSABLE. Let’s try to include this in the package and see how this works.

create or replace package Persistent_state
is
PRAGMA SERIALLY_REUSABLE;
g_var number:=10;

procedure update_g_var ( p_no number); 

end;

So, the package specification with the name persistent_state. We have included the PRAGMA SERIALLY_REUSABLE term. The variable g_var is assigned with the value 10. The procedure update_g_var is the procedure that changes the value of g_var. Let’s try to compile this package specification.

The package specification is now compiled. Let’s try to look at the package body.

create or replace package body Persistent_state
is
 PRAGMA SERIALLY_REUSABLE;
      procedure update_g_var ( p_no number)
      is 
     
      begin 
      g_var:=p_no;
      dbms_output.put_line(g_var);
      end;

end;

Here in the package body, the code is exactly the same but we have included the PRAGMA SERIALLY_REUSABLE inside the package body. Let’s try to compile the package body.

So, the package body is also compiled. Whenever the SERIALLY_REUSABLE is used in the package specification we have to use it in the package body as well. Let’s now try to test the package.

execute Persistent_state. update_g_var(90);

Here we are giving the input value as 90 to the procedure update_g_var. Let’s try to execute the statement.

The value of g_var is 90. But once the life of the procedure is compiled the value is reset to the original value. So, when we use the PRAGMA SERIALLY_REUSABLE then the scope of the variable will be only until the life of the procedure.

Note: The persistent state is not persistent for the life of a subprogram call when using PRAGMA SERIALLY_REUSABLE in the package specification. If we use pragma the scope is only for the procedure life.

Let’s try to see if that will be a variable test.

variable test number;
execute :test:=Persistent_state.g_var;

So, the DBMS output is displayed as 90 as the procedure is executed. Let’s now try to see the value of the test by printing it separately.

print test;

We can see the value test is displayed as 10 once the procedure is completed. So, the value scope is between the subprogram life.

In the next article, I am going to discuss Persistent State and Cursor in Oracle with Examples. Here, in this article, I try to explain PRAGMA SERIALLY_REUSABLE in Oracle with Examples. I hope you enjoy this PRAGMA SERIALLY_REUSABLE in Oracle article.