[Oracle] How To Solve ORA-06508 | ORA-04065 | ORA-04068 Errors?

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

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

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 …