DBMS_METADATA Error

Applies to: Oracle Server - Enterprise Edition - Version: 9.2.0.7 Information in this document applies to any platform. 

SQL> select dbms_metadata.get_ddl(’TABLE’,’mytable‘,’myuser‘) from dual;

ORA-06502: PL/SQL: numeric or value error
LPX-00210: expected '<' instead of 'n'
ORA-06512: at "SYS.UTL_XML", line 0
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3688
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4544
ORA-06512: at "SYS.DBMS_METADATA", line 466
ORA-06512: at "SYS.DBMS_METADATA", line 629
ORA-06512: at "SYS.DBMS_METADATA", line 1246
ORA-06512: at line 1

Prior to 9.2.0.6, this situation is caused by:Bug.3361288 (80) AFTER
REGISTERING XML SCHEMA IN AL32UTF8 DATABASE, EXPORT FAILS WITH ORA-24324 

Solution # 1:

The recommendations are documented in:

Note.279065.1 Ext/Mod Full Export of Database fails with EXP-00056 ORA-06502 ORA-31605 ORA-22921

Solution#1. The solution is to reload the XML API:
 

Step 1. SQL> alter system enable restricted session;

No Body can login or start a new session while running the scripts

Step 2. run: (from $ORACLE_HOME/rdbms/admin):
 

catnomet.sql
rmxml.sql
to remove the xml subsystem

After that recreate it by running following scripts:
catxml
utlcxml.sql
prvtcxml.plb
catmet.sql 

 All the scripts can be run only on testing environment and run in production at your own risk.