ORA-04068 Errors From User-Written And Oracle Packages (Doc ID 106206.1)

Purpose

You receive the following error from user-written and Oracle packages:

Scope

Cause of ORA-4068 Error:

1) A dependent object was altered through a DDL statement.

When a package is compiled, all copies in the shared pool are flagged as invalid. The next invocation of the package sees this flag set, and goes to get a new copy.

If the package is now invalid, cannot compile, or relied on a package state (i.e., package level variables), then this error occurs because the current copy of the package is no longer valid and must be thrown out.


2) The package was changed or recompiled (both DDL) and the package being used contains package level variables.

Same as above. When a package dependency is altered through DDL statements (DROP, CREATE, ALTER, ...), this package is flagged as invalid through cascade invalidation.


3) A package relied on another package that had no body, and during execution, the call failed.

When a package is compiled, it only looks for the specification.  During execution, it calls a non-existent routine and throws an error. This error then invalidates the package.

Another variation is if the procedure being called is not defined in the package body and possibly as a standalone routine.


4) A remote dependent object has been altered through a DDL statement.

This can occur between database instances (via a database link) or from Forms or Reports to a database instance.

The default remote dependency model uses the Timestamp model, and when an execution of a procedure takes place, the remote object's timestamp is validated, thus forcing invalidation on the local package.

To check for these scenarios, several SQL statements can be run:

a. To check the package's last compile:

SELECT object_name, object_type, owner, status, last_ddl_time FROM dba_objects WHERE object_name = '<INSERT_NAME_HERE>';

For example:

SQL> SELECT object_name, object_type, owner, status, last_ddl_time FROM
2 dba_objects WHERE object_name = 'DBMS_SQL';

OBJECT_NAME
------------------------------------------------------------------------
OBJECT_TYPE OWNER STATUS LAST_DDL_
------------- ------------------------------ ------- ---------
DBMS_SQL
PACKAGE SYS VALID 13-JUL-99

DBMS_SQL
PACKAGE BODY SYS VALID 13-JUL-99

DBMS_SQL
SYNONYM PUBLIC VALID 13-JUL-99

SQL>


b. To check the dependent objects last alteration:

SELECT object_name, object_type, owner, status, last_ddl_time FROM dba_objects WHERE ( object_name, object_type ) IN ( SELECT referenced_name, referenced_type FROM dba_dependencies WHERE name = '<INSERT_NAME_HERE>' );

For example:

SQL> SELECT object_name, object_type, owner, status, last_ddl_time FROM
2 dba_objects WHERE ( object_name, object_type ) IN ( SELECT
3 referenced_name, referenced_type FROM dba_dependencies WHERE name =
4 'DBMS_SQL' );

OBJECT_NAME
-----------------------------------------------------------------------------
OBJECT_TYPE OWNER STATUS LAST_DDL_
------------- ------------------------------ ------- ---------
DBMS_SQL
PACKAGE SYS VALID 13-JUL-99

DBMS_SYS_SQL
PACKAGE SYS VALID 13-JUL-99

STANDARD
PACKAGE SYS VALID 13-JUL-99


SQL>


c. To check for existing errors on package:

SELECT name, type, text FROM dba_errors WHERE name = '<INSERT_NAME_HERE>';

For example:

SQL> SELECT name, type, text FROM dba_errors WHERE name = 'DBMS_SQL';

no rows selected

SQL>

Details