Here is How to Unpersist Your Persistent PL/SQL Package Data

Avoid standalone procedures and functions and always use packages to construct your application. That is one of the “best practices” when developing Oracle PL/SQL programs.

Of course, as an Oracle PL/SQL programmer you must be familiar with PL/SQL packages and you know how powerful they are in organizing your functions and enhancing the maintenance of your code.

In this post, I am going to concentrate on one feature that makes PL/SQL packages even more powerful, session persistence.

As a refresher, let’s go through some facts:

• Variables and constants that are not defined within any function or procedure in the package are called package data.
• Package data declared inside the package specification is called public package data.
• Package data declared inside the package body is called private package data.
• Private package data can be accessed only by elements defined in the package itself.
• Public package data can be accessed by the package itself and by any program that can execute that package.
• Package data structures, public and private, act like globals and persist within a single Oracle session or connection.

This “package data session persistence” can be a very handy feature but it can also be a problem in some situations (as my coworker John had discovered). For example, consider the following:

SQL> CREATE OR REPLACE PACKAGE my_pkg
  2  AS
  3     PROCEDURE do_it;
  4  END my_pkg;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY my_pkg
  2  AS
  3     /* Private package data */
  4     TYPE g_rec IS RECORD (
  5        first_name   VARCHAR2 (50),
  6        last_name    VARCHAR2 (50)
  7     );
  8
  9     TYPE g_tab_type IS TABLE OF g_rec
 10        INDEX BY BINARY_INTEGER;
 11
 12     g_tab   g_tab_type;
 13     i       BINARY_INTEGER;
 14
 15     PROCEDURE do_it
 16     AS
 17     BEGIN
 18        i := g_tab.COUNT + 1;
 19        g_tab (i).first_name := 'Eddie';
 20        g_tab (i).last_name := 'Awad';
 21        DBMS_OUTPUT.put_line ('g_tab.count: ' || g_tab.COUNT);
 22     END do_it;
 23  END my_pkg;
 24  /

Package body created.

SQL> set serverout on
SQL>  exec my_pkg.do_it;
g_tab.count: 1

SQL>  exec my_pkg.do_it;
g_tab.count: 2

SQL>  exec my_pkg.do_it;
g_tab.count: 3

SQL> disconn
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0

SQL> connect hr/hr
Connected.

SQL> set serverout on
SQL>  exec my_pkg.do_it;
g_tab.count: 1

SQL>  exec my_pkg.do_it;
g_tab.count: 2

As you can see, because g_tab is a package level variable, its value persists across multiple package calls in the same session. Once disconnected and connected again, g_tab is re-initialized.

But, what if you do not want this behavior. What if you want the data in g_tab to not persist. Well, there are a few ways you can do that:

Use the SERIALLY_REUSABLE pragma:

SQL> CREATE OR REPLACE PACKAGE my_pkg AS
  2
  3    PRAGMA SERIALLY_REUSABLE;
  4
  5    PROCEDURE do_it;
  6
  7  END my_pkg;
  8  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY my_pkg AS
  2
  3    PRAGMA SERIALLY_REUSABLE;
  4
...
 25  END my_pkg;
 26  /

Package body created.

SQL> exec my_pkg.do_it;
g_tab.count: 1

SQL> exec my_pkg.do_it;
g_tab.count: 1

This pragma, which must appear in both the package specification and the body (if one exists), indicates that the package state is needed only for the duration of one call of a program in the package. The global memory for serially reusable packages is pooled in the System Global Area (SGA), not allocated to individual users in the User Global Area (UGA). When the call to the server ends, the memory is returned to the pool. Each time the package is reused, its package level variables are initialized to their default values or to NULL.

Execute DBMS_SESSION.RESET_PACKAGE:

SQL> exec my_pkg.do_it;
g_tab.count: 1

SQL> exec my_pkg.do_it;
g_tab.count: 2

SQL> exec dbms_session.reset_package;

SQL> set serverout on
SQL> exec my_pkg.do_it;
g_tab.count: 1

A call to this built-in procedure frees the memory associated with each of the previously run PL/SQL programs from the session, and, consequently, clears the current values of any package globals and closes any cached cursors.

Execute DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.REINITIALIZE):

SQL> exec my_pkg.do_it;
g_tab.count: 1

SQL> exec my_pkg.do_it;
g_tab.count: 2

SQL> exec dbms_session.modify_package_state(dbms_session.reinitialize);

SQL> set serverout on
SQL>  exec my_pkg.do_it;
g_tab.count: 1

Introduced in Oracle9i, this built-in procedure provides an equivalent of the dbms_session.reset_package capability, but it is an efficient, lighter-weight variant for reinitializing the state of all PL/SQL packages in the session. After calling dbms_session.modify_package_state(dbms_session.reinitialize), packages are reinitialized without actually being freed and recreated from scratch. Instead, the package memory gets reused.

Execute ALTER PACKAGE package name COMPILE:

SQL> exec my_pkg.do_it;
g_tab.count: 1

SQL> exec my_pkg.do_it;
g_tab.count: 2

SQL> alter package my_pkg compile
  2  /

Package altered.

SQL> exec my_pkg.do_it;
g_tab.count: 1

Package data is reinitialized in a session when that package is recompiled.

Finally, you can always explicitly initialize the variable before every use.

To conclude, if the requirement is that all the package data has to be “fresh” every time the package is used within a session, the SERIALLY_REUSABLE pragma makes a fine and better choice.

Sources and resources:

• Using PL/SQL Packages
• SERIALLY_REUSABLE Pragma
• DBMS_SESSION.RESET_PACKAGE procedure
• DBMS_SESSION.MODIFY_PACKAGE_STATE procedure
• ALTER PACKAGE
• Oracle PL/SQL Programming

Related articles:

• Oracle PL/SQL package initialization
• Here’s a Quick Way to Get the Line Number in PL/SQL
• PRAGMAtism in Oracle PL/SQL

 

--------------------------------------------------------------------------------

 

 Patrick Wolf | 04 Apr 2007 1:26 pm

Hi,

I’m just curios, why should anybody use static package variables and on the other side decide to use SERIALLY_REUSABLE?

If I really just want to use them for the call, why not just use local procedure/function variables?

Something else I noticed in you example, why don’t you declare “i” as local variable, does it really have to be static?

Patrick

 

--------------------------------------------------------------------------------

 

 Gary | 04 Apr 2007 3:47 pm

Worth mentioning that “ALTER PACKAGE package name COMPILE” will clear EVERY session’s state for that package, not just the one doing the compile. SERIALLY_REUSABLE is a ‘quick fix’ when you’ve got a package that uses lots of global variables written by someone who didn’t realise about persistance.

--------------------------------------------------------------------------------

•  Eddie Awad | 04 Apr 2007 9:45 pm

  Patrick, here is the scenario that we had and was actually the trigger for this post. There is one package that has many procedures/functions accessing the same associative arrays declared as private global package data. The data in the associative arrays is massaged and copied over to a procedure output parameters. Then, we have a ColdFusion web application that calls that procedure in the package more than one time. in subsequent calls, new records are added, instead of recreated, to the the data (ref cursor) returned by the procedure. This is not what we want. So, we used SERIALLY_REUSABLE to solve this situation.

  Moreover, from the documentation: This SERIALLY_REUSABLE pragma is appropriate for packages that declare large temporary work areas that are used once and not needed during subsequent database calls in the same session.

  You’re right about the “i” variable, it can be declared local to the procedure instead of global to the package. In fact, I could have declared g_tab local as well, however, this was a very simple example to specifically demonstrate package data persistency when variables are declared as package globals.

  Gary, thanks for the clarification. I noticed a strange behavior testing the “alter package … compile” command on my XE database. I opened two SQLPlus sessions. I then called my_pkg.do_it in session1 and session2 multiple times. The data persisted across calls as expected in both sessions. I then issued “alter package my_pkg compile;” in session1. The package data in session1 was reinitialized, but not in sesion2. When I issued “alter package my_pkg compile;” for *the second time* in session1, I got “ORA-04068: existing state of packages has been discarded” when I called my_pkg.do_it in session2 and only then the package data was reinitialized in session2. Hum!

•  

  --------------------------------------------------------------------------------

  Paweł Barut | 05 Apr 2007 12:29 am

  Eddie, To avoid “ORA-04068: existing state of packages has been discarded” I use “DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.REINITIALIZE);”. It is especially important when you use connection pooling. Always when I get connection from pool, first operation I run is is DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.REINITIALIZE);. It reinitializes package variables, so you have clear environment. Overhead of calling DBMS_SESSION.MODIFY_PACKAGE_STATE is minimal, ant it simplifies your development. For example I do not have to restart web server every time I change something in packages. Change is visible at once and I do not get ORA-04068 error. Regards, PaweÅ‚

   

  --------------------------------------------------------------------------------

  andrew | 05 Apr 2007 10:19 am

  Out of interest , Oracle 9iAS (unsure about V10), uses dbms_session.reset_package in mod_plsql by default. This is easily visible in the shared pool or a trace file (or by reading the docs…) http://www.sharemation.com/~dmcmahon/modowa.htm

   

  --------------------------------------------------------------------------------

   

  GurpreetSingh Tuteja | 24 Jul 2007 4:35 pm

  Hi, In the above example of SERIALLY_REUSABLE pragma if i type command ‘exec my_pkg.do_it; my_pkg.do_it’ the answer is for i is 2. I am calling a package twice from another package. The first time all the global variables are reset. But the second the old values still persist. Can you help me??

   

  --------------------------------------------------------------------------------

   

  rohan | 07 Mar 2008 1:02 am

  my question to Barut – i need your help regarding package level variables. I have 1 package lets say package A that calls package B in a loop passing parameters to a stored proc in package B. Package B has package level variable as a table type (as in the example). The requirement is to persist the data being written into the table type between subsequent calls and hence I’ve used the package level variable.

  However the package B keeps on getting uncompiled on its own throwing the error message – “ORA-04068: existing state of packages has been discarded”. How do I resolve this issue with the statement – “DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.REINITIALIZE);” and at same time sticking to the requirement of persisting the data