Package state not re-intialized on raise_application_error

error handlingoracleoracle-11g-r2plsql

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.