datapatch fails with PL/SQL Errors : "Warning: Package Body created with compilation errors. " ,"PLS-00201: identifier ' ' must be declared" (Doc ID 2657716.1)

Last updated on JULY 20, 2024

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

Symptoms

Symptom 1)

datapatch fails with error:

Validating logfiles...
Patch patchnum rollback: WITH ERRORS
  logfile: $ORACLE_BASE/cfgtoollogs/sqlpatch/~/patch_rollback_SID_<timestamp>.log (errors)
  Error at line 4992: Warning: Package Body created with compilation errors.
  Error at line 4998: 18788/3 PL/SQL: Statement ignored
  Error at line 4999: 18788/6 PLS-00201: identifier
  Error at line 5002: 18792/3 PL/SQL: Statement ignored
  Error at line 5003: 18792/3 PLS-00201: identifier

<patchnum>_apply or rollback SID_<timestamp>.log shows:

?/rdbms/admin/prvtawrs.plb

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY DBMS_AWR_REPORT_LAYOUT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
18788/3 PL/SQL: Statement ignored
18788/6 PLS-00201: identifier
'DBMS_AWR_PROTECTED.COMMON_SECTIONS_INITIALIZED' must be declared

18792/3 PL/SQL: Statement ignored
18792/3 PLS-00201: identifier
'DBMS_AWR_PROTECTED.COMMON_SECTIONS_INITIALIZED' must be declared

Symptom 2)

(a)

datapatch fails with error:

logfile: $ORACLE_BASE/cfgtoollogs/sqlpatch/30445968/23303757/30445968_rollback_SID_<timestamp>.log.log (errors)
Error at line 80679: Warning: Package Body created with compilation errors.
Error at line 80686: 18788/3 PL/SQL: Statement ignored
Error at line 80687: 18788/6 PLS-00201: identifier
Error at line 80690: 18792/3 PL/SQL: Statement ignored
Error at line 80691: 18792/3 PLS-00201: identifier
Error at line 94171: Warning: Package Body created with compilation errors.
Error at line 94178: 242/6 PL/SQL: Statement ignored
Error at line 94179: 242/6 PLS-00201: identifier 'DBMS_BDSQL.SEL_USERMAP' must be declared

<patchnum>_rollback_SID_<timestamp>.log.log shows:

SQL> @?/rdbms/admin/prvthadoop1.plb
Session altered.
..
..
Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY DBMS_HADOOP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
242/6 PL/SQL: Statement ignored
242/6 PLS-00201: identifier 'DBMS_BDSQL.SEL_USERMAP' must be declared

(b)

datapatch fails with error:

logfile: $ORACLE_BASE/cfgtoollogs/sqlpatch/35943157/25527362/35943157_apply_<SID>_<timestamp>.log (errors)
Error at line 158022: script rdbms/admin/prvthadoop1.plb
- Warning: Package Body created with compilation errors.
Error at line 158030: script rdbms/admin/prvthadoop1.plb
- 113/3    PL/SQL: SQL Statement ignored
..
Error at line 158035: script rdbms/admin/prvthadoop1.plb
- 349/33   PLS-00364: loop index variable 'R' use is invalid
Error at line 158036: script rdbms/admin/prvthadoop1.plb
- 478/14   PL/SQL: SQL Statement ignored

<patchnum>_apply_SID_<timestamp>.log.log shows:

SQL> CREATE OR REPLACE PACKAGE BODY DBMS_HADOOP_INTERNAL wrapped
2 a000000
3 1
4 abcd
..
..
170 /

Warning: Package Body created with compilation errors.

Elapsed: 00:00:00.25
SQL> show errors;
Errors for PACKAGE BODY DBMS_HADOOP_INTERNAL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
113/3    PL/SQL: SQL Statement ignored
115/8    PL/SQL: ORA-00942: table or view does not exist
344/14   PL/SQL: SQL Statement ignored
344/38   PL/SQL: ORA-00942: table or view does not exist
349/6    PL/SQL: Statement ignored
349/33   PLS-00364: loop index variable 'R' use is invalid
478/14   PL/SQL: SQL Statement ignored
478/58   PL/SQL: ORA-00942: table or view does not exist
483/6    PL/SQL: Statement ignored
483/33   PLS-00364: loop index variable 'R' use is invalid

Symptom 3)

datapatch fails with error:

Error at line 29457: Warning: Package Body created with compilation errors.
Error at line 29464: 0/0 PL/SQL: Compilation unit analysis terminated
Error at line 29465: 1/14 PLS-00201: identifier 'DBMS_ASH' must be declared
Error at line 29466: 1/14 PLS-00304: cannot compile body of 'DBMS_ASH'

<patchnum>_apply _SID_<timestamp>.log shows:

?/rdbms/admin/prvtash.plb

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY DBMS_ASH:

PL/SQL: Compilation unit analysis terminated
PLS-00201: identifier 'DBMS_ASH' must be declared
PLS-00304: cannot compile body of 'DBMS_ASH' without its specification