Avoiding ORA-04068: existing state of packages has been discarded

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.

Advertisements

RETURNING BULK COLLECT INTO

The previous post took a look at the RETURNING INTO clause for DML to obtain details about the row modified. However, the simple RETURNING INTO clause can only handle a single row modification and will throw an exception if the DML affects multiple rows:

SQL>DECLARE
  2     l_child_name child.child_name%TYPE;
  3  BEGIN
  4
  5     DELETE
  6     FROM   child
  7     RETURNING child_name INTO l_child_name;
  8
  9     dbms_output.put_line ('Deleted child ' || l_child_name);
 10  END;
 11  /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5

RETURNING INTO contains a BULK COLLECT option for handling this situation. Rather than populating simple scalar variables the returned values are stored into collections:

SQL>SELECT * FROM child;

  CHILD_ID  PARENT_ID CHILD_NAME
---------- ---------- --------------------------------------------------
         1          1 CHILD_1
         2          2 CHILD_2
         3          2 CHILD_3

SQL>DECLARE
  2     TYPE t_child_name_tab IS TABLE OF child.child_name%TYPE;
  3     l_child_name_tab t_child_name_tab;
  4  BEGIN
  5
  6     DELETE
  7     FROM   child
  8     WHERE  child_id IN (SELECT child_id FROM child)
  9     >RETURNING child_name BULK COLLECT INTO l_child_name_tab;
 10
 11     FOR i IN 1..l_child_name_tab.LAST
 12     LOOP
 13        dbms_output.put_line ('Deleted child ' || l_child_name_tab(i));
 14      END LOOP;
 15  END;
 16  /
Deleted child CHILD_1
Deleted child CHILD_2
Deleted child CHILD_3

PL/SQL procedure successfully completed.

In the above example, we started off with 3 rows in the CHILD table (taken from the previous post). Our PL/SQL defined a collection variable, L_CHILD_NAME_TAB, of a simple scalar type. It then deleted the entries from the table, collecting the names of the deleted entries into this variable.

A few things to note with regard to the BULK COLLECT option:

  • Any existing data in the collection that is BULK COLLECTed into is lost
  • The indices of the collection start at 1 and are incremented by 1, i.e. it’s a dense collection
  • If the DML operation fails to affect any rows then the returned collection is empty

Multiple collections can be used to retrieve different columns:

SQL>DECLARE
  2     TYPE t_child_id_tab IS TABLE OF child.child_id%TYPE;
  3     l_child_id_tab t_child_id_tab;
  4     TYPE t_child_name_tab IS TABLE OF child.child_name%TYPE;
  5     l_child_name_tab t_child_name_tab;
  6  BEGIN
  7
  8     DELETE
  9     FROM   child
 10     RETURNING child_id, child_name BULK COLLECT INTO l_child_id_tab,l_child_name_tab;
 11
 12     FOR i IN 1..l_child_id_tab.LAST
 13     LOOP
 14        dbms_output.put_line ('Deleted child ' || TO_CHAR(l_child_id_tab(i)) ||
 15                              ' - ' || l_child_name_tab(i));
 16      END LOOP;
 17  END;
 18  /
Deleted child 1 - CHILD_1
Deleted child 2 - CHILD_2
Deleted child 3 - CHILD_3

PL/SQL procedure successfully completed.

However, it might be easier to collect the values into a single record type collection:

SQL>DECLARE
  2     TYPE t_child_tab IS TABLE OF child%ROWTYPE;
  3     l_child_tab t_child_tab;
  4  BEGIN
  5
  6     DELETE
  7     FROM   child
  8     RETURNING child_id, parent_id, child_name
  9     BULK COLLECT INTO l_child_tab;
 10
 11     FOR i IN 1..l_child_tab.LAST
 12     LOOP
 13        dbms_output.put_line ('Deleted child ' || TO_CHAR(l_child_tab(i).child_id) ||
 14                              ' - ' || l_child_tab(i).child_name);
 15      END LOOP;
 16  END;
 17  /
Deleted child 1 - CHILD_1
Deleted child 2 - CHILD_2
Deleted child 3 - CHILD_3

PL/SQL procedure successfully completed.

All collection types are supported by BULK COLLECT; nested table, varray and associative array. It’s also possible to mix and match in the one operation (although I’m not sure exactly why you might want to do this…):

SQL>DECLARE
  2     TYPE t_child_id_tab IS TABLE OF child.child_id%TYPE
  3        INDEX BY PLS_INTEGER;
  4     l_child_id_tab t_child_id_tab;
  5
  6     TYPE t_child_name_tab IS TABLE OF child.child_name%TYPE;
  7     l_child_name_tab t_child_name_tab;
  8
  9
 10     TYPE t_parent_id_tab IS VARRAY(20) OF child.parent_id%TYPE;
 11     l_parent_id_tab t_parent_id_tab;
 12  BEGIN
 13
 14     DELETE
 15     FROM   child
 16     RETURNING child_id, parent_id, child_name
 17     BULK COLLECT INTO l_child_id_tab, l_parent_id_tab, l_child_name_tab;
 18
 19     FOR i IN 1..l_child_id_tab.LAST
 20     LOOP
 21        dbms_output.put_line ('Deleted child ' || TO_CHAR(l_child_id_tab(i)) |
 22                              ' - ' || l_child_name_tab(i) ||
 23                              ' - parent ' || TO_CHAR(l_parent_id_tab(i)));
 24      END LOOP;
 25  END;
 26  /
Deleted child 1 - CHILD_1 - parent 1
Deleted child 2 - CHILD_2 - parent 2
Deleted child 3 - CHILD_3 - parent 2

PL/SQL procedure successfully completed.

Keep in mind however that the collected values are stored in the PGA. Collecting millions of entries will consume memory. In days where we have gigabytes of memory on our mobile devices it is easily to overlook such concerns but since the database is typically a shared resource we need to be aware of what our programs are doing. If many sessions all tried to consume large amounts of memory then we can easily impact the overall operation of the database server.