Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Metalink Note ID 783675.1? –  René Nyffenegger Jan 22 '13 at 20:15
    
@René - The demonstration is interesting and very similar to mine. The difference is that they are discarding the error, while I am doing a raise_application_error with the third parameter set to true true to return the stack. –  Leigh Riffel Jan 22 '13 at 21:37
2  
I'd consider this a bug. Good luck arguing with Oracle on this one.... you'll need it :) I guess unless they make the ORA-04068 have some kind of precedence that doesn't interrupt r_a_e yet still causes the dependant code to recompile, you're stuffed. (Sounds like a difficult fix). –  Phil Jan 22 '13 at 22:59
    
@Phil I think you're right. I'm more likely to get the developers to change the code than Oracle to fix this. –  Leigh Riffel Jan 23 '13 at 15:25

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.