How to compile invalid objects in an APPS Environment

Applying Patches can create invalid objects. To get a quick count of the number of existing invalids (if any), use the following select statement :

SELECT COUNT(*)
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID';

For a more detailed query, use the following script :

SELECT OWNER, OBJECT_TYPE, COUNT(*)
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
GROUP BY OWNER, OBJECT_TYPE;

To recompile an individual object, connect to SQL*PLUS as the owner of the object (generally apps) and use one of the following depending on the object type :

alter package <package_name> compile; (package specification)
alter package <package_name> compile body; (package body)
alter view <view_name> compile; (view)

If the object compiles with warnings, use either of the following to see the errors that caused the warnings :

show errors
OR
select * from user_errors where name = '<OBJECT_NAME>';

Another way to correct invalid objects is to run the adadmin utility as follows:

UNIX OPERATING PLATFORM

1. Log in as APPS User : <applmgr username>/<applmgr password>

2. Start the adadmin-Utility from the Unix prompt with this command :

adadmin

The utility will then ask you a series of questions.

3. Under the Maintain Applications Database Objects Menu, select Compile APPS schema(s)

This task spawns parallel workers to compile invalid database objects in your APPS schema(s). It uses the same parallel phases as AutoInstall.

Also try running $ORACLE_HOME/rdbms/admin/utlrp.sql ( as sysdba )

Within Applications, there is a script to compile INVALID objects - called ADCOMPSC.pls

Arguments for ADCOMPSC.pls :

1 - Schema to run in
2 - Password for schema
3 - Check errors for objects starting with #3

NOTE: The order in which to compile Invalid Objects in schemas is SYS, SYSTEM, APPS and then all others. APPS_DDL and APPS_ARRAY_DDL should exist in all schema's. In case of an ORA-1555 error while running adcompsc.pls, restart the script.

The script can be run as followed :

cd $AD_TOP/sql
sqlplus @adcompsc.pls SCHEMA_NAME SCHEMA_PASSWORD %

Example : SQL> @adcompsc.pls apps apps %

After the script completes, check for invalid objects again. If the number has decreased, but invalid objects still exist, run adcompsc.pls again. Keep running adcompsc.pls until number of invalid objects stops decreasing.

If there are any objects still left INVALID, verify them by using the script 'aderrchk.sql' to record the remaining INVALID objects. 'Aderrchk.sql' uses the same syntax as 'adcompsc.pls'. This script is also supplied with the Applications. Send the aderrchk.sql to a file using the spool <file> command in sqlplus.

e.g. sqlplus x/y @aderrchk.sql SCHEMA_NAME SCHEMA_PASSWORD %

For objects which will not compile, try the following :

select text
from user_source
where name = 'OBJECTNAME'
and text like '%Header%';

This script will provide the script that creates the packages/recreates the packages.
SQL>@packageheader
SQL>@packagebody
If recreating the package does not make the package valid, analyze the user_errors table to determine the cause of the invalid package :

select text
from user_errors
where name = '<PACKAGENAME>';