Reduce occurrence of ORA-04068 while upgrading PL/SQL packages by moving global variables to Application Context

This article will argue against the use of (stateful) global PL/SQL variables – as they are both a potential problem for stateless web applications and the availability experienced by end users during database upgrades.

In various database releases, Oracle did a lot of work in supporting online redefinition of database objects, striving to a reduction of the impact of changes both in terms of unavailability (through locks) and invalidation of dependent objects. When a table is redefined online, it is accessible to both queries and DML during much of the redefinition process. The table is locked in the exclusive mode only during a very small window that is independent of the size of the table and complexity of the redefinition, and that is completely transparent to users. Through fine grained dependency tracking, the number of occasions on which dependent objects are rendered invalid is reduced to those situations where it is really justified. In database releases prior to 11g, object dependencies were managed at the object level, so altering an object automatically invalidated all dependent objects. Oracle 11g has more granular dependency management, so only changes that directly affect an object will cause an invalidation.

Additionally, the frequency of the appearance of ORA-04068 (existing state of packages has been discarded) was reduced. This message prior to Oracle Database 10g was shown for calls to a package in sessions that already invoked that package whenever that package had been recompiled or when objects it depends on had been recompiled .

Note however that there are still situations where the message will be shown, and correctly so. When state is associated with a package – through the use of global variables in the package – and the package is recompiled itself, because a new version of the package is introduced , the state of the package is lost for all sessions that had created such state.

The figure overhead shows a package THING with a global variable g_value. Three different database sessions have each accessed the package and thereby created associated state for the package. This state is stored in the UGA – global area per session. When package THING is recompiled, the associated state for the three database sessions is wiped out. Their highly useful values of hello world and goodbye world are lost.

Sessions may not have availability issues with the (introduction of the) new version of the package. However, the loss of state because of the upgrade may result in service interruptions. This, combined with the fact that database connection pools such as used from FMW applications do not go together well with session state kept in packages, leads to a suggested practice regarding session related state in the database: use an application context to store such state, rather than global variables in packages.

So instead of:

package body MY_PACK

g_important_global varchar2(1000);

use something like:

package body MY_PACK

function g_important_global

return varchar2

is

begin

return sys_context(‘MY_PACK_CONTEXT’, ‘ g_important_global’);

end;

(and cater for modifying the value of g_important_global). When the package is now recompiled, its state is preserved because its state if stored in an application context that is not affected by the recompilation.

Of course introducing this approach would mean changing quite a bit of existing code in all cases where the global is set/modified. We also need to cater for the fact that application context only stores varchar2 data. However, when uninterrupted service to the users of the application is essential, it is worth considering. And, on the upside there is an interesting bonus in the form of the use of Client Session Based Application Context – where the value of CLIENT_IDENTIFIER is used to link a session to the state in a particular Application Context. This introduces a way to connect sessions on the middle tier to their database held state.