How to compile Apps Schema (invalid objects) in 11i and R12

In this post, giving some quick tip on how to compile invalid objects apps schema in Oracle applications 11i and R12

You can compile invalid objects (or Apps Schema) using the following methods:

I. Using Database Tier
-Login as database tier user
11i

• Set environment variable (under $ORACLE_HOME/[SID]_[Hostname].env)
• cd $ORACLE_HOME/rdbms/admin
• sqlplus /nolog
• SQL>conn /as sysdba
• SQL> @utlrp.sql

Release 12

• Set environment variable (under $INSTALL_DIR/db/tech_st/RDMBS_Home/[SID]_[Hostname].env)
• cd $ORACLE_HOME/rdbms/admin
• sqlplus /nolog
• SQL>conn /as sysdba
• SQL> @utlrp.sql

II. Using application tier (adadmin)
-Login as application tier user
11i

• Set environment variable from $APPL_TOP/APPSORA.env)
• adadmin
• option 3 compile/reload Applications Database Entities menu
• option 1 Compile Apps Schema”

Release 12

• Set environment variable (under $INSTALL_DIR/apps/apps_st/appl/APPS[sid]_[hostname].env)
• adadmin
• option 3 compile/reload Applications Database Entities menu
• option 1 Compile Apps Schema”

III. From SQL plus, this is individual objects only
-Figure out invalid Object in the database using

• SQL> select object_name, owner, object_type from all_objects where status ='INVALID';
• SQL> alter [object] [object_name] compile;

IV. ADCOMPSC.pls

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 %

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 command in sqlplus.

e.g. sqlplus apps/password @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 >'

Related Posts by Categories