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
);

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1759413213, '216.73.216.42', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/errors/ORA-00918_column-ambiguously-defined(107): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78

ORA-00918: column ambiguously defined

Subquery

See also