RE: source of package invalidation

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Thu, 12 Mar 2009 15:13:59 -0400
Message-ID: <D1DC33E67722D54A93F05F702C99E2A903929746_at_usahm208.amer.corp.eds.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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?

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 12 2009 - 14:13:59 CDT