ORA-00918 column ambiguously defined

Question: What is the cause of the “ORA-00918 column ambiguously defined” error? How do I resolve this?

    SQL> SELECT empno, ename, job, deptno, dname
    FROM scott.emp e, scott.dept d
    WHERE e.deptno=d.deptno;
    select empno, ename, job, deptno, dname from scott.emp e, scott.dept d where e.deptno=d.deptno
    ERROR at line 1:
    ORA-00918: column ambiguously defined

Answer: In a join statement two or more tables have the same column name and Oracle is unable to determine which table the column is referenced to, in the select column list.

Solution: Modify the statement so that the table name or alias is added at the beginning of the column name. Thus the statement should look TABLE_NAME.COLUMN_NAME.

Here add either the table or the alias to the deptno column as below.

    SQL> SELECT empno, ename, job, d.deptno, dname
    FROM scott.emp e, scott.dept d
    WHERE e.deptno=d.deptno;

    35 rows selected.

Related OraTips

A quick guide of Oracle 12c RAC Installation on your laptop running on Windows. Read the step by step instructions to configure Oracle 12c Grid and the database Software.
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP – I am trying to restore the controlfile but and getting the RMAN-06563 error. I know I have a backup on the controlfile but RMAN is not able to see it.

Other Articles

Setting up Oracle 12c Active Data Guard Database In the Multitenant world, the PDB Database is considered an independent database but the operational tasks performed at the CDB level effect all the PDB databases plugged into it. The task of setting up of a Data Guard, switching or failing over are all performed at the CDB level. When the Data Guard is initially setup, with the CDB database, all PDB’s are also replicated to the target. Read more here.
Oracle 12c RAC: New Features Oracle has come a long way from its inception of the 9i RAC database to its current 12c version. I remember the days when we had to deal with RAC related issues, ranging from performance to stability which gradually improved as this product matured. Learn more about the new feature now available with RAC.

Note: This Oracle documentation was created for reference for use by Vitalsofttech DBA’s. If you have any questions please post by clicking on the ASK A QUESTION link above.

Leave a Reply

Your email address will not be published.