source: Avoiding ORA-04068: existing state of packages has been discarded | Mark Hoxey (wordpress.com)

If you’re trying to run your database as close to 24×7 as possible, with no downtime for updates, then one thing you will need to avoid is the following error:

ORA-04068: existing state of packages has been discarded

Package state comes about when a package has variables or cursors defined at the package level, as opposed to local variables within procedures and functions. According to the PL/SQL documentation for Oracle 11.2:

The values of the variables, constants, and cursors that a package declares (in either its specification or body) comprise its package state. If a PL/SQL package declares at least one variable, constant, or cursor, then the package is stateful; otherwise, it is stateless.

Whenever a package is recompiled any package state held by a current session is invalidated and ORA-04068 occurs the next time the session tries to use the package again. Subsequent calls to the package by the session will succeed but since the package state has been reset the behaviour of the package code might have been affected. Actually, if the behaviour of the code has not been affected after a reset of the package state then I would argue that the package state is unnecessary and therefore should be removed.

With application connection pools reusing database connections and holding them open for extended periods of time it is quite possible for ORA-04068 to hit you hours or days after a code change.

In the examples that follow we’ll use this simple package; PKG_STATE. It consists of a procedure to set a numeric variable and a function to retrieve that value.

CREATE OR REPLACE PACKAGE pkg_state
AS
   PROCEDURE set_variable (p_number IN NUMBER);
   FUNCTION get_variable
   RETURN NUMBER;
END pkg_state;
/

CREATE OR REPLACE PACKAGE BODY pkg_state
AS
   g_pkg_variable   NUMBER(10);

   PROCEDURE set_variable (p_number IN NUMBER)
   AS
   BEGIN   
      g_pkg_variable := p_number;
   END set_variable;
   
   FUNCTION get_variable
   RETURN NUMBER
   AS
   BEGIN
      RETURN g_pkg_variable;
   END get_variable;

END pkg_state;
/

Before we get into strategies to avoid package state, lets first show ORA-04068 in action using an 11.2 database. In session 1 we’ll call the SET_VARIABLE procedure then compile the package body in session 2 before calling the GET_VARIABLE function in session 1:

SQL1>EXEC pkg_state.set_variable(5)

PL/SQL procedure successfully completed.

SQL2>ALTER PACKAGE pkg_state COMPILE BODY;

Package body altered.

SQL1>SELECT pkg_state.get_variable FROM dual;
SELECT pkg_state.get_variable FROM dual
                                   *
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "DEVELOPER.PKG_STATE" has been invalidated
ORA-04065: not executed, altered or dropped package body "DEVELOPER.PKG_STATE"

It’s quite easy to see how we cannot make code changes without interfering with sessions that are currently using the package.

If we were to run the same query in session 1 immediate after getting ORA-04068 then we get:

SQL1>SELECT pkg_state.get_variable
  2  FROM   dual
  3  /

GET_VARIABLE
------------

After encountering ORA-04068 if we reference the package again then we no longer get the error but, as can be seen above, we’ve had our package variable reset. This behaviour can confuse developers who don’t understand package state; ORA-04068 only occurs on the first reference to a package once its state has been cleared.

So, what can we do to avoid ORA-04068? The following are some potential strategies for you:

• Removal of package variables
• Trap ORA-04068 and retry
• Separate globals package
• Move package variables to a context
• Versioning code via Edition-based Redefinition

Removal of package state

A simple answer to overcome ORA-04068 is to remove package level variables, thereby eliminating package state. Whilst this might seem self evident it’s also quite common to see packages variables that are unnecessary. Poor coding practices and the lack of understanding of the PL/SQL language itself both give rise to scenarios where a developer might create package variables when not required. As a result, I would recommend a review of the stateful packages and determine exactly why they have package state and if it’s necessary.

Trap ORA-04068 and retry

I noted previously that if the behaviour of the code is not impacted by the presence of package state then a package probably shouldn’t have state. Removal of package state would be the preferred option in this scenario but there may be reasons why this isn’t possible. In such a scenario ORA-04068 could be trapped by the calling application code and the operation simply repeated. Just be careful of where the transaction boundaries lie and any non-transactional operations such as writing to files.

Separate globals package

Separating out package variables into their own package, away from the procedural code that manipulates those variables, can provide for a simple but effective solution to ORA-04068 errors. The procedural code will be the code that is updated most frequently so by moving the package state into a separate package the objective of being able to patch the procedural code whilst the database is live is met.

There are some obvious downsides to this strategy though. The package storing the variables is exposed to other code and can therefore be manipulated separately from the main package. A key advantage that packages provide of data encapsulation is lost. Also, since we haven’t actually resolved package state we will still be affected by it whenever there comes a need to modify the variables package.

Dusting off our example package, we move the package variable into a new package, PKG_STATE_VARS. Our original PKG_STATE has the body changed to reference the variable in the new package:

CREATE OR REPLACE PACKAGE pkg_state_vars
AS
   g_pkg_variable   NUMBER(10);
END pkg_state_vars;
/

CREATE OR REPLACE PACKAGE BODY pkg_state
AS
   PROCEDURE set_variable (p_number IN NUMBER)
   AS
   BEGIN   
      pkg_state_vars.g_pkg_variable := p_number;
   END set_variable;
   
   FUNCTION get_variable
   RETURN NUMBER
   AS
   BEGIN
      RETURN pkg_state_vars.g_pkg_variable;
   END get_variable;

END pkg_state;
/

When we run our 2 session example we now find:

SQL1>EXEC pkg_state.set_variable(5)

PL/SQL procedure successfully completed.

SQL2>ALTER PACKAGE pkg_state COMPILE BODY;

Package body altered.

SQL1>SELECT pkg_state.get_variable FROM dual;

GET_VARIABLE
------------
           5

So, we can see that the first session was not interrupted by the code being compiled by another session.

Move variables to a context

Contexts provide a mechanism for storing session variables that are not associated with a package.

Back to our example, we create a new context and associated package and modify it to reference the context:

CREATE OR REPLACE PACKAGE pkg_context
AS
   PROCEDURE set_variable (p_number IN NUMBER);
END pkg_context;
/
   
CREATE OR REPLACE PACKAGE BODY pkg_context
AS
   PROCEDURE set_variable (p_number IN NUMBER)
   AS
   BEGIN
      DBMS_SESSION.SET_CONTEXT('ctx_pkg_context', 'variable', TO_CHAR(p_number));
   END set_variable;
END pkg_context;
/

CREATE OR REPLACE CONTEXT ctx_pkg_context USING pkg_context
/

CREATE OR REPLACE PACKAGE BODY pkg_state
AS
   PROCEDURE set_variable (p_number IN NUMBER)
   AS
   BEGIN   
      pkg_context.set_variable (p_number);
   END set_variable;
   
   FUNCTION get_variable
   RETURN NUMBER
   AS
   BEGIN
      RETURN TO_NUMBER(SYS_CONTEXT('ctx_pkg_context','variable'));
   END get_variable;

END pkg_state;
/

Running our code compilation example we see:

SQL1> EXEC pkg_state.set_variable(5)

PL/SQL procedure successfully completed.

SQL2>ALTER PACKAGE pkg_state COMPILE BODY;

Package body altered.

SQL1> SELECT pkg_state.get_variable
  2  FROM   dual
  3  /

GET_VARIABLE
------------
           5

Again, our first session is not impacted by the compilation of the PKG_STATE code any more.

Keep in mind though that context variables are strings so appropriate interfaces are necessary to prevent data type conversion errors if numeric or date types are required.

Edition-based Redefinition

Oracle 11g Release 2 introduced the ability to create versions of code via edition-based redefinition. This topic is rather large for a simple blog post concerning ORA-04068 so I’ll simply refer you to Tim Hall’s excellent post instead at http://www.oracle-base.com/articles/11g/edition-based-redefinition-11gr2.php.

I will note one thing about edition-based redefinition; it’s rather involved and needs to managed carefully.

Update 30 Jan 2015

I’ve added a new post regarding string constants and package state in Oracle 11.2 and beyond.
Update 6 Sept 2016

I’ve added a new post on using edition-based redefinition to avoid ORA-04068.