Re: Invalid packages not being recompiled by Oracle

On Thu, Jun 28, 2001 at 10:56:23AM -0800, Troiano, Paul (CAP, GEFA) wrote:
>
> We have one package A that refers to package B. If package B's body and
> specification are both recompiled by user 1, package A is correctly marked
> as invalid. Another user, user 2, then attempts to execute package A and
> gets the following error stack:
>
> -- ORA-04068: existing state of packages has been discarded
> -- ORA-04061: existing state of package "B" has been invalidated
> -- ORA-04065: not executed, altered or dropped package "B"
> -- ORA-06508: PL/SQL: could not find program unit being called
> -- ORA-06512: at "B", line n ORA-06512: at line 2
>
> User 2 then issues the statement 'alter package A compile;' It compiles
> successfully and user 2 can now execute the package.
>
> Why does Oracle not automatically recompile package A as it should? Since
> user 2 was able to recompile the package in its existing state, Oracle
> should not have failed at doing so. User 2's session instantiation of
> package A (as well as all of their other instantiated packages) should have
> been lost when package A was invalidated.
>
> I understand that two possible options are: (1) Flush the shared pool after
> recompiling; and (2) Manually recompile all dependent packages (such as A).
> The first seems like overkill and will cause performance issues. The second,
> with many dependencies involved, is not an option.
>
> We need to consistently be able to recompile a package that other packages
> are dependent upon without a user recieving the above error stack in
> addition to not impacting performance or without having to recompile all the
> dependent packages.

Having read the other responses, and thinking "odd, I've been doing this for years", I've spotted the source of your confusion: Oracle does automatically recompile package A as it should. ;-)

The problem is that package B declares some package variables, and user 2 has already invoked it at some point in their session.

So when B is recompiled by user 1, the package variables of user 2 will need to be flushed, and reinitialised. This doesn't happen until they reinvoked A, which then produces the exception that you're discovering. This is really just a warning, to tell you that the variables are being flushed (and at this point the reset happens). If the operation is tried again, it'll succeed.

So you could code...

PACKAGE A IS
BEGIN
  ...
  BEGIN
    B.proc_call;
  EXCEPTION
  WHEN STATE_DISCARDED THEN
    B.proc_call;
  END;
  ...
END; Tedious though...

-- 
T.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tommy Wareing
  INET: twareing_at_oup.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).