ADZDEXRPT.sql Fails 'AD_ZD_PREP' Invalid or with With Error: ORA-06508: PL/SQL: could not find APPS.AD_ZD_PREPAD_ZD_PREP Due To Missing Package 'xdb_migrateschema' or PLS-00201: identifier 'SYS.DBMS_OBJECTS_APPS_UTILS'

Issue:

AD_ZD_PREP Invalid while running script: $AD_TOP/sql/ADZDEXRPT.sql as part of EBS 12.2 upgrade

SQL> select owner,object_name,object_type,status from dba_objects where object_name like 'AD_ZD%' and status ='INVALID';

OWNER           OBJECT_NAME                    OBJECT_TYPE         STATUS
--------------- ------------------------------ ------------------- -------
APPS            AD_ZD_PREP                     PACKAGE BODY        INVALID

SQL> alter package AD_ZD_PREP compile body;

Warning: Package Body altered with compilation errors.

SQL> sho error
Errors for PACKAGE BODY AD_ZD_PREP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
297/6    PL/SQL: Statement ignored
297/6    PLS-00201: identifier 'SYS.DBMS_OBJECTS_APPS_UTILS' must be
         declared

633/4    PL/SQL: Statement ignored
633/4    PLS-00201: identifier 'SYS.DBMS_OBJECTS_APPS_UTILS' must be
         declared

715/6    PL/SQL: Statement ignored
715/6    PLS-00201: identifier 'SYS.DBMS_OBJECTS_APPS_UTILS' must be
         declared

1081/4   PL/SQL: Statement ignored
1081/17  PLS-00201: identifier 'SYS.DBMS_OBJECTS_APPS_UTILS' must be
         declared
       
Solution:

I.
Check if the latest R12.TXK.C.Delta patch applied on instance.
If yes, Please run the adgrants from the latest R12.TXK.C.Delta patch to grant privilages to APPS user
If not, Please apply the patch and check.

Recompile the package and check.

II.
For issue: Error: PL/SQL: ORA-00942: table or view does not exist
sys.xdb$moveSchemaTab;

Sol:

1. Confirm if the package 'xdb_migrateschema' exists using the following select statement:

select owner, object_name from dba_objects where object_name like upper ('xdb_migrateschema');

If it does NOT exist, then use the following scripts to create it and confirm results again using the above query:

conn / as sysdba

@?/rdbms/admin/dbmsxdbschmig.sql
@?/rdbms/admin/prvtxdbschmig.plb

2. Run adgrants.sql.
3. Recompile the package AD_ZD_PREP and ensure it compiles successfully.
4. Continue with the upgrade and confirm online patching is successfully enabled.

III.

If you see the issue with the package: XDB_MIGRATESCHEMA invalid.

Sol:

Connect database sys as sysdba user

SQL> @?/md/admin/catnomgdidcode.sql

and

SQL> @?/md/admin/catmgdidcode.sql

Run adgrants.sql

Compile package : XDB_MIGRATESCHEMA
Compile package : AD_ZD_PREP

IV.

If you see the issue with the package: SYS.DBMS_OBJECTS_APPS_UTILS not exist

SQL> select owner, object_name, object_type from dba_objects where object_name='DBMS_OBJECTS_APPS_UTILS';
no rows selected

1. Please refer to Note 1594274.1 (Oracle E-Business Suite Release @ 12.2: Consolidated List of Patches and Technology Bug Fixes) to find the corresponding patch that delivers the missing bug fixes.

2. If an overlay patch is needed for any particular patch listed, the footnote for that patch also applies to the overlay patch.

3. At a minimum ensure that the following bug fixes are not missing in your instance:

17421340
17487358
12749597
14692386

4. If the above patches are not applied, apply them in a test environment with guidance from Note 1594274.1.

5. Retest the issue.


OR

Connect DB as sys user and execute below.

cd $ORACLE_HOME/rdbms/admin/

SQL> @dbmsobj.sql

select owner, object_name, object_type from dba_objects where object_name='DBMS_OBJECTS_APPS_UTILS';

Compile Invalid packages and test.