Tuesday, May 09, 2006

ORA-00918 column ambiguously defined

Recently i had this wonderful idea of adding a column to an existing table for future use.
It's a common practice to put extra columns that one day or another you will start using in your application seamlessly.

However, under certain conditions, it can be an awful idea, especially if a web page starts to crash all of sudden as a result of the change in the table.
That's what happened to me recently.

Imagine you have a table with 10 columns and you define a view on it as:

create view awful_view (c1, c2, ..., c10)
select * from awful_table

Now, if you add a column to awful_table, the view will become permanently invalid raising ORA-00918 as soon as you try to reference it.

The reason is that Oracle is too indulgent towards the programmers and allows us to define a view by means of a generic column specification like

select * from table

* unfortunately means all columns, including those added at a later time, whereas the list of columns names in the view is static (c1...c10).

So, the parser suddenly realizes that you have a view with 10 column names against 11 columns returned by the defining query and throws "ORA-00918 column ambiguously defined".

I don't know why Oracle didn't include an explanation for this error message in the documentation, but at any rate it was a good opportunity to remind me that:

1) I shall not use * in a view definition EVER.
2) I shall revalidate all schema objects manually after adding or dropping a table column.

And this ends temporarily the "best programming practices series".

ORA-00918: definizione della colonna ambigua
ORA-00918: columna definida de forma ambigua
ORA-00918: la columna s'ha definit de forma ambigua
ORA-00918: Définition de colonne ambigu
ORA-00918: Spalte nicht eindeutig definiert
ORA-00918: στήλη ορίσθηκε διφορούμενα
ORA-00918: kolonne er tvetydigt defineret
ORA-00918: kolumn inte entydigt definierad
ORA-00918: kolonnen er ikke entydig definert
ORA-00918: sarake on määritelty moniselitteisesti
ORA-00918: az oszlop nem egyértelműen definiált
ORA-00918: coloană definită ambiguu
ORA-00918: Kolom is dubbelzinnig gedefinieerd.
ORA-00918: coluna definida de maneira ambígua
ORA-00918: coluna definida de modo ambíguo
ORA-00918: столбец определен неоднозначно
ORA-00918: nejednoznačně definovaný sloupec
ORA-00918: stĺpec nejednoznačne definovaný
ORA-00918: kolumna zdefiniowana w sposób niejednoznaczny
ORA-00918: sütun belirsiz bir tanımlandı


Mark Stewart said...

I got the 918 error, and Oracle said it was on a certain line:


PARENT_TAG || chr(124) ||

EMPLOYEE_ID_OLD || chr(124) ||


ERROR at line 123:
ORA-00918: column ambiguously defined

In fact, it was really a column name at line 94; not sure if the line numbers got fouled up by me concatenating a bunch of fields together.

- Mark

Anonymous said...

you sql developers are all sick...

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles