RE: source of package invalidation

  • From: "Powell, Mark D" <mark.powell@xxxxxxx>
  • To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 12 Mar 2009 15:13:59 -0400

I agree with Chris.  The real problem is that you are changing objects
that are referenced in stored code while the production system is in
use.  You really should apply object changes during maintenance windows
and then run utlrp to ensure validation.  Any application that uses a
connection pool and traditional Forms applications where the forms call
packaged code are likely to encounter errors if you have recompilation
of heavily used stored code.

-- Mark D Powell -- 
Phone (313) 592-5148 



        From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Nigel Thomas
        Sent: Thursday, March 12, 2009 12:49 PM
        To: oracle-l; Stephens, Chris
        Subject: Re: source of package invalidation
        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 pool.
        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 "PROJECT.PCKG_CHARGECODE"



                        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: