Oracle Database Packages and Types 10.2.0.4.0 INVALID

After upgrading one of our databases to 10.2.0.4 dba_registry showed an invalid component
------------------------------ -----------
Oracle Database Packages and Types
10.2.0.4.0 INVALID


The upgrade script $ORACLE_HOME/rdbms/admin/catupgr.sql generated the error messages
ORA-04043: object XDB_DATASTORE_PROC does not exist

PL/SQL: ORA-00904: "OTHER_XML": invalid identifier
Comp ID Component Status Version Org_Version Prv_Version
------- ---------------------------------- --------- -------------- -------------- ---
CATALOG Oracle Database Catalog Views VALID 10.2.0.4.0 9.2.0.5.0 9.2.0.5.0
CATJAVA Oracle Database Java Packages VALID 10.2.0.4.0 9.2.0.5.0 10.2.0.3.0
CATPROC Oracle Database Packages and Types INVALID 10.2.0.4.0 9.2.0.5.0 10.2.0.3.0
EM Oracle Enterprise Manager VALID 10.2.0.4.0 10.2.0.3.0
JAVAVM JServer JAVA Virtual Machine VALID 10.2.0.4.0 9.2.0.5.0 10.2.0.3.0
XDB Oracle XML Database VALID 10.2.0.4.0 10.2.0.1.0 10.2.0.3.0
XML Oracle XDK VALID 10.2.0.4.0 9.2.0.7.0 10.2.0.3.0
======================================================
List of Invalid Database Objects Owned by SYS / SYSTEM
======================================================


OWNER OBJECT_NAME OBJECT_TYPE
------------ ---------------------------------------- ----------------------------------------
SYS DBMS_SQLPA PACKAGE BODY

.
ISSUE CLARIFICATION
====================
after update to 10.2.0.4 CATPROC is invalid and the following error is found in the spool file:
Warning: Package Body created with compilation errors.

SQL> show errors;
Errors for PACKAGE BODY DBMS_SQLPA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
113/5 PL/SQL: SQL Statement ignored
118/44 PL/SQL: ORA-00904: "OTHER_XML": invalid identifier


.
.
CAUSE DETERMINATION
====================
Metalink Note 565600.1 ERROR IN CATUPGRD: ORA-00904 IN DBMS_SQLPA
Bug 6893073 - DBMS_SQLPA IS INVALID ORA-904: "OTHER_XML": INVALID IDENTIFIER


CAUSE JUSTIFICATION
====================
Note 565600.1 ERROR IN CATUPGRD: ORA-00904 IN DBMS_SQLPA
Bug 6893073 - DBMS_SQLPA IS INVALID ORA-904: "OTHER_XML": INVALID IDENTIFIER


.
PROPOSED SOLUTION(S)
======================
1. review note Note 565600.1 ERROR IN CATUPGRD: ORA-00904 IN DBMS_SQLPA and implement the solution proposed
2. check if dba_registry still shows CATPROC component invalid:
SQL> SELECT comp_name, status, substr(version,1,10) as version from dba_registry order 
by modified;

.
UPDATE
=======
Hi Renzo,

The issue that you encountered is similar with the one described in Note 565600.1 ERROR IN CATUPGRD: ORA-00904 IN DBMS_SQLPA.
Please follow the bellow action plan in order to solve your issue:


.
SOLUTION / ACTION PLAN
=======================
To implement the solution, please execute the following steps:

1. review note Note 565600.1 ERROR IN CATUPGRD: ORA-00904 IN DBMS_SQLPA and implement the solution proposed
2. check if dba_registry still shows CATPROC component invalid:
SQL> SELECT comp_name, status, substr(version,1,10) as version from dba_registry order by modified;

Subject: ERROR IN CATUPGRD: ORA-00904 IN DBMS_SQLPA
Doc ID: 565600.1

In the mean time can you please follow below Workaround:
SQL>sqlplus / as sysdba
SQL>drop table plan_table;
SQL>@?/rdbms/admin/utlxplan
SQL>@?/rdbms/admin/prvtspao.plb
SQL>@?/rdbms/admin/utlrp.sql
The proposed workaround was effective!

SQL> select substr(comp_id,1,15) comp_id, substr(comp_name,1,30) comp_name, 
substr(version,1,10) version, status from dba_registry order by modified;
COMP_ID COMP_NAME VERSION STATUS
--------------- ------------------------------ ---------- -----------
CATPROC Oracle Database Packages and T 10.2.0.4.0 VALID
CATALOG Oracle Database Catalog Views 10.2.0.4.0 VALID
XML Oracle XDK 10.2.0.4.0 VALID
CATJAVA Oracle Database Java Packages 10.2.0.4.0 VALID
JAVAVM JServer JAVA Virtual Machine 10.2.0.4.0 VALID
XDB Oracle XML Database 10.2.0.4.0 VALID
EM Oracle Enterprise Manager 10.2.0.4.0 VALID

Inactivated -- Issue Resolved
Rate this note ...
Useless Poor Average Good Very helpful