Compiling Apps Schema (invalid objects) in 11i and R12

Compiling (invalid objects) in 11i and R12

Invalid objects will appear for several reasons and it is not very easy to identify invalid objects as the root cause of a failure. In many cases invalid objects can be identified
for failures reported from the functional area or for any mis-behaviour identified. In any case you can expect invalid objects, when you have imported a Database or when you have
applied a Patch bringing new PL/SQL Packages. If you are using adpatch with the Standard options, a compilation of invalid objects for the APPS Schema is included by default -
many Customers disable this compilation via "adpatch options=nocompile", to save time. In any case, it is a good idea to check the E-Business Suite Instance on a regular basis for
invalid objects.

Validating invalid objects

There are several possibilities to validate the invalid objects, which depends on the summary of invalids. If you have identified only some invalid objects, it will be the best to compile
them manually. Please be aware, that you need first to compile the objects, which are belonging to Database Schemas like SYS, SYSTEM, CTXSYS. Objects in the APPS Schema
are often dependent on the objects listed and need to be valid before you are compiling the objects owned by APPS or APPLSYS.

Objects that requires recompilation are:

VIEW, SYNONYM, PUBLIC SYNONYM, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, UNDEFINED , JAVA CLASS, TYPE, TYPE BODY

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

Using Database Tier

There are different ways to recompile invalid objects in schema.
1. DBMS_DDL
2. DBMS_UTILITY
3. UTL_RECOMP
4. UTLRP.SQL

DBMS_DDL
Syntax: Exec dbms_ddl.alter_compile(type,schema,name)
Where
Type : Must be procedure,function,package or package body.
Schema : Database username.
Name: Object name
Example:
Sql> exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST');
PL/SQL procedure successfully completed.

DBMS_UTILITY:
This compiles the object in the specified schema.
Syntax
Exec dbms_utility.compile_schema ( schema,compile all)
Schema : Database Username
Compile All : Object type ( procedure, function, packages,trigger)
Example
SQL> exec dbms_utility.compile_schema('SCOTT');
PL/SQL procedure successfully completed

UTL_RECOMP
Syntax
Exec UTL_RECOMP.RECOMP_SERIAL ();
Example
SQL> Exec UTL_RECOMP.RECOMP_SERIAL ();
PL/SQL procedure successfully completed
Note: Required SYS user to run this package

UTLRP.SQL
Syntax
Located: $ORACLE_HOME/rdbms/admin
Example:
Sql> @$ ORACLE_HOME/rdbms/admin/utlrp.sql

Note: must use UPPER case on the schema name

The script 'utlrp.sql' under the DATABASE $ORACLE_HOME/rdbms/admin/

You need to be logged on as the OS User, who owns the DATABASE $ORACLE_HOME and you need to source the Database Environment.

-Login as database tier user

Release 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

Notes:
This script must be run using SQL*PLUS.
You must be connected AS SYSDBA to run this script.
There should be no other DDL on the database while running the script.
Not following this recommendation may lead to deadlocks.
Finally the number of invalid objects and spool the invalid objects list before and after running these scripts.

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';
alter package <package_name> compile; (package specification)
alter package <package_name> compile body; (package body)
alter view <view_name> compile; (view)

This script needs to run with oracle user.
execute utl_recomp.recomp_parallel(4);

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”

ADCOMPSC.sql

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.

NOTE:  In later versions adcompsc.pls has been replaced with adcompsc.sql

The script can be run as followed :

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

SQL> @adcompsc.sql apps apps %

After the script completes, check for invalid objects again. If the number has decreased, but invalid objects still exist, run @adcompsc.sql again.
Keep running @adcompsc.sql 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.sql. 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 >'

Queries:

To get a quick count of the number of existing invalids

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;
or
column owner format A9
column object_name format A31
column object_type format A15
column last_ddl_time format A10
select owner, object_name, object_type,status,created,last_ddl_time from dba_objects where status = 'INVALID' and owner in ('APPS') order by object_type, object_name desc;

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

For objects which will not compile, try the following :

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

To analyze the user_errors table to determine the cause of the invalid package :

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

Reference Metalink Doc ID 1325394.1