Re: source of package invalidation

From: Nigel Thomas <nigel.cl.thomas_at_googlemail.com>
Date: Thu, 12 Mar 2009 16:49:14 +0000
Message-ID: <53258cd50903120949g54a7bdcclc1d8ba6fd5c283c5_at_mail.gmail.com>



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 - 11:49:14 CDT

Original text of this message