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.
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.