ORA-04068 Errors from User-Written and Oracle Packages

ORA-04068 Errors from User-Written and Oracle Packages



Causes of ORA-4068 Errors


-------------------------



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 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 situations, 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 = '';



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 =

'' );



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 = '';



For example:



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



no rows selected



SQL>





Solutions for ORA-4068 Errors

-----------------------------



1) Simply re-execute the package.



For example:



Session 1: Create the package and body for package p_pack:



SQL> create or replace package p_pack as

2 p_var varchar2(1);

3 procedure p;

4 end p_pack;

5 /



Package created.



SQL> create or replace package body p_pack as

2 procedure p is

3 begin

4 p_var := 1;

5 end;

6 end p_pack;

7 /



Package body created.



SQL>



Session 2: Execute the package:



SQL> exec p_pack.p



PL/SQL procedure successfully completed.



SQL>



Session 1: Recreate the package and body:



SQL> create or replace package p_pack as

2 p_var varchar2(1);

3 procedure p;

4 end p_pack;

5 /



Package created.



SQL> create or replace package body p_pack as

2 procedure p is

3 begin

4 p_var := 1;

5 end;

6 end p_pack;

7 /



Package body created.



SQL>



Session 2: Re-execute the package:



SQL> exec p_pack.p

begin p_pack.p; end;



*

ERROR at line 1:

ORA-04068: existing state of packages has been discarded

ORA-04061: existing state of package "SCOTT.P_PACK" has been invalidated

ORA-04065: not executed, altered or dropped package "SCOTT.P_PACK"

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

ORA-06512: at line 1





SQL> exec p_pack.p



PL/SQL procedure successfully completed.



SQL>



As background, when the ORA-4068 is raised, ORACLE will throw away all

existing instantiations of the package. When the package (more properly,

the subprogram referring to the package) is re-executed, ORACLE will

re-instantiate the package automatically (if possible), which

typically will succeed, and re-execution of the subprogram will succeed.



An important proviso is that the ORA-4068 error must be unhandled on exit

from the subprogram in order for this solution to work. It's only when an

_unhandled_ ORA-4068 is returned by PL/SQL to ORACLE that the needed

deinstantiations take place.

(The ORA-4068 may be handled in the subprogram and various actions

taken in the handler, but the error must be reraised in order for it to be

unhandled on exit from PL/SQL to get the desired deinstantiations.)





2) Attempt to recompile the package by using the ALTER PACKAGE command.



For example:



SQL> ALTER PACKAGE DBMS_SQL COMPILE;



Package altered.



SQL>



3) Verify that proper execute permissions have been provided. In PL/SQL

stored program units, roles are disabled prior to the release of Oracle

8i. Oracle 8i definers rights follow the previous release model of

requiring explicit permission to the object.



In Oracle 8i, if invoker's rights are set on the routine, then execution

is done with roles enabled, so permission could be granted explicitly to

the schema executing or through a role. For additional information,

refer to Note:162489.1 entitled "Invokers Rights Procedure Executed by Definers Rights Procedures".









For example:



SQL> SELECT owner, table_name, privilege FROM dba_tab_privs WHERE

2 table_name = 'DBMS_SQL' AND ( grantee = 'SCOTT' OR grantee = 'PUBLIC' );



OWNER TABLE_NAME

------------------------------ ------------------------------

PRIVILEGE

----------------------------------------

SYS DBMS_SQL

EXECUTE





SQL>



4) If errors exist (check DBA_ERRORS or USER_ERRORS views with above

query), then take the appropriate action to correct the errors.



If the package is a system package that comes with the Oracle server,

the scripts are located in $ORACLE_HOME/rdbms/admin. Most packages have

their own .sql and .plb script to build the specification and body (see

below for names).



System packages that come with the Oracle server, as well as other Oracle

products, typically need to be owned by a particular schema. In the case

of the Oracle server DBMS packages, these need to be owned by SYS. If these

packages are not owned by SYS, some packages start getting 'ORA-6509 PL/SQL

ICD vector missing for this package' errors.



5) If duplicate SYS owned objects exist, clean them up. Refer to

Note:1030426.6, entitled "HOW TO CLEAN UP DUPLICATE OBJECTS OWNED BY

SYS AND SYSTEM SCHEMA", for information on this.





$ORACLE_HOME/rdbms/admin Packages

---------------------------------

System Package Name Package Spc Package Bdy Owner

------------------- ----------- ----------- -----

DBMS_ALERT dbmsalrt.sql prvtalrt.plb SYS

DBMS_APPLICATION_INFO dbmsapin.sql prvtapin.plb SYS

DBMS_AQ dbmsaq.plb prvtaq.plb SYS

DBMS_AQADM dbmsaqad.sql prvtaqad.plb SYS

DBMS_CRYPTO_TOOLKIT dbmsoctk.sql prvtoctk.plb SYS

DBMS_DDL dbmsutil.sql prvtutil.plb SYS

DBMS_DEBUG dbmspb.sql prvtpb.plb SYS

DBMS_DEFER dbmsdefr.sql prvtdefr.plb SYS

DBMS_DEFER_QUERY dbmsdefr.sql prvtdefr.plb SYS

DBMS_DEFER_SYS dbmsdefr.sql prvtdefr.plb SYS

DBMS_DESCRIBE dbmsdesc.sql prvtdesc.plb SYS

DBMS_DISTRIBUTED_TRUST_ADMIN

dbmstrst.sql prvttrst.plb SYS

DBMS_HS dbmshs.sql prvths.plb SYS

DBMS_IOT dbmsiotc.sql prvtiotc.plb SYS

DBMS_JOB dbmsjob.sql prvtjob.plb SYS

DBMS_LOB dbmslob.sql prvtlob.plb SYS

DBMS_LOCK dbmslock.sql prvtlock.plb SYS

DBMS_LOGMNR dbmslm.sql prvtlm.plb SYS

DBMS_LOGMNR_D dbmslmd.sql dbmslmd.sql SYS

DBMS_OFFLINE_OG dbmsofln.sql prvtofln.plb SYS

DBMS_OFFLINE_SNAPSHOT dbmsofsn.sql prvtofsn.plb SYS

DBMS_ORACLE_TRACE_AGENT

dbmsotrc.sql prvtotrc.plb SYS

DBMS_ORACLE_TRACE_USER

dbmsotrc.sql prvtotrc.plb SYS

DBMS_OUTPUT dbmsotpt.sql prvtotpt.plb SYS

DBMS_PCLXUTIL dbmsutil.sql prvtutil.plb SYS

DBMS_PIPE dbmspipe.sql prvtpipe.sql SYS

DBMS_RANDOM dbmsrand.sql dbmsrand.sql SYS

DBMS_RECTIFIER_DIFF dbmsrctf.sql prvtrctf.plb SYS

DBMS_REFRESH dbmssnap.sql prvtsnap.plb SYS

DBMS_REPAIR dbmsrpr.sql prvtrpr.plb SYS

DBMS_REPCAT dbmshrep.sql prvtbrep.plb SYS

DBMS_REPCAT_ADMIN prvthdmn.plb prvtbdmn.plb SYS

DBMS_REPCAT_INSTANTIATE

dbmsrint.sql prvtbrnt.plb SYS

DBMS_REPCAT_RGT dbmsrgt.sql prvtbrgt.plb SYS

DBMS_REPUTIL dbms_gen.sql prvtgen.plb SYS

DBMS_RESOURCE_MANAGER dbmsrmad.sql prvtrmad.plb SYS

DBMS_RESOURCE_MANAGER_PRIVS

dbmsrmpr.sql prvtrmpr.plb SYS

DBMS_RLS dbmsrlsa.sql prvtrlsa.plb SYS

DBMS_ROWID dbmsutil.sql prvtutil.plb SYS

DBMS_SESSION dbmsutil.sql prvtutil.plb SYS

DBMS_SHARED_POOL dbmspool.sql prvtpool.plb SYS

DBMS_SNAPSHOT dbmssnap.sql prvtsnap.plb SYS

DBMS_SPACE dbmsutil.sql prvtutil.plb SYS

DBMS_SPACE_ADMIN dbmsspc.sql prvtspad.plb SYS

DBMS_SQL dbmssql.sql prvtsql.plb SYS

DBMS_STATS dbmsstat.sql prvtstat.plb SYS

DBMS_TRACE dbmspbt.sql prvtpbt.plb SYS

DBMS_TRANSACTION dbmsutil.sql prvtutil.plb SYS

DBMS_UTILITY dbmsutil.sql prvtutil.plb SYS

OUTLN_PKG dbmsol.sql prvtol.plb SYS

UTL_COLL utlcoll.sql prvtcoll.plb SYS

UTL_FILE utlfile.sql prvtfile.plb SYS

UTL_HTTP utlhttp.sql prvthttp.plb SYS

UTL_RAW utlraw.sql prvtrawb.plb SYS

UTL_REF utlref.sql prvtref.plb SYS





References

----------



"Oracle7 Server Application Developer's Guide", (A32536-1)



"Oracle8 Server Application Developer's Guide", (A54642-01)



"Oracle8i Application Developer's Guide - Fundamentals Release 8.1.5",

(A68003-01)





Related Articles

----------------



Note:19857.1 OERR: ORA 4068 "existing state of packages%s%s%s has been

discarded"



Note:19854.1 OERR: ORA 4065 "not executed, altered or dropped %s"



Note:19850.1 OERR: ORA 4061 "existing state of %s has been invalidated"



Note:20065.1 OERR: ORA 6508 "PL/SQL: could not find program unit being

called"



Note:1012129.102 ORA-4068,4067,6508 When Executing a Procedure Using PL/SQL



Note:117118.1 Errors Running or Compiling DBMS_RANDOM or DBMS_CRYPTO_TOOLKIT



Note:1030426.6 HOW TO CLEAN UP DUPLICATE OBJECTS OWNED BY SYS AND SYSTEM

SCHEMA



Note:1012129.102 ORA-4068,4067,6508 WHEN EXECUTING A PROCEDURE USING PLSQL