When a package has state and the header is changed, the first call gets an error stack something like this:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "LRIFFEL.PKG1" has been invalidated
ORA-04065: not executed, altered or dropped package "LRIFFEL.PKG1"
ORA-06508: PL/SQL: could not find program unit being called: "LRIFFEL.PKG1"
ORA-06512: at "LRIFFEL.PKG2", line 7
ORA-06512: at line 1
A subsequent call from the same session re-initializes the package state and therefore runs successfully. The interesting thing is that this behavior seems to change when the caller catches the exception and does a raise_application_error. As expected the raise_application_error is included in the stack, but contrary to expectations, the ORA-04068 is not. Here is an example error stack:
ERROR at line 1:
ORA-20001: Failed
ORA-06512: at "LRIFFEL.PKG2", line 7
ORA-04061: existing state of package "LRIFFEL.PKG1" has been invalidated
ORA-04065: not executed, altered or dropped package "LRIFFEL.PKG1"
ORA-06508: PL/SQL: could not find program unit being called: "LRIFFEL.PKG1"
ORA-06512: at line 1
This doesn't just seem to be a display issue as repeated calls to the procedure continue to get the same error. Only when converting the raise_application_error back to a simple raise (or eliminating the exception block entirely) does the next execution include the ORA-04068 and the following execution succeed.
Can anyone confirm and/or explain this behavior? Here is a recipe for reproducing the behavior:
--Session 1 - Create Objects
create or replace package pkg1 as
vInteger Integer := 7;
procedure procA;
end;
/
create or replace
package body pkg1 as
procedure procA is
begin
DBMS_Output.Put_Line('ProcA');
end;
end;
/
create or replace
package pkg2 as
procedure procB;
end;
/
create or replace
package body pkg2 as
procedure procB is
begin
pkg1.procA;
exception
when others then
raise;
end;
end;
/
--Session 2 - Test execution.
execute pkg2.procB;
--Session 1 - Change package header and thus invalidate package state.
create or replace package pkg1 as
vInteger Integer := 8;
procedure procA;
end;
/
--Session 2 - Observe failure due to changed package state.
execute pkg2.procB;
--Session 2 - Observe success due to re-initialized package state.
execute pkg2.procB;
--Session 1 - Change Raise to Raise_Application_Error.
create or replace
package body pkg2 as
procedure procB is
begin
pkg1.procA;
exception
when others then
raise_application_error(-20001,'Failed',True);
end;
end;
/
--Session 1 - Change package header and thus invalidate package state.
create or replace package pkg1 as
vInteger Integer := 9;
procedure procA;
end;
/
--Session 2 - Observe failure due to changed package state.
execute pkg2.procB;
--Session 2 - Observe unexpected continued failures.
execute pkg2.procB;
execute pkg2.procB;
execute pkg2.procB;
execute pkg2.procB;
execute pkg2.procB;
execute pkg2.procB;
execute pkg2.procB;
--Session 1 - Change back to raise.
create or replace
package body pkg2 as
procedure procB is
begin
pkg1.procA;
exception
when others then
raise;
end;
end;
/
--Session 2 - Observe failure on the first execution.
execute pkg2.procB;
--Session 2 - Observe success.
execute pkg2.procB;
Bug 229349 looks similar except that a trigger is involved.
ORA-04068have some kind of precedence that doesn't interruptr_a_eyet still causes the dependant code to recompile, you're stuffed. (Sounds like a difficult fix). – Phil Jan 22 at 22:59