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ı