YAPO-4068: Yet Another Post on ORA-04068

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.

How to solve ORA-4068

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.

Here is How to Unpersist Your Persistent PL/SQL Package Data

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.