Paul,
>From what I remember, if a package has been marked "invalid" and a user
tries to execute it, it DOES get re-compiled at that time, but does not get
executed. So the FIRST call to an invalid Package changes it's state to
valid. The Second call will actually execute the package.
The only other suggestion I have (other than running UTLRP, or
dbms_utility.compile_schema) would be to write a short SQL script to look at
the ALL_DEPENDENCIES table, looking for all PACKAGES that are dependent on
the package you just re-compiled to compile them for you. This would cut
down the amount of work you would need to do.
Personally, I would test UTLRP to be sure it works for you before I wrote my
own script.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Thursday, June 28, 2001 5:41 PM
To: Multiple recipients of list ORACLE-L
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
