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.