I am trying to resolve the problem we have whenever we recompile a package, and it causes sessions to fail the next time they try to use it.
DBMS_Utility.Format_Error_Stack shows these errors in this sequence:
-- ORA-04061: existing state of has been invalidated
-- ORA-04061: existing state of package "PKG01" has been invalidated
-- ORA-04065: not executed, altered or dropped package "PKG01"
-- ORA-06508: PL/SQL: could not find program unit being called: "PKG01"
We have a web application that calls a set of packages, and there are multiple web sessions on the server, waiting for requests. I am trying to find a way that the web users will not encounter a failure message when we recompile a package. I can issue a dummy package call when starting a new user-request, and when it encounters the above errors, I want it to gracefully start over, without any interaction required from the user, and without anything noticed by the user. Each of those multiple web sessions hits the error. So far the only thing that works is to kill the web sessions.
I have written a code to trap for the error situation, and when encountered, it issues this:
dbms_session.modify_package_state(1);
The problem is, Oracle's documentation on Modify_Package_State reads: "This takes effect after the PL/SQL call that made the current invocation finishes running."
What that means, is that I hit the error repeatedly, until I exit the stored procedure, or package procedure, or anonymous block. Once I return to the top-level (in SQL Plus, waiting for the next keyboard command), I can re-execute my trial, and it all works. I've tried using Execute Immediate and Pragma Autonomous Transaction, but nothing seems to work.
Is there any sequence of steps where I can get my trial to succeed, without returning control to the top level of pl/sql execution? I sure can't find it.