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.
Best Answer
The response from Oracle is that this is not really a bug because the application is catching the 4068 and handling it, thereby not allowing the normal resolution of the error to occur. Their explanation doesn't seem adequate to me because if the package throws an ORA-01476-Divide By Zero (for example) doing a raise_application_error does not effect whether the ORA-01476 is included in the stack or not.