Re: source of package invalidation

  • From: Nigel Thomas <>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>, "Stephens, Chris" <chris_stephens@xxxxxxxxxxxx>
  • Date: Thu, 12 Mar 2009 16:49:14 +0000

Note that the re-compilation itself isn't the problem. The real problem is
that *after* the package has been recompiled in one session (eg A), any *other
*session (eg B, C, D) that used the package (and especially that retain
state - ie package variables) will find that state discarded. That's the
error you are seeing. I have been in development (and test, and once or
twice production) environments where the response of users B, C and D is
themselves to ALTER PACKAGE xxx COMPILE - thus causing the problem to ripple
out of control. Made worse if there are pooled connections in an app server
because each session trips over this the first time it meets the offending
package; this might not be for a considerable time (minutes, hours, even
days) after the original recompilation. It is a bomb waiting to happen (just
once) to any session that has state for that package. The only easy (not
necessarily convenient) way to defuse the bomb is to recycle your connection

BTW - therefore having your exception handler recompile the package doesn't
really help you (it's already recompiled, and you've lost your state) but it
is guaranteed to cause problems to the next session to come along. The more
users of the feature, the higher the probability of this event ... I suspect
the risk is exponential.

(IMHO it is a weakness of PL/SQL that it won't let the two versions of a
package run in parallel until all sessions using the old version have gone
away. Of course that can get complicated very quickly if someone really is
recompiling the package, and if the sessions are long lasting, like in a
connection pool).

Regards Nigel

ORA-04068: existing state of packages has been discarded ORA-04061: existing
>> state of package body "PROJECT.PCKG_CHARGECODE" has been invalidated
>> ORA-04065: not executed, altered or dropped package body
>> We have added an exception handler in the code to recompile the package
>> with “execute immediate ‘alter package xxxx compile’;”  that prevents the
>> user from seeing the error but I would like to prevent this from being
>> necessary in the first place.
>> Also, am I wrong in expecting the compilation to happen automatically?

Other related posts: