Compile Invalid Packages in Oracle EBS Database

Introduction:

EBS Functionality breaks if dependent packages become invalid.  While troubleshooting any E-Business Suite Functional issue, always check if any packages are changed, modified, or become invalids.

Query to check invalids:

Few queries you must use to check the status of the invalids.

Total Number of Invalids:

    select count(*) from dba_objects where status='INVALID';

Number of Invalids with schema details

    col owner for a30

    select owner, object_type, count(*) from dba_objects where status='INVALID'

    group by owner, object_type;

Objects which are invalids in the database along with the OWNER name

    col OWNER for a30

    col OBJECT_NAME for a30

    set lines 1000

    select object_name, owner from dba_objects where status='INVALID';

How to Compile Invalids

Manual approach - Compile each package or package body manually.

alter package <package_name> compile body;

alter package <package_name> compile;

Similarly, we can compile other object_type like VIEW, FUNCTION, SYNONYM, TRIGGER and MATERIALIZED VIEW

Use the same above query to compile. 

alter procedure <procedure_name> compile;

alter synonym <synonym_name> compile;

alter trigger <trigger_name> compile; 

DBMS_DDL Package method:

EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');

EBS 12.2 Method

SQL> exec AD_ZD.compile ('XX_DETAIL_PKG');

PL/SQL procedure successfully completed.

SQL> sho error

No errors.

SQL>

utlrp.sql tool

Goto $ORACLE_HOME/rdbms/admin and run utlrp.sql

SQL> @utlrp.sql

EBS 12.2 best methods to compile entire Schema

SQL> EXEC UTL_RECOMP.recomp_serial('APPS');

PL/SQL procedure successfully completed.

SQL>

Compile Invalids with parallel thread

SQL> EXEC UTL_RECOMP.recomp_parallel(10);

Verify the Invalids

    col OWNER for a30

    col OBJECT_NAME for a30

    set lines 1000

    select object_name, owner from dba_objects where status='INVALID';