Re: Invalid packages not being recompiled by Oracle

Hi Paul,

I agree completely with you and feel your pain. I have a workaround though, if the changes made to your package does not affect the specification(i.e no new parameters, no new functions/procedures)  really, for changes only to the package body, You can do a ALTER PACKAGE package_name COMPILE BODY This does not invalidate the dependent packages. My thinking is that the dependency is on the specification. Try it out.

HTH,
Niyi
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, June 28, 2001 11:41 PM

> Thanks Lisa and others,
>
> The dbms_utility.compile_schema will work. It is a bit annoying though as
> this involves six schemas and hundreds of packages to be recompiled.
> Fortunatly I do keep control and there are no circular dependencies in the
> packages. I am sending out an order of dependency among the schemas to the
> appropriate people. The recursive error seems to have been fixed when I
> patched to 8.0.5.2.1. Has anyone seen it in 8.1.7?
>
> It seems to be only for one package that this happens. It is larger than
the
> rest at 6000 lines and 250 KB ????
>
> Modifying the application to execute again isn't really feasible in this
> case. There are dozens of packages that refer to this packages. Each one
> would have to be modified and fully regression tested.
>
> As far as standard behavior, Oracle is supposed to (and does in all other
> cases except for this package) automatically recompile any package flagged
> as invalid at execution time. It should only raise an error when the
package
> in question can not be recompiled. In this case, the user can 'alter
package
> package_name compile' and all is fixed, provided the 'user' is privileged
to
> do so. Obviously, in production there aren't many who can do this.
>
> Any thoughts as to how or what could cause this normal behavior to fail?
>
> - Paul
>
>
>
> -----Original Message-----
> Sent: Thursday, June 28, 2001 1:20 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> HI Paul,
>
> By chance can you use dbms_utility.compile_Schema after recompiling? Are
you
> using it already? I know it doesn't answer your quesiton but this package
> is suppossed to follow the dependencies, no matter how odd they are (what
> you are describing below is pretty weird).
>
> HTH
> Lisa Koivu
> Database Bored Administrator
> Ft. Lauderdale, FL, USA
>
> -----Original Message-----
> Sent: Thursday, June 28, 2001 2:56 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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.
>
> Thanks in advance,
>
> - Paul
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> <http://www.orafaq.com>
> --
> Author: Troiano, Paul (CAP, GEFA)
> INET: Paul.Troiano_at_gecapital.com
>
> 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).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Troiano, Paul (CAP, GEFA)
> INET: Paul.Troiano_at_gecapital.com
>
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Niyi Olajide
  INET: orcldba2001_at_hotmail.com

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).