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.

About these ads

2 thoughts on “YAPO-4068: Yet Another Post on ORA-04068

  1. Neil,
    Great post and very helpful sum up.

    We had issues with this which lead us flush the pool after releases and we were looking for the possible solutions and by your help I can forward this post to the developers to sort the problem

    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s