Avoiding unnecessary package state and ORA-04068

dale77011299
dale77011299 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
What is the preceding Oracle error:  one of 4060-4067?
One of 4060 - 4067 will always preced 4068.

Author

Commented:
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

Commented:
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.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Author

Commented:

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.
Commented:
There may not another way to define constants.  Even pragma restrict_references doesn't seem to do the trick.  The following workaround appears to work:

Write a function to return a constant value....

Author

Commented:

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

Commented:
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


 

Commented:
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

Author

Commented:

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 ;)

Commented:
As another suggestion,  you can define the constants in a separate package which is never recompiled.


Rgds.


VC
Commented:
An example:

--x is defined in a separate package which is never recompiled
CREATE OR REPLACE package p2_pkg as
  x constant int := 10;
end;
/

-----
CREATE OR REPLACE package p1_pkg as
  procedure p1(p_v out int);
end;
/

-- the body can be recompiled without causing the invalidation error
CREATE OR REPLACE package body p1_pkg as
  procedure p1(p_v out int) is
  begin
    p_v := p2_pkg.x +2;
  end;
end;
/


Rgds.

VC

Commented:
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.

Author

Commented:

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.

Commented:
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 or DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.FREE_ALL_RESOURCES) [under 9i] to clear up.

Apart from that, if your DBA insists on recompiling package specs on a whim, then you are going to continually get Invalid state errors.

Author

Commented:

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.

Author

Commented:

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial