I’ve had discussions with developers over the last few months about how to deal with the dreaded ORA-04068.
oerr ora 04068 04068, 00000, "existing state of packages%s%s%s has been discarded" // *Cause: One of errors 4060 - 4067 when attempt to execute a stored // procedure. // *Action: Try again after proper re-initialization of any application's // state.
We talked about using the SERIALLY_REUSABLE pragma which is a great solution and is well covered by the post below from Lauren Schneider so I won’t go over it here.
This is a good way of protecting your application from ORA-04068 by changing the source package. But what if you have a package where sometimes the package state needs to be carried forward to a subsequent call and sometimes not?
You can control this behaviour from your calling code also, using the Oracle supplied procedure DBMS_SESSION.MODIFY_PACKAGE_STATE (or DBMS_SESSION.RESET_PACKAGE). These are documented in the Oracle Docs here – MODIFY_PACKAGE_STATE Procedure
An example using 2 different database sessions is below. First we demonstrate hitting the ORA-04068 exception:
-- session 1 -- create a test package create or replace package mypkg as G_var number := 1; function retvar return number; end mypkg; / create or replace package body mypkg as function retvar return number is begin return mypkg.G_var; end retvar; end mypkg; / -- test a call to the package declare L_num number; begin L_num := mypkg.retvar; end; / PL/SQL procedure successfully completed. -- session 2 -- alter the package alter package mypkg compile; -- session 1 -- when we come back to session 1 we have lost our package state declare L_num number; begin L_num := mypkg.retvar; end; / * ERROR at line 1: ORA-04068: existing state of packages has been discarded ORA-04061: existing state of package "NEIL.MYPKG" has been invalidated ORA-04065: not executed, altered or dropped package "NEIL.MYPKG" ORA-06508: PL/SQL: could not find program unit being called: "NEIL.MYPKG" ORA-06512: at line 4
Now we repeat the process but use a call to DBMS_SESSION.REINITIALIZE in order to protect ourselves. We are safe to do this as we know we do not want to carry forward the package state.
-- session 1 declare L_num number; begin L_num := mypkg.retvar; end; / PL/SQL procedure successfully completed. -- session 2 -- alter the package alter package mypkg compile; -- session 1 -- we no longer hit ORA-04068 exec dbms_session.modify_package_state(dbms_session.reinitialize) PL/SQL procedure successfully completed. declare L_num number; begin L_num := mypkg.retvar; end; / PL/SQL procedure successfully completed.
So we can control this behaviour from the source package and from the calling code. Super.
Before posting this I did a quick Google to see if I was duplicating anything (I should really have done this first) and came across this post by Eddie Awads.
This talks about the DBMS_SESSION.REINITIALIZE procedure used in this post and has a good comment from Paweł Barut regarding using this method in web applications.