I have previously discussed package state and avoiding the “ORA-04068: existing state of packages has been discarded” exception. Connor McDonald has pointed out that come Oracle 11.2 constants do not create package state, except when they involve a calculation. This post expands on that by noting that string constants always create package state (thanks to my work colleague Vlad for pointing this out, after finding out the hard way…).
First up, let’s take a look at how numeric constants don’t create package state. We start with the simplest of packages compiled in session 1:
CREATE OR REPLACE PACKAGE pkg_state AS c_number CONSTANT NUMBER(5) := 123; END pkg_state;
and in another session, session 2, we run a short program that makes reference to the package constant:
SET SERVEROUTPUT ON BEGIN dbms_output.put_line ('Package value is: ' || TO_CHAR(pkg_state.c_number)); END;
Not surprisingly we get the output:
Package value is: 123
If we go back to session 1 and recompile the package changing the constant to a different value:
CREATE OR REPLACE PACKAGE pkg_state AS c_number CONSTANT NUMBER(5) := 789; END pkg_state;
and rerun our simple output script back in session 2 we don’t get ORA-04068 but instead our session displays the new value:
Package value is: 789
Now let’s repeat the entire exercise but with a string constant. In session 1:
CREATE OR REPLACE PACKAGE pkg_state AS c_string CONSTANT VARCHAR2(5) := 'ABC'; END pkg_state;
Running this in session 2:
SET SERVEROUTPUT ON BEGIN dbms_output.put_line ('Package value is: ' || pkg_state.c_string); END;
Package value is: ABC
Compiling the package in session 1 to:
CREATE OR REPLACE PACKAGE pkg_state AS c_string CONSTANT CHAR(5) := 'XYZ'; END pkg_state;
gives us the following when we rerun our display routine in session 2:
BEGIN * ERROR at line 1: ORA-04068: existing state of packages has been discarded ORA-04061: existing state of package "DEVELOPER.PKG_STATE" has been invalidated ORA-04065: not executed, altered or dropped package "DEVELOPER.PKG_STATE" ORA-06508: PL/SQL: could not find program unit being called: "DEVELOPER.PKG_STATE" ORA-06512: at line 2
This occurs with Oracle 11.2 and 12.1. So, while in these versions have taken steps to limit constants from creating package state, Connor’s post and this post show that it doesn’t apply to all constants. Hence some care needs to be taken when planning your deployments. You wouldn’t want to be in a situation where you think you don’t have package state but in reality you do. Right Vlad?
If you’re separating the constants into their own package, then adding:
will solve this I think
Hi, thanks for this post.
in our Oracle-database-environment (Version 220.127.116.11.0) I am watching the following behaviour:
If we declare a CONSTANT with a table-column-datatype, e.g.
>> c_number CONSTANT table_test.column_test%TYPE := 5;
the packages becomes “stateful”…
If we are using a “regular” datatype, e.g.
>> c_id CONSTANT PLS_INTEGER := 5;
the package stays “stateless”!!!
This also works for VARCHAR-Types…
Can you confirm this?
Hmmm… I’m not seeing this behaviour. When the constant is anchored to a column type and the column is numeric then the package remains stateless. If the column is a character type then the package becomes “stateful”. However, I’m using 18.104.22.168 (and 22.214.171.124). I’ll see if I can find a 126.96.36.199 database to test.
Reblogged this on Dinesh Ram Kali..
Looks like this error occurs also after ‘grant role to package xxxx’;
Pingback: utPLSQL and ORA-04061 – Global State and Package Invalidation | Clean Database Development