The ORA-00604 error: one possible cause

Recently I wanted to modify an existing view. I needed to add a few extra colums from another table. So the original view was something like

create or replace view testview as
select t1.id
,      t1.code
,      t1.description
from   table_1 t1
;

and I wanted to change that into

create or replace view testview as
select t1.id
,      t1.code
,      t1.description
,      t2.code
from   table_1 t1
  join table_2 t2 on t2.t1_id = t1.id
;

From this simplified example you might immediately spot the error here, but my original view was a bit more complicated. The view creation gave the following 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.

I don't recall having an ORA-00604 error before, so I started searching the internet looking for possible causes for this error. But I couldn't find one that related to the statement I was trying to execute. The original statement still executed without error by the way.

After some time I asked a colleague to look at my statement (which I also had simplified a bit), and within minutes he replied "You have a duplicate column CODE".

Ouch.

So I changed my statement to

create or replace view testview as
select t1.id
,      t1.code
,      t1.description
,      t2.code code2
from   table_1 t1
  join table_2 t2 on t2.t1_id = t1.id
;

and it executed without error.

So the lesson I learned from this: even if you get a very exotic error message, keep taking a good look at the code. I was so distracted by the error message that I forgot to rule out simple possible errors. And the second lesson: ORA-00604 might just mean "duplicate column name".

Epilogue

Before writing this blog post I tried to duplicate this on the free cloud database in SQL Developer Web (also a 19c database). But here I get a much more meaningful error message: