Ошибка ora 04068

We have had this issues for couple of times and for time being, we were compiling schema to resolve this issue temporarily. Over couple of days we were searching for the permanent resolution.

We found below query that showed timestamp difference in our synonym. we recompiled synonym and It worked !!! It’s been almost a week and so far we have no issues. Here is the query that helped in our case.

**

select do.obj# d_obj,do.name d_name, do.type# d_type, po.obj# p_obj,po.name p_name,
to_char(p_timestamp,'DD-MON-YYYY HH24:MI:SS') "P_Timestamp",
to_char(po.stime ,'DD-MON-YYYY HH24:MI:SS') "STIME", 
decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X 
from sys.obj$ do, sys.dependency$ d, sys.obj$ po
where P_OBJ#=po.obj#(+) and D_OBJ#=do.obj# 
and do.status=1 /*dependent is valid*/ 
and po.status=1 /*parent is valid*/ 
and po.stime!=p_timestamp /*parent timestamp not match*/ 
order by 2,1;

**

I hope this helps someone who may be having this issue.

ORA-04068 возможно самое неприятное из существующих в Oracle исключений. В последних версиях сделано довольно много, для снижения частоты его возникновения, но к сожалению, неизбежно при перекомпиляции stateful пакетов оно неизбежно. Итак, посмотрим, что это такое и как с ним жить:

Создаем stateful package:

create or replace package p_test_dependant is
function getValue return varchar2;
procedure setValue(pVALUE in varchar2);
end;
/
create or replace package body p_test_dependant is
g_value varchar2(4000);
function getValue return varchar2
is
begin
return g_value;
end;
procedure setValue(pVALUE in varchar2)
is
begin
g_value := pVALUE;
end;
end;

Создаем второй пакет, из которого будем работать с первым:

create or replace package p_test_main is
procedure work(pVALUE in varchar2);
end;
/
create or replace package body p_test_main is
procedure work(pVALUE in varchar2)
is
begin
p_test_dependant.setValue(pVALUE);
dbms_output.put_line(p_test_dependant.getValue);
exception when others then
dbms_output.put_line(SQLERRM);
end;
end;

Запустим:

begin
p_test_main.work('AA');
end;

Все работает. Из второй сессии перекомпилируем p_test_dependant:

alter package p_test_dependant compile

Еще раз запустим:

Запустим:

begin
p_test_main.work('AA');
end;

--ORA-06508: PL/SQL: could not find program unit being called

Ни повторные запуски, ни перекомпиляция p_test_main не помогут устранить эту ошибку. Она так и будет возникать, пока мы не начнем новую сессию. Повторный запуск из блока исключений не решает эту проблему:

create or replace package p_test_main is
procedure work(pVALUE in varchar2);
end;
/
create or replace package body p_test_main is
ex_state_reset exception;
PRAGMA EXCEPTION_INIT(ex_state_reset, -6508);
procedure work(pVALUE in varchar2)
is
begin
p_test_dependant.setValue(pVALUE);
dbms_output.put_line(p_test_dependant.getValue);
exception
when ex_state_reset then
p_test_dependant.setValue(pVALUE);
dbms_output.put_line(p_test_dependant.getValue);
when others then
dbms_output.put_line(SQLERRM);
end;
end;

begin
p_test_main.work('AA');
end;

/*ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "TEST.P_TEST_DEPENDANT" has been invalidated
ORA-04065: not executed, altered or dropped package "TEST.P_TEST_DEPENDANT"
ORA-06508: PL/SQL: could not find program unit being called: "TEST.P_TEST_DEPENDANT"
ORA-06512: at "TEST.P_TEST_MAIN", line 11
ORA-04061: existing state of package "TEST.P_TEST_DEPENDANT" has been invalidated
ORA-04065: not executed, altered or dropped package "TEST.P_TEST_DEPENDANT"
ORA-06508: PL/SQL: could not find program unit being called: "TEST.P_TEST_DEPENDANT"
ORA-06512: at line 2*/

Но повторный вызов отработает уже без ошибок, как и в случае с неперехваченным исключением. И к сожалению, других вариантов нет. Как утверждает asktom:

The issue here is the test 3 doesn’t raise the ORA-4068 back to the client. This is necessary to reset the package state. As stated in MOS note 1161225.1:

Subsequent attempts to call the package will either succeed if the package was successfully recompiled, or raise the underlying error if it was not. Error messages further down the error stack from the ORA_4068 indicate what prompted the recompilation to occur and may indicate whether a subsequent call will fail. An important proviso is that the ORA-4068 must be unhandled on exit from the subprogram in order for this solution to work. It is only when an unhandled ORA-4068 is returned by PL/SQL to ORACLE that the needed deinstantiations take place

It’s only when you leave ORA-4068 unhandled that Oracle resets the package state and validates the package.

«Exit from subprogram» в данном случае, увы, переход в хост-среду, а не выход из текущего или родительского pl/sql блока. Таким образом, для обработки этого исключения нужен внешний клиент.

P.S. При попытке перекомпилировать зависимый пакет в обработчике исключений возникает ORA-600.

We have had this issues for couple of times and for time being, we were compiling schema to resolve this issue temporarily. Over couple of days we were searching for the permanent resolution.

We found below query that showed timestamp difference in our synonym. we recompiled synonym and It worked !!! It’s been almost a week and so far we have no issues. Here is the query that helped in our case.

**

select do.obj# d_obj,do.name d_name, do.type# d_type, po.obj# p_obj,po.name p_name,
to_char(p_timestamp,'DD-MON-YYYY HH24:MI:SS') "P_Timestamp",
to_char(po.stime ,'DD-MON-YYYY HH24:MI:SS') "STIME", 
decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X 
from sys.obj$ do, sys.dependency$ d, sys.obj$ po
where P_OBJ#=po.obj#(+) and D_OBJ#=do.obj# 
and do.status=1 /*dependent is valid*/ 
and po.status=1 /*parent is valid*/ 
and po.stime!=p_timestamp /*parent timestamp not match*/ 
order by 2,1;

**

I hope this helps someone who may be having this issue.

If you’re trying to run your database as close to 24×7 as possible, with no downtime for updates, then one thing you will need to avoid is the following error:

ORA-04068: existing state of packages has been discarded

Package state comes about when a package has variables or cursors defined at the package level, as opposed to local variables within procedures and functions. According to the PL/SQL documentation for Oracle 11.2:

The values of the variables, constants, and cursors that a package declares (in either its specification or body) comprise its package state. If a PL/SQL package declares at least one variable, constant, or cursor, then the package is stateful; otherwise, it is stateless.

Whenever a package is recompiled any package state held by a current session is invalidated and ORA-04068 occurs the next time the session tries to use the package again. Subsequent calls to the package by the session will succeed but since the package state has been reset the behaviour of the package code might have been affected. Actually, if the behaviour of the code has not been affected after a reset of the package state then I would argue that the package state is unnecessary and therefore should be removed.

With application connection pools reusing database connections and holding them open for extended periods of time it is quite possible for ORA-04068 to hit you hours or days after a code change.

In the examples that follow we’ll use this simple package; PKG_STATE. It consists of a procedure to set a numeric variable and a function to retrieve that value.

CREATE OR REPLACE PACKAGE pkg_state
AS
   PROCEDURE set_variable (p_number IN NUMBER);
   FUNCTION get_variable
   RETURN NUMBER;
END pkg_state;
/

CREATE OR REPLACE PACKAGE BODY pkg_state
AS
   g_pkg_variable   NUMBER(10);

   PROCEDURE set_variable (p_number IN NUMBER)
   AS
   BEGIN   
      g_pkg_variable := p_number;
   END set_variable;
   
   FUNCTION get_variable
   RETURN NUMBER
   AS
   BEGIN
      RETURN g_pkg_variable;
   END get_variable;

END pkg_state;
/

Before we get into strategies to avoid package state, lets first show ORA-04068 in action using an 11.2 database. In session 1 we’ll call the SET_VARIABLE procedure then compile the package body in session 2 before calling the GET_VARIABLE function in session 1:

SQL1>EXEC pkg_state.set_variable(5)

PL/SQL procedure successfully completed.
SQL2>ALTER PACKAGE pkg_state COMPILE BODY;

Package body altered.
SQL1>SELECT pkg_state.get_variable FROM dual;
SELECT pkg_state.get_variable FROM dual
                                   *
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "DEVELOPER.PKG_STATE" has been invalidated
ORA-04065: not executed, altered or dropped package body "DEVELOPER.PKG_STATE"

It’s quite easy to see how we cannot make code changes without interfering with sessions that are currently using the package.

If we were to run the same query in session 1 immediate after getting ORA-04068 then we get:

SQL1>SELECT pkg_state.get_variable
  2  FROM   dual
  3  /

GET_VARIABLE
------------

After encountering ORA-04068 if we reference the package again then we no longer get the error but, as can be seen above, we’ve had our package variable reset. This behaviour can confuse developers who don’t understand package state; ORA-04068 only occurs on the first reference to a package once its state has been cleared.

So, what can we do to avoid ORA-04068? The following are some potential strategies for you:

  • Removal of package variables
  • Trap ORA-04068 and retry
  • Separate globals package
  • Move package variables to a context
  • Versioning code via Edition-based Redefinition

Removal of package state

A simple answer to overcome ORA-04068 is to remove package level variables, thereby eliminating package state. Whilst this might seem self evident it’s also quite common to see packages variables that are unnecessary. Poor coding practices and the lack of understanding of the PL/SQL language itself both give rise to scenarios where a developer might create package variables when not required. As a result, I would recommend a review of the stateful packages and determine exactly why they have package state and if it’s necessary.

Trap ORA-04068 and retry

I noted previously that if the behaviour of the code is not impacted by the presence of package state then a package probably shouldn’t have state. Removal of package state would be the preferred option in this scenario but there may be reasons why this isn’t possible. In such a scenario ORA-04068 could be trapped by the calling application code and the operation simply repeated. Just be careful of where the transaction boundaries lie and any non-transactional operations such as writing to files.

Separate globals package

Separating out package variables into their own package, away from the procedural code that manipulates those variables, can provide for a simple but effective solution to ORA-04068 errors. The procedural code will be the code that is updated most frequently so by moving the package state into a separate package the objective of being able to patch the procedural code whilst the database is live is met.

There are some obvious downsides to this strategy though. The package storing the variables is exposed to other code and can therefore be manipulated separately from the main package. A key advantage that packages provide of data encapsulation is lost. Also, since we haven’t actually resolved package state we will still be affected by it whenever there comes a need to modify the variables package.

Dusting off our example package, we move the package variable into a new package, PKG_STATE_VARS. Our original PKG_STATE has the body changed to reference the variable in the new package:

CREATE OR REPLACE PACKAGE pkg_state_vars
AS
   g_pkg_variable   NUMBER(10);
END pkg_state_vars;
/

CREATE OR REPLACE PACKAGE BODY pkg_state
AS
   PROCEDURE set_variable (p_number IN NUMBER)
   AS
   BEGIN   
      pkg_state_vars.g_pkg_variable := p_number;
   END set_variable;
   
   FUNCTION get_variable
   RETURN NUMBER
   AS
   BEGIN
      RETURN pkg_state_vars.g_pkg_variable;
   END get_variable;

END pkg_state;
/

When we run our 2 session example we now find:

SQL1>EXEC pkg_state.set_variable(5)

PL/SQL procedure successfully completed.
SQL2>ALTER PACKAGE pkg_state COMPILE BODY;

Package body altered.
SQL1>SELECT pkg_state.get_variable FROM dual;

GET_VARIABLE
------------
           5

So, we can see that the first session was not interrupted by the code being compiled by another session.

Move variables to a context

Contexts provide a mechanism for storing session variables that are not associated with a package.

Back to our example, we create a new context and associated package and modify it to reference the context:

CREATE OR REPLACE PACKAGE pkg_context
AS
   PROCEDURE set_variable (p_number IN NUMBER);
END pkg_context;
/
   
CREATE OR REPLACE PACKAGE BODY pkg_context
AS
   PROCEDURE set_variable (p_number IN NUMBER)
   AS
   BEGIN
      DBMS_SESSION.SET_CONTEXT('ctx_pkg_context', 'variable', TO_CHAR(p_number));
   END set_variable;
END pkg_context;
/

CREATE OR REPLACE CONTEXT ctx_pkg_context USING pkg_context
/

CREATE OR REPLACE PACKAGE BODY pkg_state
AS
   PROCEDURE set_variable (p_number IN NUMBER)
   AS
   BEGIN   
      pkg_context.set_variable (p_number);
   END set_variable;
   
   FUNCTION get_variable
   RETURN NUMBER
   AS
   BEGIN
      RETURN TO_NUMBER(SYS_CONTEXT('ctx_pkg_context','variable'));
   END get_variable;

END pkg_state;
/

Running our code compilation example we see:

SQL1> EXEC pkg_state.set_variable(5)

PL/SQL procedure successfully completed.
SQL2>ALTER PACKAGE pkg_state COMPILE BODY;

Package body altered.
SQL1> SELECT pkg_state.get_variable
  2  FROM   dual
  3  /

GET_VARIABLE
------------
           5

Again, our first session is not impacted by the compilation of the PKG_STATE code any more.

Keep in mind though that context variables are strings so appropriate interfaces are necessary to prevent data type conversion errors if numeric or date types are required.

Edition-based Redefinition

Oracle 11g Release 2 introduced the ability to create versions of code via edition-based redefinition. This topic is rather large for a simple blog post concerning ORA-04068 so I’ll simply refer you to Tim Hall’s excellent post instead at http://www.oracle-base.com/articles/11g/edition-based-redefinition-11gr2.php.

I will note one thing about edition-based redefinition; it’s rather involved and needs to managed carefully.

Update 30 Jan 2015

I’ve added a new post regarding string constants and package state in Oracle 11.2 and beyond.
Update 6 Sept 2016

I’ve added a new post on using edition-based redefinition to avoid ORA-04068.

In this article, I will write about resolving  ORA-06508, ORA-04065, ORA-04068 errors.

The cause of the ORA-04068 is when a dependent object in a package is altered by a DDL statement.  When packages are compiled, the copies of the package in the shared pool are marked as invalid.  In the meantime, there is a new copy of the package that recognizes the calling package. As a result, the ORA-04068 occures. This error occures on the first time when it is called. On the second time, it runs successfuly. But in some cases may have to make a small change in our codes to get rid of these errors.

Now let’s do a test on this issue. I build a package as follows talip_test. Declare a global variable at the SPEC of the package.

CREATE OR REPLACE PACKAGE talip_test
IS
global_var NUMBER := 10;

PROCEDURE inner_test_proc;
END;
/

CREATE OR REPLACE PACKAGE BODY talip_test
IS
PROCEDURE inner_test_proc
IS
BEGIN
global_var := global_var + 1;
DBMS_OUTPUT.put_line (‘Variable =’ || global_var);
END;
END;
/

Next we create a procedure that will call this package.

CREATE OR REPLACE PROCEDURE outer_test_proc
AS
err VARCHAR2 (1024);
BEGIN
talip_test.inner_test_proc;
END;
/

We see that it works successfuly on SQL * Plus.

SQL> set serveroutput on

SQL> begin outer_test_proc; end; /

Variable =12

PL/SQL procedure successfully completed.

Now let some change at the SPEC of  the talip_test package. Add second global variable. And re-compile the SPEC + BODY.

CREATE OR REPLACE PACKAGE talip_test
IS
global_var NUMBER := 10;
global_var2 NUMBER := 10;

PROCEDURE inner_test_proc;
END;
/

CREATE OR REPLACE PACKAGE BODY talip_test
IS
PROCEDURE inner_test_proc
IS
BEGIN
global_var := global_var + 1;
DBMS_OUTPUT.put_line (‘Variable =’ || global_var);
END;
END;
/

outer_test_proc  procedure was invalidated. Compile it.

SQL> select status from dba_objects where object_name=’OUTER_TEST_PROC’;

STATUS

——-

INVALID

SQL> alter procedure outer_test_proc compile ;

SQL> select status from dba_objects where object_name=’OUTER_TEST_PROC’;

STATUS

——-

VALID

In this case, the sessions connected to the database receives ORA-04 068 error when they call the procedure. This error is logged only once in the calling package.

SQL>begin outer_test_proc; end; /

begin outer_test_proc; end;

* ERROR at line 1: ORA-04068: existing state of packages has been discarded

ORA-04061: existing state of package body “TALIP.TALIP_TEST” has been invalidated

ORA-04065: not executed, altered or dropped package body “TALIP.TALIP_TEST”

ORA-06508: PL/SQL: could not find program unit being called: “TALIP.TALIP_TEST”

ORA-06512: at “TALIP.OUTER_TEST_PROC”, line 5 ORA-06512: at line 1

If the package executed for the second time in the same session, it runs successfuly.

SQL> set serveroutput on

SQL> /

Variable =12

PL/SQL procedure successfully completed.

Any PL/SQL block can have an exception-handling part, which can have one or more exception handlers.  In this case, the sessions connected to a database receives these errors on each execution. let’s change outer_test_proc procedure as follows.

CREATE OR REPLACE PROCEDURE outer_test_proc
AS
err VARCHAR2 (1024);
BEGIN
talip_test.inner_test_proc;
EXCEPTION
WHEN OTHERS
THEN
err := SUBSTR (SQLERRM, 0, 1000);
DBMS_OUTPUT.put_line (err);
ROLLBACK;
END;
/

Now let talip_test some change at the SPEC. Add a third global variable. And recompile the SPEC + BODY.

CREATE OR REPLACE PACKAGE talip_test
IS
global_var NUMBER := 10;
global_var2 NUMBER := 10;
global_var3 NUMBER := 10;

PROCEDURE inner_test_proc;
END;
/

CREATE OR REPLACE PACKAGE BODY talip_test
IS
PROCEDURE inner_test_proc
IS
BEGIN
global_var := global_var + 1;
DBMS_OUTPUT.put_line (‘Değişken =’ || global_var);
END;
END;
/

Our outer_test_proc procedure was invalidated. Compile it.

SQL> select status from dba_objects where object_name=’OUTER_TEST_PROC’;

STATUS

——-

INVALID

SQL> alter procedure outer_test_proc compile ;

SQL> select status from dba_objects where object_name=’OUTER_TEST_PROC’;

STATUS

——-

VALID

In this case, the sessions connected to the database will receive ORA-06508 error on each execution of outer_test_proc procedure.

SQL> begin outer_test_proc; end; /

ORA-06508: PL/SQL: could not find program unit being called

PL/SQL procedure successfully completed.

SQL> /

ORA-06508: PL/SQL: could not find program unit being called

PL/SQL procedure successfully completed.

Raise the exception in “when others then” block. In this situation, sessions will receive error on the first time. They will not receive any error on the second time.

CREATE OR REPLACE PROCEDURE outer_test_proc
AS
err VARCHAR2 (1024);
BEGIN
talip_test.inner_test_proc;
EXCEPTION
WHEN OTHERS
THEN
err := SUBSTR (SQLERRM, 0, 1000);
DBMS_OUTPUT.put_line (err);
ROLLBACK;
RAISE;
END;
/

Re-execute the procedure. It will run successfuly on the second time.

SQL> begin outer_test_proc; end; /

begin outer_test_proc; end;

* ERROR at line 1: ORA-04068: existing state of packages has been discarded

ORA-04061: existing state of package “TALIP.TALIP_TEST” has been invalidated

ORA-04065: not executed, altered or dropped package “TALIP.TALIP_TEST”

ORA-06508: PL/SQL: could not find program unit being called: “TALIP.TALIP_TEST”

ORA-06512: at “TALIP.OUTER_TEST_PROC”, line 11 ORA-06512: at line 1

SQL> /

PL/SQL procedure successfully completed.

Wishing to be useful …

Talip Hakan Öztürk

  • Ошибка ora 00972 identifier is too long
  • Ошибка ora 00936 отсутствует выражение
  • Ошибка ora 00936 missing expression
  • Ошибка operating system not found windows 10
  • Ошибка ora 00922