Compile Invalid Packages in Oracle EBS Database

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

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8