ORA-20104: create_collection_from_query ParseErr:ORA-00918: column ambiguously defined

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
 
If you are wondering why you are getting this error message after following the example in the documentation describing procedure CREATE_COLLECTION_FROM_QUERYB2 in the APEX_COLLECTION API, the quick answer is that the sample code is flawed.

ORA-20104: create_collection_from_queryb2 Error:ORA-20104: create_collection_from_query 

ParseErr:ORA-00918: column ambiguously defined

The problem is in the missing aliases for the null columns:

Begin
    l_query := 'select empno, sal, comm, deptno, null, hiredate

              , null, null, null, null, ename, job, mgr from emp';
    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERYB2 (
        p_collection_name => 'EMPLOYEES', 
        p_query => l_query,
        p_generate_md5 => 'NO');
End;

After adding the aliases, the API call works without a hitch.

Begin
    l_query := 'select empno, sal, comm, deptno, null as n5, hiredate

              , null as d2, null as d3, null as d4, null as d5

              , ename, job, mgr from emp';
    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERYB2 (
        p_collection_name => 'EMPLOYEES', 
        p_query => l_query,
        p_generate_md5 => 'NO');
End;

 See message translations for ORA-00918 and search additional resources.

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)
as
select * from awful_table
where 

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ı