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 [EMAIL PROTECTED] 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: [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
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: [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).
RE: Invalid packages not being recompiled by Oracle
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
RE: Invalid packages not being recompiled by Oracle
Does the owner of the package have a private database link that is utilized by the package? The only person that can compile such a package it the owner. --Michael -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 -- 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: Jenkins, Michael 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).
RE: Invalid packages not being recompiled by Oracle
Title: RE: Invalid packages not being recompiled by Oracle 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- From: Troiano, Paul (CAP, GEFA) [SMTP:[EMAIL PROTECTED]] Sent: Thursday, June 28, 2001 2:56 PM To: Multiple recipients of list ORACLE-L Subject: Invalid packages not being recompiled by Oracle 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 -- 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).
RE: Invalid packages not being recompiled by Oracle
Title: RE: Invalid packages not being recompiled by Oracle
Paul,
Lisa
has the correct answer - you must recompile all dependent items.
This
behavior has been standard since the beginning of PL/SQL.
Actually, if your application could be changed to
capture the error, and just call the package a second time, all would be well -
that is - the package would be compiled by the call, and be
validated.
I
would suggest either runningthe dbms_utility.compile_Schema utility (which
I am not a fan of, as it sometimes dies with a recursive sql error) or running
the script below which will re-compile all invalid objects within the
schema.
hope
this helps.
set
heading offset pagesize 5000set pages 100set serveroutput onexec
dbms_output.enable(50);spool compile.sqldeclarecursor c1
is select 'alter ' || object_type || ' ' || object_name || ' compile;'
out_line from user_objects where status='INVALID'
and object_type in ('PROCEDURE','PACKAGE','FUNCTION','VIEW','TRIGGER')
order by 1;beginfor c1_rec in c1 loop
dbms_output.put_line(c1_rec.out_line);end
loop;dbms_output.put_line('exit');end;/spool
off@compile
Tom Mercadante Oracle Certified Professional
-Original Message-From: Koivu, Lisa
[mailto:[EMAIL PROTECTED]]Sent: Thursday, June 28, 2001
3:20 PMTo: Multiple recipients of list ORACLE-LSubject:
RE: Invalid packages not being recompiled by Oracle
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- From: Troiano, Paul (CAP, GEFA) [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, June 28, 2001 2:56 PM To: Multiple recipients of list ORACLE-L Subject: Invalid packages not being recompiled by Oracle
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 -- 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).
RE: Invalid packages not being recompiled by Oracle
later versions of oracle
$ORACLE_HOME/rdbms/admin/utlrp.sql
joe
[EMAIL PROTECTED] 06/28/01 03:40PM
Paul,
Lisa
has the correct answer - you must recompile all dependent items.
This
behavior has been standard since the beginning of PL/SQL.
Actually, if your application could be changed to
capture the error, and just call the package a second time, all would be well -
that is - the package would be compiled by the call, and be
validated.
I
would suggest either runningthe dbms_utility.compile_Schema utility (which
I am not a fan of, as it sometimes dies with a recursive sql error) or running
the script below which will re-compile all invalid objects within the
schema.
hope
this helps.
set
heading offset pagesize 5000set pages 100set serveroutput onexec
dbms_output.enable(50);spool compile.sqldeclarecursor c1
is select 'alter ' || object_type || ' ' || object_name || ' compile;'
out_line from user_objects where status='INVALID'
and object_type in ('PROCEDURE','PACKAGE','FUNCTION','VIEW','TRIGGER')
order by 1;beginfor c1_rec in c1 loop
dbms_output.put_line(c1_rec.out_line);end
loop;dbms_output.put_line('exit');end;/spool
off@compile
Tom Mercadante Oracle Certified Professional
-Original Message-From: Koivu, Lisa
[mailto:[EMAIL PROTECTED]]Sent: Thursday, June 28, 2001
3:20 PMTo: Multiple recipients of list ORACLE-LSubject:
RE: Invalid packages not being recompiled by Oracle
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- From: Troiano, Paul (CAP, GEFA) [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, June 28, 2001 2:56 PM To: Multiple recipients of list ORACLE-L Subject: Invalid packages not being recompiled by Oracle
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 -- 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).
RE: Invalid packages not being recompiled by Oracle
You may run a script in cron to check invalids and recompile them. It may run once or twice in a day...This problem pertains to custom code... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 28 Jun 2001 11:50:27 -0800 Are you talking about an auto-recompile that happens when a user tries to access a package that is invalid ?? Basically, just like views do ??? I have wanted that for a long time ! Every time we modify one package we always have to go back and do an entire recompile on our system. There are usually over 100 invalid items at that time depending on what we changed. Its a pain. Kevin -Original Message- Sent: Thursday, June 28, 2001 1: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 -- 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: Kevin Lange 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq 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).
RE: Invalid packages not being recompiled by Oracle
Tom, if you do enough patches to oracle, you'll
remember(assuming you as anal as me and really read the patch
instructions).
joe
[EMAIL PROTECTED] 06/28/01 04:26PM
Joe,
I keep forgetting about that
one... :)
Tom Mercadante Oracle Certified Professional
-Original Message-From: JOE TESTA
[mailto:[EMAIL PROTECTED]]Sent: Thursday, June 28, 2001 4:03
PMTo: Multiple recipients of list ORACLE-LSubject: RE:
Invalid packages not being recompiled by Oracle
later versions of oracle
$ORACLE_HOME/rdbms/admin/utlrp.sql
joe
[EMAIL PROTECTED] 06/28/01 03:40PM
Paul,
Lisa
has the correct answer - you must recompile all dependent items.
This
behavior has been standard since the beginning of PL/SQL.
Actually, if your application could be changed to
capture the error, and just call the package a second time, all would be well
- that is - the package would be compiled by the call, and be
validated.
I
would suggest either runningthe dbms_utility.compile_Schema utility
(which I am not a fan of, as it sometimes dies with a recursive sql error) or
running the script below which will re-compile all invalid objects within the
schema.
hope
this helps.
set
heading offset pagesize 5000set pages 100set serveroutput
onexec dbms_output.enable(50);spool
compile.sqldeclarecursor c1 is select 'alter ' ||
object_type || ' ' || object_name || ' compile;' out_line from
user_objects where status='INVALID' and object_type in
('PROCEDURE','PACKAGE','FUNCTION','VIEW','TRIGGER') order by
1;beginfor c1_rec in c1 loop
dbms_output.put_line(c1_rec.out_line);end
loop;dbms_output.put_line('exit');end;/spool
off@compile
Tom Mercadante Oracle Certified Professional
-Original Message-From: Koivu, Lisa
[mailto:[EMAIL PROTECTED]]Sent: Thursday, June 28, 2001
3:20 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: Invalid packages not being recompiled by
Oracle
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- From: Troiano, Paul (CAP, GEFA) [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, June 28, 2001 2:56 PM To: Multiple recipients of list ORACLE-L Subject: Invalid packages not being recompiled by Oracle
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 -- 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] (no
RE: Invalid packages not being recompiled by Oracle
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).
