Frequent Error in Oracle Ora-04068: Existing State of Packages Has Been Discarded

Frequent error in Oracle ORA-04068: existing state of packages has been discarded

This one liner actually solved everything:

PRAGMA SERIALLY_REUSABLE;

Be sure that your global variables are stateless to avoid any issues.

Frequent ORA-4068 Error in webLogic 12c after rebuilding the package in Oracle

Finally I am able to solve this issue. It was because of timestamp mismatch error in Oracle Database. Resolution found here

ASP.NET - ORA-04068: existing state of packages has been discarded

Make sure you compile the specification even though there are no changes.

Existing state of package discarded - Oracle

serially_reusable only makes sense for constant package variables.

There is only one way to avoid this error and maintain performance (reset_package is really not a good option). Avoid any package level variables in your PL/SQL packages. Your Oracle's server memory is not the right place to store state.

If something really does not change, is expensive to compute and a return value from a function can be reused over and over without recomputation, then DETERMINISTIC helps in that regard

example: DON'T DO THIS:
varchar2(100) cached_result;

function foo return varchar2 is
begin
  if cached_result is null then
     cached_result:= ... --expensive calc here
  end if; 
 return cached_result;
end foo;

DO THIS INSTEAD

function foo return varchar2 DETERMINISTIC is
begin
  result:=... --expensive calc here
  return result;
end foo;

Deterministic tells Oracle that for a given input, the result does not change, so it can cache the result and avoid calling the function.

If this is not your use case and you need to share a variable across sessions, use a table, and query it. Your table will end up in the buffer cache memory if it is used with any frequency, so you get the in memory storage you desire without the problems of session variables

Effective handling java.sql.SQLException: ORA-04068

This is the standard behavior of Oracle, since it keeps a compiled instance of the packages in the session memory of each session. Because of all package states are valid within a session, (i.e. a package variable can have different value in two database sessions) also the package variable values are bound to a session.

Now if you change a package, Oracle has to re-load the package for all the sessions. So far so good. However as you have a code change, the package variables are getting lost. This is like stopping and restaring an application after a code change - the variables are getting lost.

This graceful exception is showing that now the package variables are getting lost.

Depending on how you're using the package variables, this can cause problems in your application - hence the exception.

---

But in the real life, all package variables are used so that they're initialized on demand (e.g. oldschool caching with plsql tables), and no one shall keep persistent data (for example, content of the shopping cart in a webshop app) in package variables. In fact, package variables - although look persistent - shall be treated as transient with an optimistic persistency between calls.

If you can keep this rule, then you can handle this exception very easily:

• do not change package code :) ok, just kidding
• simply re-run the failed pl/sql call when getting this exception.

I.e.:

• call addCustomer(?,?,?)
• you got a java.sql.SQLException: ORA-04068
• now call again the same addCustomer(?,?,?)

PL/SQL Package invalidated

Background

existing state of packages has been discarded means, that your Package had some sort of state.

This is caused by a global variable stored in your Package Body.

Until 11.2.0.2, constants did also cause this behavior (see documentation).

Since the package has already been used in your session, Oracle assumes that this state is relevant for you. Some of these variables might have different values now, and when you recompile the Body, the values are reset.

This exception is thrown, so that your clients know that they can't rely on those variables any more.

Solutions

• Remove all global variables and constants (before 11gR2) from the Package Body if possible
• Replace global variables by DETERMINISTIC functions (as suggested by this answer)
• Defining packages with PRAGMA SERIALLY_REUSABLE causes Oracle to re-initialize the global variables with every call to the server.
• Close your session and reconnect before calling the package again.
• Reset the state manually (see Paul James' answer)

Why do I keep getting error ORA-04061: existing state of has been invalidated without any invalid objects? (Oracle sql)

The error doesn't mean there are any invalid objects, so your queries shouldn't show any.

This is caused by a stateful package:

If a PL/SQL package declares at least one variable, constant, or cursor, then the package is stateful; otherwise, it is stateless.

You said that "no package-wide variables are defined" but the error suggests there must be something making it stateful.

Each session that references a package item has its own instantiation of that package. If the package is stateful, the instantiation includes its state.

You are seeing the error after you recompiled the package, initially to add your new API (procedure) to it. The behaviour you saw implies your queue process had existing database sessions - possibly several, from a connection pool - and each of those that had previously referenced the package had state.

The package state persists for the life of a session, except in these situations:

• The package is SERIALLY_REUSABLE.
• The package body is recompiled.
  
  If the body of an instantiated, stateful package is recompiled (either explicitly, with the "ALTER PACKAGE Statement", or implicitly), the next invocation of a subprogram in the package causes Oracle Database to discard the existing package state and raise the exception ORA-04068.
  
  After PL/SQL raises the exception, a reference to the package causes Oracle Database to re-instantiate the package, which re-initializes it. Therefore, previous changes to the package state are lost.

That only refers to ORA-04068 but ORA-04061 is in the same class of issues.

The reason I think you probably have a connection pool is that the error didn't always happen. The first call you made after adding your new procedure would have errored, but the session that used would then have new state and wouldn't error again if you happened to reuse that; but if your queue picked a different connection from the pool then its session would then error the first time but then be OK, and so on.

The things you tried just extended the problem - recompiling everything, or just that package to remove the new procedure again, would have discarded the state on the sessions that had already encountered and cleared the error, so they would then hit it again.

Eventually all of the sessions would have hit the error and then had clean state, but that could have taken a while (depending on your pool size and how long it took to cycle through all of the connections); but your attempts to fix it didn't give it a chance.

Just encountering the errors and pushing through them isn't ideal of course. You haven't said how your queue operates but if you're using middleware (like WebLogic) there is usually a way to reset a connection pool, which closes all open connections and starts new ones - with fresh state.

If you know you have stateful packages then performing a reset, or if that's not possible then perhaps restarting the application/queue, may need to be part of the process you use when deploying changes to the package.

When you manually invoked the package that will have been in a new session outside the connection pool, which would have had its own fresh state - so that didn't error, as there was no pre-compilation state to discard.

If you can it might be worth reviewing all of the packages and other objects looking for what is making them stateful, and seeing if that is really necessary.

---

Related Topics