Upgrade Script ADZDEXRPT.sql Fails 'AD_ZD_PREP' Invalid With Sys.xdb$ Or Patch 13543062 Fails With Error: ORA-06508: PL/SQL: could not find APPS.AD_ZD_PREPAD_ZD_PREP Due To Missing Package 'xdb_migrateschema'

(Doc ID 2066607.1)

Last updated on APRIL 12, 2017

Applies to:

Oracle Applications DBA - Version 12.2 and later
Information in this document applies to any platform.

Symptoms

E-Business Suite 12.2 Applications DBA, Upgrade related issues

 

 

While upgrading E-Business Suite and / or enabling online patching, ADZDEXRPT.sql fails as AD_ZD_PREP is invalid due to the following errors:

Compilation errors for PACKAGE BODY APPS.AD_ZD_PREP

Error: PLS-00201: identifier 'SYS.DBMS_OBJECTS_APPS_UTILS' must be declared
Line: 292
Text: sys.dbms_objects_apps_utils.recompile_types(names=> g_obj_list_to_recompile);

Error: PL/SQL: Statement ignored
Line: 292
Text: sys.dbms_objects_apps_utils.recompile_types(names=> g_obj_list_to_recompile);

Error: PL/SQL: ORA-00942: table or view does not exist
Line: 419
Text: delete from sys.xdb$moveSchemaTab;

Error: PL/SQL: SQL Statement ignored
Line: 419
Text: delete from sys.xdb$moveSchemaTab;

Error: PL/SQL: ORA-00942: table or view does not exist
Line: 423
Text: insert into sys.xdb$moveSchemaTab(schema_url, schemaownerfrom, schemaownerto, schema)

Error: PL/SQL: SQL Statement ignored
Line: 423
Text: insert into sys.xdb$moveSchemaTab(schema_url, schemaownerfrom, schemaownerto, schema)

Error: PLS-00201: identifier 'SYS.XDB_MIGRATESCHEMA' must be declared
Line: 443
Text: sys.xdb_migrateschema.moveSchemas;

Error: PL/SQL: Statement ignored
Line: 443
Text: sys.xdb_migrateschema.moveSchemas;

Error: PL/SQL: ORA-00942: table or view does not exist
Line: 457
Text: delete from sys.xdb$moveSchemaTab;

Error: PL/SQL: SQL Statement ignored
Line: 457
Text: delete from sys.xdb$moveSchemaTab;

  

This can be reproduced with the following steps:
1. After applying 12.2.0 upgrade driver and about to enable Online Patching.
2. Following document E48839-09 : 7. Run the Online Patching Enablement - Status Report => b. Run the report using the following command
3. At this step, the document advises to "Take special note to ensure that all online patching objects (objects that match the pattern ‘AD_ZD%’)" are valid:
    >sqlplus <apps username> @$AD_TOP/sql/ADZDEXRPT.sql

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms