Existing state of package discarded - Oracle

I am using Oracle and I have modified code on some triggers and a package. When I run the script file which modifies the code and try to do an update on a table (which fires the trigger) I am getting Existing State of Package discarded

I am getting a bunch of error

ORA-04068:
ORA-04061:
ORA-04065:
ORA-06512:--Trigger error -- line 50
ORA-04088:

This error is happening only开发者_高级运维 the first time. Any inputs to avoid this would be greatly appreciated. Thank you!

serially_reusable only makes sense for constant package variables.

There is only one way to avoid this error and maintain performance (reset_package is really not a good option). Avoid any package level variables in your PL/SQL packages. Your Oracle's server memory is not the right place to store state.

If something really does not change, is expensive to compute and a return value from a function can be reused over and over without recomputation, then DETERMINISTIC helps in that regard

example: DON'T DO THIS: varchar2(100) cached_result;

function foo return varchar2 is
begin
  if cached_result is null then
     cached_result:= ... --expensive calc here
  end if; 
 return cached_result;
end foo;

DO THIS INSTEAD

function foo return varchar2 DETERMINISTIC is
begin
  result:=... --expensive calc here
  return result;
end foo;

Deterministic tells Oracle that for a given input, the result does not change, so it can cache the result and avoid calling the function.

If this is not your use case and you need to share a variable across sessions, use a table, and query it. Your table will end up in the buffer cache memory if it is used with any frequency, so you get the in memory storage you desire without the problems of session variables

Your script is more than likely caching out-dated code. So, from Michael Pakhanstovs link you can run

DBMS_SESSION.RESET_PACKAGE 

at the beginning of your script or use

PRAGMA SERIALLY_REUSABLE; 

in your script.

Note however the implications of both:

http://download.oracle.com/docs/cd/B13789_01/appdev.101/b10807/13_elems046.htm

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sessio.htm#i1010767

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2298325131459

AskTom on DBMS_SESSION.RESET_PACKAGE:

dbms_session.reset_package, while MUCH faster then logging in and out, has some performance implications (not as fast as not doing it obviously!). That and it'll rip down the entire session state, eg: any opened cursors you have (prepared statements) as well.

AskTom on PRAGMA SERIALLY_REUSABLE:

Its basically saying 'if you use a package and you don't save a state in that package and would like avoid having the persistance of the package in your session -- less memory -- use this'

I had the same problem.

My situation was that I wrote a dynamic creation script for a package, then in the next script line call the procedure from the package. I think you're in the same situation.

The problem I detected was that the timestamp for the package generation was identical to the timestamp of the procedure call. Nowadays, with faster and faster servers ... it happens.

So in the generation script I have introduced dbms_lock.wait(2) after the package creation line.

This got rid of the problem!