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.