Tuesday, March 20, 2007

ORA-04061: existing state of package has been invalidated

Here is another hit in the parade of the most frequently misunderstood (or underestimated if you prefer) error messages of Oracle.
Impatient readers can jump to entry #1 of the bullet list below and then back here in case you don't understand what i am saying :-)

Understanding the implications of this error message allows you to avoid some unpleasant consequences that may have either a big or small impact on running applications depending on how well you designed the client portion of the application or instructed other developers in doing so, especially if their primary skill is not oracle but some other programming language like C or VisualBasic.

But first of all, let's have a closer look at the real problem, creating first our own set of tools:

Create
Package Test_Pkg as
Function Set_Var (p_val number) return number;
Function Get_Var return number;
a_global_variable number;
End Test_Pkg;
/

Create
Package Body Test_Pkg as
Function Set_Var (
p_val number)
return number
is
begin
a_global_variable := p_val;
return a_global_variable;
end;
--
Function Get_Var
return number
is
begin
return a_global_variable;
end;
-- package initialization
Begin
select 100
into a_global_variable
from dual;
End Test_Pkg;
/

Create
Function test_fn -- a depending object
return number as
begin
return test_pkg.get_var;
end;
/

select test_fn as global_var_value
from dual;

global_var_value
100

Here we have a package with two functions, one sets a global variable and returns it, the other one just returns the current value of the global variable, plus a third non-packaged function called test_fn, that will show us what happens to depending objects.

Note that i included the package initialization section, that normally is present only when you need to do some special operations and you want to be sure they are executed just once, the first time the client accesses the package, either calling a function or executing a procedure. In this case, i wanted to show you that by means of package initialization code you can set some defaults taking their values from tables for increased flexibility. A typical usage would be retrieving the current language in a multi-language application for instance, a thing that normally can be done once, at startup, and it is then used everywhere while the session is active.

It's important to understand that the meaning of global variable refers to one specific session only.
If you open two distinct sessions, each session could have its own copy of the global variable, each one with a different value.
This means that global variables are not meant for passing values in some unorthodox way across sessions!

Before proceeding, open another sql*plus session and execute the following query
select test_pkg.set_var(50) as global_var_value
from dual;

global_var_value
50
Go back now to the previous session and run the following query:

select test_fn as global_var_value
from dual;

global_var_value
100
You see, two sessions using the same global variable, having two distinct values.

Now, suppose that for some reason we need to add a default value to the set_var's parameter p_val.

As i already explained in a previous post, you can't just add the default value in the package body function header, you need to add it to the package specification as well.

Revert to the session where you entered the PL/SQL code and compile the two following modified PL/SQL objects:

Create or Replace
Package Test_Pkg as
Function Set_Var (p_val number default 10) return number;
Function Get_Var return number;
a_global_variable number;
End Test_Pkg;
/

Create or Replace
Package Body Test_Pkg as
Function Set_Var (
p_val number default 10)
return number
is
begin
a_global_variable := p_val;
return a_global_variable;
end;
--
Function Get_Var
return number
is
begin
return a_global_variable;
end;
-- package initialization
Begin
select 100
into a_global_variable
from dual;
End Test_Pkg;
/

select status from user_objects
where object_name = 'TEST_FN';

status
INVALID

Now, go to the other session and execute the SELECT query again:

select test_fn as var_value
from dual;

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "XXX.TEST_PKG"
has been invalidated

ORA-04065: not executed, altered or
dropped package "XXX.TEST_PKG"


execute the query again:
select test_fn as var_value
from dual;

var_value
100
There at least four remarkable things worth mentioning:

  1. The global variable set to 50 was held in the server, in the memory area associated with the session. When the package was modified, Oracle realized that there were "old" copies of the package in some memory area and flushed them. This is why you've got the ORA-04061. Oracle is telling you "look mate, the copy of the program you're running is old, i'll have to dump and reload it with a freshly compiled version, sorry about that!"
  2. The value of the global variable was reset, meaning that the package initialization code ran once more.
  3. If you repeat the operation, you don't get the error again, because the new version of the package has been loaded into memory. This is good, it means that you can trap this specific error condition and retry the operation if you designed your client application in a smart way.
  4. Function test_fn, a depending object of test_pkg was automatically recompiled.
Changing a tiny bit in a package specification can cause extensive damage to all the clients connected, directly or indirectly.

If the change is limited to the package body, the situation is slightly better, because the depending objects will remain valid, but ORA-04061 will be raised if any function or procedure (of the modified package only) is invoked at the client side. Let's see what happens to our depending object if we just comment out the package initialization section:

select status from user_objects
where object_name = 'TEST_FN';

status
VALID

Create or Replace
Package Body Test_Pkg as
Function Set_Var (
p_val number default 10)
return number
is
begin
a_global_variable := p_val;
return a_global_variable;
end;
--
Function Get_Var
return number
is
begin
return a_global_variable;
end;
-- package initialization commented out
-- Begin
-- select 100
-- into a_global_variable
-- from dual;
End Test_Pkg;
/
select status from user_objects
where object_name = 'TEST_FN';

status
VALID

Go to the other session now:
select test_fn as var_value
from dual;

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "XXX.TEST_PKG"
has been invalidated

ORA-04065: not executed, altered or
dropped package "XXX.TEST_PKG"


As you see, the depending object was still valid after modifying the package body, but still we got ORA-04061. The two facts are not necessarily correlated. You might encounter ORA-04061 whether or not the depending object was recompiled, the problem is at the parent's level, not at the child's level.

Now that we have (hopefully) a clear understanding of the core problem, we can tackle the issues related to the application design at the client side.
You must be aware of the consequences of upgrading the package in a running system otherwise you may run the risk of disrupting the operations of dozen of users.

Normally is much better to ask clients to disconnect in a proper way before doing the package upgrade, but it is not always possible. There can be situations where a client is unattended.

With a robust application design, if you know in advance that there can be situations where you need to perform this operation and nobody can close a client session, you may want to handle the exception at the client side, for instance trapping the run-time error in VisualBasic and resubmitting the query, provided you didn't store any vital information in PL/SQL global variables, as shown above.
I mean, a language setting for instance is something that can be reset at almost any time without too much hassle, but other types of volatile information like "last known state was 110" should never be stored in pl/sql global variables.
On the contrary, an application stopping because of a warning message like this one may loose important state information that cannot be reconstructed easily, if at all, or simply must perform some synchronization or initialization step that could take a long time.
For example, i have been working on an application doing heavy image processing and until i managed to convince the VB developers to handle this specific situation, it happened we had to wait 15 minutes every time an embedded pc needed to be restarted just because i upgraded a minor component of the package.
It took less than 10 minutes to create a new version of the software trapping this exception and continuing to work as if nothing had occurred.

See message translations for ORA-04061, ORA-04065, ORA-04068 and search additional resources.



translated messages:

ORA-04068: lo stato esistente dei package è stato eliminato
ORA-04061: lo stato esistente di è stato invalidato

ORA-04068: se ha anulado el estado existente de los paquetes
ORA-04061: el estado existente de ha sido invalidado

ORA-04068: l'estat dels packages s'ha descartat
ORA-04061: s'ha invalidat l'estat actual de

ORA-04068: état de packages existant rejetés
ORA-04061: état existant de invalidé

ORA-04068: Der bestehende Status der Pakete wurde aufgehoben
ORA-04061: Bestehender Status von wurde annulliert

ORA-04068: η υπάρχουσα κατάσταση των πακέτων έχει απορριφθεί
ORA-04061: η υπάρχουσα κατάσταση του έπαψε να είναι έγκυρη

ORA-04068: eksisterende tilstand for pakkerne er kasseret (discard)
ORA-04061: eksisterende tilstand af er ugyldiggjort

ORA-04068: befintligt tillstånd för paket har sorterats bort
ORA-04061: befintlig status för har blivit ogiltigförklarad

ORA-04068: den eksisterende tilstanden til pakkene er forkastet
ORA-04061: den eksisterende tilstanden til er gjort ugyldig

ORA-04068: paketin nykyinen tila on hylätty
ORA-04061: :n nykyinen tila on mitätöity

ORA-04068: a csomagok létezési állapota megszünt
ORA-04061: létezési állapota érvénytelenítődött

ORA-04068: starea existentă a pachetelor a fost revocată
ORA-04061: starea existentă pentru a fost invalidată

ORA-04068: Bestaande status van packages is niet langer in gebruik.
ORA-04061: Bestaande status van is ongeldig gemaakt.

ORA-04068: estado atual dos pacotes foi descartado
ORA-04061: estado existente de foi invalidado

ORA-04068: estado existente de pacotes foi excluído
ORA-04061: estado existente de foi invalidado

ORA-04068: существующее состояние пакетов было сброшено
ORA-04061: существующее состояния стало неприемлемым

ORA-04068: existující stav balíků byl vyřazen
ORA-04061: existující stav byl označen za neplatný

ORA-04068: existujúci stav balíka bol zrušený
ORA-04061: existujúci stav neprešiel kontrolou platnosti

ORA-04068: istniejący stan pakietów został odrzucony
ORA-04061: istniejący stan nie został unieważniony

ORA-04068: paketlerinin içinde bulundukları durum atıldı
ORA-04061: mevcut durumu geçersiz kılındı

13 comments:

Anonymous said...

Very nice layout and helpful information. Thank you for the blog - you've been bookmarked!

-Jeff in California

Byte64 said...

Thanks for your comment Jeff, i appreciated it.

Bye,
Flavio

fils_de_zanga said...

Je suis heureux d'avoire un compte sur ce blog .Je remercie tous membres pour leur collaboration pour soutenir nous les nuls en oracle 9i warehouse builder surquel je travaille et qui me fatique au niveau des mapping deploiement et execution via les errors comme pls-00201,ora-04068,pls-00435

Byte64 said...

Mercí pour ton commentaire.
I don't speak much french but i understand it!

A bientôt,
Flavio

Peter Nosko said...

It's not a solution all of the time, but if you intend to execute a package call and know you don't need any persistence (such as an unattended client = batch job), precede the call with...

EXECUTE DBMS_SESSION.reset_package;

Byte64 said...

Thanks for the tip Peter,
it's amazing to see how many things i still have to learn and you know what? It seems they are going to increase rather than to decrease...

;-)

I guess that the correct usage for this procedure is inside an error trapping block like:

...
exception
when ora-04061 then
dbms_session.reset_package;
end;

or something alike.
While reading the documentation it turns out that from version 9i there is also a similar procedure called dbms_session.modify_package_state which gives you the option of recycling the memory during reinitialization and gives some kind of advantage in terms of performance.

Bye,
Flavio

Jan Matusiewicz said...

I found a workaround for this error:
1. You need to access a package which may change through a synonym
2. Instead of changing the existing package - you create a copy of it and alter the copy
3. You change the synonym so that it points to the new package

Package variables for the session would be initialized but this is not a problem if you use them only as a cache. Moreover, when all sessions which used the primary package finish, you may copy changes from the secondary package to the primary one and apply the next change in the primary package. Thus, you need only 2 copies of package and a synonym.

Byte64 said...

Jan,
what you call workaround is a questionable practice.

First of all this exception is informative, there is nothing that you are really required to do, except retrying the operation, normally.

Basically you are pointing a client to an old version of the software which could possibly do things in the, wrong way.
You must be sure that the new version behaves like the old one (so why did you change it in the first place?).

If you have a client running and then you recompile a package being used, then you want to ensure that each and every client is running the new version of the software, discarding any previous state.

This type of error, if handled, should be dealt with at the client side, that is intercepting the exception, saving whatever needs to be saved and re-trying the operation, if possible.

So, i don't think is a good idea to keep online two versions of the same package just to avoid this exception, with the additional headache of a synonym pointing to either of them.

Well, at least this is what i think, may be someone else disagrees with me :-)

Jan Matusiewicz said...

Byte64,
Thanks for the reply and for the article. It let me understand the origin of this error. I can see your point. In my opinion the value of my workaround depends on the business practices.

1. It is true that this error could be handled on the client side, but programmer who write clients are often unaware of the problem.

2. In the systems I support, package variables are used only for caching. Thus, there is no need for saving the state in these systems. In other system, however, things could be different.

3. I support an ODS with multiple import jobs running simultaneously. Execution of a single job lasts up to a few hour. It is not a problem in this system that previous version would work a few hours more ,but programmer must remember about this behavior.

4. I have however a problem now that if I want to update a package which is used by several import jobs then I cannot do this, because before one job finishes - another starts. I have to kill several working jobs! This is not an action, which you can undertake at any time. Switching to a new version through a synonym should solve the problem.

To sum up, I think that the workaround I stated is useful in cases of some systems, but the programmers need to be aware of the limitations you pointed out.

Anonymous said...

Thanks for sharing...
its just what i needed to learn...

Danny said...

Thanks a ton for this piece! I'm doing the client-end of this scenario and was looking for information on how to trap this exception. This is definitely informative about the cause and a great start, at least!

Onward in the search of how to identify this particular SoapFaultException from others...

On that note, I'm slightly confused as to why you agree with and expand on Peter Nosko's solution but later say it's the client's responsibility in your reply to Jan. I was tempted to just give that sql code to my DB guys as the solution until I read the later response!

I suppose both won't hurt.

Christian Boivin said...

Very interesting information here, but we get this error from the internal mdsys package ... and we don't want to change it! Any suggestions?

Thanks
Christian

Byte64 said...

Christian,
unfortunately I am not very proficient on Oracle Spatial, if the package is invalidated, there must be some DDL activity that is causing it to change state. Off the top of my mind I can't suggest precisely where to look at in order to find the offending code.
I don't know if turning on SQL auditing on MDSYS objects may help.

Flavio

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles