Oracle SQL Developer - Errors Copying View from One Database to Another
I am trying to copy a view from one database to another. I am exporting the view using "Quick DDL> Save to Worksheet" to generate a 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 databases are identical, the view I am trying to copy exists in both databases, but one of them was dropped with an error.
When I try to run the script on my second database, I get the error
Bug report:
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.
Does anyone know what I might be doing wrong?
Message
- Unable to display VIEW DDL for object attempting DBMS_METADATA for internal
provided by SQL Developer. This is because it cannot use dbms_metadata.get_ddl. If everything is fine with privileges, most likely due to the type of error (when executing 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 reloading 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.
In my case the problem was that there were two columns with the same name or alias in the view, and since the COL $ table has a unique index with the object number and the column name, two columns are not allowed with the same name