Various errors occurs when clicking on the Script tab of a view in the database explorer, generates errors:
A couple of samples of error being generated:
SAMPLE 1
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 4
SAMPLE 2
ORA-31603: object "CHARTER_MAIL_QUANTITIES" of type VIEW not found in schema "CCOM"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
The user is able to see the script using another SQL Editor with similar functionality.
NOTE: Error only occurs on this one view.
In Toad for Data Analysis 2.0, there is a change to on how to recreate the DDL for an object.
Access Oracle Metalink and read Note:364214.1
NOTE: Metalink document is attached.
The document suggests reloading the XML API.
This is a DBA task because it involves changing the Oracle installation.
It states the following instructions:
1. Enable restricted database session.
2. Run from $ORACLE_HOME/rdbms/admin:
catnomet.sql
rmxml.sql
Which will de-install the xml subsystem.
3. After that, reload it as follows:
and after that (also from $ORACLE_HOME/rdbms/admin):
catxml
utlcxml.sql
prvtcxml.plb
catmet.sql
Additional Information:
The reason for the change in Toad for Data Analysis to recreate the DDL for an object :
Basically, there are two ways to recreate the DDL for an object.
- Method A: Go look into all the relevant Oracle DBA_ views and figure out how to recreate the DDL. This requires "re-engineering" the CREATE statement. It is very laborious to code this.
- Method B: Or use an Oracle built-in package named DBMS_METADATA that provides the DDL used to recreate the object. DBMS_METADATA was introduced in Oracle 9i.
Toad for Oracle and other Quest tools use method A. This is because they were written to support older versions of Oracle, previous to 9.2
Toad for Data Analysis uses method B. We could do this because TDA was written to support Oracle 9i and later.
Method B is better because when Oracle introduces new object types / data types, they also update the DBMS_METADATA package to handle those new object types, this allows us to provide faster delivery of a product to support a new Oracle version when it comes out.
Problem with method B: if there is a problem with the DBMS_METADATA package because of an Oracle bug (which is what you are running into) then we can't show the DDL for an object.
But method A also has problems: if one of the DBA_ views in Oracle had a bug then we would show you the wrong DDL. (Though this does not happen very often.) From a developer point of view, method A requires A LOT more coding and testing.
For TDA we are going to stick with method B. In this case, there is a bug in the software install in the database. One's DBA should be able to fix the bug since it is causing problems. The problem would occur for anyone using DBMS_METADATA in the database, not just someone using TDA, so it would be a good thing to fix it. It would take a DBA a matter of minutes to do the fix (of course after they have read the Metalink note and understood the ramifications.) It would take Quest Software many man-hours to modify TDA to use Method A to generate DDL, and those man-hours are much better spent working on other features in TDA, since the problem encountering with Method B is rare and is due to a bug in the Oracle installation.
