Search notes:

ORA-00918: column ambiguously defined

create table tq84_tab_a (id_a  integer, num number, txt varchar2(10));
create table tq84_tab_b (id_b  integer, num number, dt  date        );
OK: 
select
   id_a,
   txt
from
   tq84_tab_a    join
   tq84_tab_b on id_a = id_b;
The column num is found in both tables, tables, tq84_tab_a and tq84_tab_b, the identifier is therefore abmbiguous and causes the error ORA-00918: column ambiguously defined: 
select
   id_a,
   num
from
   tq84_tab_a                 join
   tq84_tab_b on id_a = id_b;
Use table aliases to get rid of the error: 
select
   A.id_a,
   B.num
from
   tq84_tab_a A                      join
   tq84_tab_b B  on A.id_a = B.id_b;

drop table tq84_tab_a;
drop table tq84_tab_b;

Subquery

The following query, executed in a tool like SQL*Plus or SQL Developer, runs fine, even though the column name NUM appears twice. Oracle silently renames the second NUM to NUM_1: 
select
   4   num,
   8   num,
  'x'  txt
from
   dual;
This query can be used as a subquery as long as NUM is not selected from the subquery, i. e. the following statement runs fine as well: 
select txt from (
   select
      4   num,
      8   num,
      7   num_1,
     'x'  txt
   from
      dual
);
However, if either NUM or * is selected from this subquery, the ORA-00918 error is raised, as demonstrated with the following query: 
select * from (
   select
      4   num,
      8   num,
     'x'  txt
   from
      dual
);

See also

Columns
ORA-00957: duplicate column name
Other Oracle error messages

Index