dale77011299
asked on
Avoiding unnecessary package state and ORA-04068
We're seeing this error a fair bit in our application:
ORA-04068: existing state of packages has been discarded
Our package body looks like this:
PACKAGE BODY pkg_scheduled_reports IS
daily CONSTANT NUMBER := 0; /* <-- Arrgh! Package state! */
PROCEDURE prc_blah( period IN NUMBER)
IS
BEGIN
IF ( period = daily ) THEN
/* Do stuff */
END IF;
END;
Is there a way to define a alphanumeric constant that does not introduce package state and therefore the possibility of "ORA-04068: existing state of packages has been discarded" occurring if the package is changed? It is a lot nicer to read ( period = daily ) than ( period = 0 ) but not at the cost of having to handle ORA-4068.
thanks
Dale
ORA-04068: existing state of packages has been discarded
Our package body looks like this:
PACKAGE BODY pkg_scheduled_reports IS
daily CONSTANT NUMBER := 0; /* <-- Arrgh! Package state! */
PROCEDURE prc_blah( period IN NUMBER)
IS
BEGIN
IF ( period = daily ) THEN
/* Do stuff */
END IF;
END;
Is there a way to define a alphanumeric constant that does not introduce package state and therefore the possibility of "ORA-04068: existing state of packages has been discarded" occurring if the package is changed? It is a lot nicer to read ( period = daily ) than ( period = 0 ) but not at the cost of having to handle ORA-4068.
thanks
Dale
ASKER
Here's the full stack
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "NCCDB.PKG_SV_REPORTS" has been invalidated
ORA-04065: not executed, altered or dropped package body "NCCDB.PKG_SV_REPORTS"
ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 1
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "NCCDB.PKG_SV_REPORTS" has been invalidated
ORA-04065: not executed, altered or dropped package body "NCCDB.PKG_SV_REPORTS"
ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 1
The real problem is the 4065..Invalid PKG_SV_REPORTS referenced.
Variable definitions does not cause 4068. Usually the one ( one of 4060-4067) will help you identify the problem.
Recompile all invalid packages and dependent packages to resolve the problem.
Variable definitions does not cause 4068. Usually the one ( one of 4060-4067) will help you identify the problem.
Recompile all invalid packages and dependent packages to resolve the problem.
ASKER
Sure, the package has changed (04065). And this does happen if we change the body of the package. Problem is, any session that has called a procedure in the package will have established package session state of "daily := 0". The next time they call it their session will throw 04068 because we *might* have changed daily to be 1 for example. If I don't use the "daily constant number := 0;" line, no exception is thrown, as Oracle has automagically recompiled the stored proc, *and* there is no package session state to be concerned about.
The real problem I'm seeing is that the constants I'm defining are being treated as session state, which seems a bit screwy to me, but there you go.
I'm hoping there is another way to define these constants which will avoid ORA-04068 when we change the package body.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, a function seems to do the trick, but no doubt you'll agree that this is a very heavy handed way of declaring a constant. I'll wait around and see if there is a better solution, otherwise the points are yours (even though I'll probably go with ( period = 0 ) instead of ( period = fnc_daily ) ).
thanks
Hello,
The problem is the discarding of the procedure instantiation in your session's UGA because of DLL on the referenced tables or the package body changes. The fact that Oracle automatically recompiles the procedure does not prevent the discard upon the subsequent reference.
It happens only once, though. So , if you write an exception handler to re-try the call, it (the call) should suceed aftter the re-try.
Another alternative would be to move the constant definition into the specification part.
Rgds.
VC
The problem is the discarding of the procedure instantiation in your session's UGA because of DLL on the referenced tables or the package body changes. The fact that Oracle automatically recompiles the procedure does not prevent the discard upon the subsequent reference.
It happens only once, though. So , if you write an exception handler to re-try the call, it (the call) should suceed aftter the re-try.
Another alternative would be to move the constant definition into the specification part.
Rgds.
VC
Hello,
Additional comments:
1. When I wrote "So , if you write an exception handler to re-try the call, it (the call) should suceed after the re-try", I meant retrying on the client (as in the Java try... catch block). You cannot re-try inside another PL/SQL procedure.
2. Please ignore my second suggestion -- the state, of course, is still there
Rgds.
VC
Additional comments:
1. When I wrote "So , if you write an exception handler to re-try the call, it (the call) should suceed after the re-try", I meant retrying on the client (as in the Java try... catch block). You cannot re-try inside another PL/SQL procedure.
2. Please ignore my second suggestion -- the state, of course, is still there
Rgds.
VC
ASKER
Thanks VC, we don't want to have to change the client to handle the exception. The reason being we really don't care about the package state. I'd much rather it just went away ;)
As another suggestion, you can define the constants in a separate package which is never recompiled.
Rgds.
VC
Rgds.
VC
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You should NOT be frequently recompiling packages in your production system, and so there is no need to handle this error. Changes to production code should be released in a controlled manner.
ASKER
I agree that we shouldn't be frequently recompiling our code, but this happens today for reasons our DBA has yet to explain.
Even if the recompile only happens every month or so as we role out new functionality, I wouldn't like one of our clients to try and run a report and get an error caused by this exception.
One thought is are you actually maintaining your package state beyond when you need it.
If the client session has finished with it (eg returned to the main menu), can you use DBMS_SESSION.RESET_PACKAGE
Apart from that, if your DBA insists on recompiling package specs on a whim, then you are going to continually get Invalid state errors.
If the client session has finished with it (eg returned to the main menu), can you use DBMS_SESSION.RESET_PACKAGE
Apart from that, if your DBA insists on recompiling package specs on a whim, then you are going to continually get Invalid state errors.
ASKER
We really don't need to maintain state at all. Unfortunately declaring constants in our packages gives us some, which introduces the possibility of ORA-04068.
ASKER
I'm going to split the points between "use a function" which works in every situation but is pretty tacky (IMHO) and "put the constant in another package which is not recompiled" which only works if the other package is not recompiled.
One of 4060 - 4067 will always preced 4068.