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

20 thoughts on “Avoiding ORA-04068: existing state of packages has been discarded

  1. Pingback: An introduction to Application Context | OracleFrontovik

  2. Hi, we have a problem about this and maybe you can tell me why this triggers that ORA-04068 fires.
    this is used from a application web.

    create or replace PACKAGE BODY TESTS IS
    PROCEDURE TEST1(sentence in varchar, result out SYS_REFCURSOR) IS
    BEGIN
    DECLARE
    ret sys_refcursor;
    BEGIN
    open ret for sentence ;
    result := ret;
    END;
    END;
    END TEST1;

    thx for your time.

    • Hi,

      I wasn’t able to reproduce the problem you described under Oracle versions 11.2.0.4 and 12.1.0.2. I used the following code to test the package:

      DECLARE
         l_rec dual%ROWTYPE;
         l_rc  SYS_REFCURSOR;
      BEGIN   
         tests.test1('select * from dual', l_rc);
         LOOP
            FETCH l_rc INTO l_rec;
            EXIT WHEN l_rc%NOTFOUND;
            dbms_output.put_line ('dummy: ' || l_rec.dummy);
         END LOOP;
         CLOSE l_rc;
      END;
      

      As well as a test involving dual, I also ran a similar test against a regular table. No package state exception was thrown.

      What version of Oracle are you using?

      Regards

      Mark

      • Hi,
        First thx for taking your time.
        The problem is that in development we don’t experience the problem, it seems to appears in test machine.
        This procedure is called from a .NET web application and runs on a 10.2.0.5.0 64bit machine.

        thx in advance

      • Hi Aton,

        Some points to check:

        • Do you have any global variables in the package spec? These will cause package to have state and therefore throw the exception if recompiled.
        • Is the package you noted the true source of the package state errors? Your routine is a generic execute SQL query routine. If the application passed in a statement that contained a call to a function in another package then package state might be within that other package. The full error message should identify the package that is throwing the exception though.

        Regards

        Mark

  3. Hi,
    Excellently explained.
    Wonder if you can expand on your statement :
    “With application connection pools reusing database connections …. it is quite possible for ORA-04068 to hit you hours or days after a code change”.

    Why should pooling behave differently…Meaning why does it not hit 04068 once only and succeed next call after state has been reinitialized? What point am I missing?

    Appreciate your explanation.
    Thanks for your time.

    • Hi Jamil,

      Yes, good question, as I didn’t explain myself too well. You are quite correct that once a session has hit ORA-04068 then it will succeed on the next call, regardless of whether connection pooling is involved or not.

      With connection pooling you may well have a number of connections that have not been used for some time and which will be used only when the application gets busy. As an example, the organisation I work for is quite busy on weekends. So, say our application normally uses a pool of 10 connections but on the weekend when we are busy the pool increases to 20 connections. If the pool properties are not set to decrease automatically as the load decreases then those 20 connections remain. Come Monday, the system is less busy so only 10 connections are actively in use. I do a deployment Monday evening and those 10 active sessions hit ORA-04068 quite quickly thereafter. However, the other 10 connections that are not in use won’t hit ORA-04068 until the system gets busy again the following weekend; 5 days after my deployment. This is the type of scenario I was referring to.

      Sure, if you manage a connection pool properly and have it release unused connections then this scenario is not likely to trouble you. My experience is that developers tend to run with settings that are in excess of what is required and so this problem is very real.

      Regards
      Mark

      • Hi Mark,

        It took me some time to wrap everything up with DBA.

        We’re using Oracle Database Native Web Services and we experience once in a while the scenario where, after an object recompil, an ORA-04068 appears and lasts for hours.

        Conclusion with DBA is that the scenario you describe is highly probable as there are hundred of sessions in a pool dedicated to ODNWS – as per its configuration file.

        Solution adopted is to flush the connection pool after each code recompile. Tests conducted show this will not kick busy connections out.

        Again, thank you

        Regards,
        Jamil

  4. Hello Jamil,

    I hope this isnt too late to get an answer for this topic.
    I am really interessted in the “Trap and retry” part because I found that this cant work.

    If you trap this error and DONT reraise it, a second call will also fail.
    I made an entry on Stackoverflow regarding this behaviour:
    http://stackoverflow.com/questions/42156026/still-getting-ora-04068-existing-state-of-packages-has-been-discarded-after-se

    There you can see my testcode.

    • Hi Sebastian,

      You won’t be able to trap the ORA-04068 exception within a single call to Oracle. You’ll need to allow the exception to propagate out to the calling application and have that reissue the Oracle call. As an example, consider running your test within SQL*Plus. Here SQL*Plus is the external application. As you noted, the first call runs correctly and creates the package state. After modifying the package the second call fails with ORA-04068. Re-issuing the command for the third time is successful though. This is all being done on the same Oracle connection. If you could set up SQL*Plus to trap the exception (within SQL*Plus, not within the call it makes to Oracle) then you could have done the third execution automatically.

      Does this make sense?

      Mark

      • Thanks for the response Mark!

        I think I get what you say. I’m just trying to figure out how this applies to me.
        We have forms and (in the future) Apex applications. Would these be regarded as an “external” application?
        Because I know that Apex basically runs in the DB … so maybe this wont work.
        Not sure about Forms though.

        So trapping the Error in a forms call and doing the call again should work basically the same code as I posted on Stackoverflow but not inside SQLPlus but from Forms/APEX)?
        I dont see any alternative instead of Forms/Apex for trapping this error (.
        Am I missing something?

      • Environments, like APEX, cannot rely on package state as each call down to the database may be done on a different connection. Any state established by one call may not be present for the next call. That said, from the sound of it the presence of package state doesn’t impact the operation of your application, which is why you will simply retry an operation should it encounter ORA-04068. If that’s the case then rather than waiting for it to occur you could reset package state in advance. Prior to making a call down to the database call dbms_session.reset_package first as this routine clears package state. It seems like a bit over overkill but it might be an easier solution than trying to intercept ORA-04068 and retrying the operation.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s