I'm trying to copy a view from one database to another. I'm exporting the view using 'Quick DDL > save to worksheet' to generate the script. At the top of the script i get the message:
"-- Unable to render VIEW DDL for object <view name> with DBMS_METADATA attempting internal generator."
Both the databases are identical, the view I'm trying to copy existed in both databases but was dropped in error in one of them.
When I try to run the script on my second database i get the error
SQL Error: ORA-00604: error occurred at recursive SQL level 1 ORA-00001: unique constraint (SYS.I_COL1) violated 00604. 00000 - "error occurred at recursive SQL level %s" *Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables). *Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support.
Has anyone any idea what I might be doing wrong?
-- Unable to render VIEW DDL for object with DBMS_METADATA attempting internal
gives the SQL Developer. This is due to the fact that he can not use dbms_metadata.get_ddl. If everything is fine with the privileges, most likely due to the type of error (on execute dbms_metadata.get_ddl):
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 3296 ORA-06512: at "SYS.DBMS_METADATA_INT", line 4148 ORA-06512: at "SYS.DBMS_METADATA", line 458 ORA-06512: at "SYS.DBMS_METADATA", line 615 ORA-06512: at "SYS.DBMS_METADATA", line 1221 ORA-06512: at line 1
Try to reload the XML API:
1. enable restricted database session 2. run: (from $ORACLE_HOME/rdbms/admin): catnomet.sql rmxml.sql to remove the xml subsystem and after that: catxml utlcxml.sql prvtcxml.plb catmet.sql to reload it.