Forum Stats

  • 3,783,314 Users
  • 2,254,756 Discussions
  • 7,880,359 Comments

Discussions

Compile Invalid Packages in Oracle EBS Database

Piyush Prakash
Piyush Prakash Member Posts: 21 Red Ribbon

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