Home » SQL & PL/SQL » SQL & PL/SQL » Oracle ORA-00918: column ambiguously defined (19c)
Oracle ORA-00918: column ambiguously defined [message #689755] Tue, 16 April 2024 07:05 Go to next message
Unclefool
Messages: 85
Registered: August 2021
Member
I'm trying to generate some test data but I am getting the following error during the insert.


ORA-00918: column ambiguously defined

I can't seem to fix the issue. Any help will be greatly appreciated.


ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';

 
create table purchases(
  ORDER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
  customer_id   number, 
  PRODUCT_ID NUMBER, 
  QUANTITY NUMBER, 
  purchase_date timestamp
);

insert  into purchases (customer_id, product_id, quantity, purchase_date)
select 1, 102,1, DATE '2024-04-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '1 00:00:01' DAY TO SECOND) 
from dual
          connect by level <= 3
UNION all 
select 1, 102,1, DATE '2024-03-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '0 23:59:59' DAY TO SECOND) 
from dual
          connect by level <= 3;

Re: Oracle ORA-00918: column ambiguously defined [message #689756 is a reply to message #689755] Tue, 16 April 2024 07:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9091
Registered: November 2002
Location: California, USA
Senior Member
You need column aliases for the columns in the first select statement in your insert statement, as shown below.


C##SCOTT@XE_21.3.0.0.0> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';

Session altered.

C##SCOTT@XE_21.3.0.0.0> 
C##SCOTT@XE_21.3.0.0.0> 
C##SCOTT@XE_21.3.0.0.0> create table purchases(
  2    ORDER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
  3    customer_id   number,
  4    PRODUCT_ID NUMBER,
  5    QUANTITY NUMBER,
  6    purchase_date timestamp
  7  );

Table created.

C##SCOTT@XE_21.3.0.0.0> 
C##SCOTT@XE_21.3.0.0.0> insert	into purchases (customer_id, product_id, quantity, purchase_date)
  2  select  1 customer_id, 102 product_id, 1 quantity,
  3  	     DATE '2024-04-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '1 00:00:01' DAY TO SECOND)
  4  	       as purchase_date
  5  from    dual
  6  connect by level <= 3
  7  UNION all
  8  select  1, 102, 1,
  9  	     DATE '2024-03-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '0 23:59:59' DAY TO SECOND)
 10  from    dual
 11  connect by level <= 3
 12  /

6 rows created.

C##SCOTT@XE_21.3.0.0.0> column purchase_date format a30
C##SCOTT@XE_21.3.0.0.0> select * from purchases
  2  /

  ORDER_ID CUSTOMER_ID PRODUCT_ID   QUANTITY PURCHASE_DATE                      
---------- ----------- ---------- ---------- ------------------------------     
         1           1        102          1 12-APR-2024  13:00:00.000000       
         2           1        102          1 13-APR-2024  13:00:01.000000       
         3           1        102          1 14-APR-2024  13:00:02.000000       
         4           1        102          1 12-MAR-2024  13:00:00.000000       
         5           1        102          1 13-MAR-2024  12:59:59.000000       
         6           1        102          1 14-MAR-2024  12:59:58.000000       

6 rows selected.
Re: Oracle ORA-00918: column ambiguously defined [message #689758 is a reply to message #689756] Tue, 16 April 2024 08:21 Go to previous messageGo to next message
Unclefool
Messages: 85
Registered: August 2021
Member
Barbara, thank you. Not sure why I need the alias though
Re: Oracle ORA-00918: column ambiguously defined [message #689760 is a reply to message #689758] Tue, 16 April 2024 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For me, it is a bug in SQL syntax analyzer as if it is needed for a simple SELECT it is not for an INSERT SELECT.
Or maybe, it is a bug in the SQL standard that Oracle had to follow. Smile

Re: Oracle ORA-00918: column ambiguously defined [message #689765 is a reply to message #689760] Tue, 16 April 2024 13:19 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Or, you could provide different notation for same literal values:

SQL> insert  into purchases (customer_id, product_id, quantity, purchase_date)
  2  select 1, 102,1, DATE '2024-04-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '1 00:00:01' DAY TO SECOND)
  3  from dual
  4            connect by level <= 3
  5  UNION all
  6  select 1, 102,1, DATE '2024-03-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '0 23:59:59' DAY TO SECOND)
  7  from dual
  8            connect by level <= 3;
insert  into purchases (customer_id, product_id, quantity, purchase_date)
       *
ERROR at line 1:
ORA-00918: column ambiguously defined


SQL> insert  into purchases (customer_id, product_id, quantity, purchase_date)
  2  select 1, 102,+1, DATE '2024-04-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '1 00:00:01' DAY TO SECOND)
  3  from dual
  4            connect by level <= 3
  5  UNION all
  6  select 1, 102,1, DATE '2024-03-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '0 23:59:59' DAY TO SECOND)
  7  from dual
  8            connect by level <= 3;

6 rows created.

SQL>
SY.
Re: Oracle ORA-00918: column ambiguously defined [message #689770 is a reply to message #689765] Wed, 17 April 2024 04:35 Go to previous messageGo to next message
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
Senior Member
I stared at that for ages before I spotted what you'd changed (for anyone else staring "1, 102,1" becomes "1, 102,+1" in the first part of the union)
Re: Oracle ORA-00918: column ambiguously defined [message #689771 is a reply to message #689770] Wed, 17 April 2024 08:13 Go to previous messageGo to next message
Unclefool
Messages: 85
Registered: August 2021
Member
SY and cookemonster thank you both. I couldn't figure it out
Re: Oracle ORA-00918: column ambiguously defined [message #689772 is a reply to message #689771] Wed, 17 April 2024 09:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have to correct myself in several points.
First, it is not mandatory in SELECT:
SQL> select 1, 102,1, DATE '2024-04-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '1 00:00:01' DAY TO SECOND)
  2  from dual
  3            connect by level <= 3
  4  UNION all
  5  select 1, 102,1, DATE '2024-03-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '0 23:59:59' DAY TO SECOND)
  6  from dual
  7            connect by level <= 3;
         1        102          1 DATE'2024-04-12'+IN
---------- ---------- ---------- -------------------
         1        102          1 12/04/2024 13:00:00
         1        102          1 13/04/2024 13:00:01
         1        102          1 14/04/2024 13:00:02
         1        102          1 12/03/2024 13:00:00
         1        102          1 13/03/2024 12:59:59
         1        102          1 14/03/2024 12:59:58

6 rows selected.

Then, it is not the case if you don't use an IDENTITY COLUMN:
SQL> create table purchases(
  2    ORDER_ID NUMBER,
  3    customer_id   number,
  4    PRODUCT_ID NUMBER,
  5    QUANTITY NUMBER,
  6    purchase_date timestamp
  7  );

Table created.

SQL> insert  into purchases (customer_id, product_id, quantity, purchase_date)
  2  select 1, 102,1, DATE '2024-04-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '1 00:00:01' DAY TO SECOND)
  3  from dual
  4  connect by level <= 3
  5  UNION all
  6  select 1, 102,1, DATE '2024-03-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '0 23:59:59' DAY TO SECOND)
  7  from dual
  8  connect by level <= 3;

6 rows created.

So it seems clear at first sight the problem is introduced by the IDENTITY column.
BUT:
SQL> select * from (
  2  select 1, 102,1, DATE '2024-04-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '1 00:00:01' DAY TO SECOND)
  3  from dual
  4  connect by level <= 3
  5  UNION all
  6  select 1, 102,1, DATE '2024-03-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '0 23:59:59' DAY TO SECOND)
  7  from dual
  8  connect by level <= 3
  9  );
select * from (
       *
ERROR at line 1:
ORA-00918: column ambiguously defined

Using Solomon's workaround we can see why we have the error on INSERT:
SQL> explain plan for
  2  insert  into purchases (customer_id, product_id, quantity, purchase_date)
  3  select 1, 102,+1, DATE '2024-04-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '1 00:00:01' DAY TO SECOND)
  4  from dual
  5  connect by level <= 3
  6  UNION all
  7  select 1, 102,1, DATE '2024-03-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '0 23:59:59' DAY TO SECOND)
  8  from dual
  9  connect by level <= 3
 10  /

Explained.

SQL> @xpl3
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 2597824445

--------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name         | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |              |      2 |    36 |     4   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL         | PURCHASES    |        |       |            |          |
|   2 |   SEQUENCE                       | ISEQ$$_98581 |        |       |            |          |
|   3 |    VIEW                          |              |      2 |    36 |     4   (0)| 00:00:01 |
|   4 |     UNION-ALL                    |              |        |       |            |          |
|*  5 |      CONNECT BY WITHOUT FILTERING|              |        |       |            |          |
|   6 |       FAST DUAL                  |              |      1 |       |     2   (0)| 00:00:01 |
|*  7 |      CONNECT BY WITHOUT FILTERING|              |        |       |            |          |
|   8 |       FAST DUAL                  |              |      1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
...

The IDENTITY column forces Oracle to use an outer query (line Id 3 VIEW) to sequence the rows.
So, no not really a bug just the way it works and the error comes from we have 2 columns named "1" (but why does it internally need column names?).

[Updated on: Wed, 17 April 2024 09:34]

Report message to a moderator

Re: Oracle ORA-00918: column ambiguously defined [message #689773 is a reply to message #689772] Wed, 17 April 2024 12:17 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
No, it is not a bug. When we insert Oracle creates a mapping between source column aliases and target columns names. That is why column aliases must be unique. I agree, it could be avoided if mapping would be done positionally...

SY.
Re: Oracle ORA-00918: column ambiguously defined [message #689774 is a reply to message #689773] Wed, 17 April 2024 12:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Ah, Michel, you nailed it. Not having identity column in insert list forces positional approach. Having identity column forces Oracle to use name mapping. If we add ORDER_ID (since identity is by default):

insert  into purchases (order_id,customer_id, product_id, quantity, purchase_date)
select level,1, 102,1, DATE '2024-04-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '1 00:00:01' DAY TO SECOND) 
from dual
          connect by level <= 3
UNION all 
select level + 3,1, 102,1, DATE '2024-03-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '0 23:59:59' DAY TO SECOND) 
from dual
          connect by level <= 3;

6 rows created.

SQL>
SY.
Re: Oracle ORA-00918: column ambiguously defined [message #689775 is a reply to message #689773] Wed, 17 April 2024 12:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Oracle creates a mapping between source column aliases and target columns names.

I don't understand what you mean, you can give any column aliases you want, it does not matter:
SQL> insert  into purchases (customer_id, product_id, quantity, purchase_date)
  2  select 1 foo1, 102  foo2, 1  foo3, DATE '2024-04-12' foo4
  3  from dual
  4  /

1 row created.
Re: Oracle ORA-00918: column ambiguously defined [message #689776 is a reply to message #689775] Wed, 17 April 2024 14:34 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel, original query had no select list aliases. So they were derived from select list expressions:

SQL> select 1, 102,1, DATE '2024-04-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '1 00:00:01' DAY TO SECOND)
  2  from dual
  3            connect by level <= 3;

         1        102          1 DATE'2024
---------- ---------- ---------- ---------
         1        102          1 12-APR-24
         1        102          1 13-APR-24
         1        102          1 14-APR-24

SQL>
So now, we hae two aliases named "1".

SY.

Re: Oracle ORA-00918: column ambiguously defined [message #689777 is a reply to message #689776] Wed, 17 April 2024 14:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
So now, we hae two aliases named "1".

I agree, this is what I earlier said.
Column aliases have no relation with target column names, you can even give the same name:
SQL> insert  into purchases (customer_id, product_id, quantity, purchase_date)
  2  select 1 foo, 102  foo, 1  foo, DATE '2024-04-12' foo
  3  from dual
  4  /

1 row created.

The problem occurs when there are multiple subqueries which force Oracle to use an outer query to number the rows.

Re: Oracle ORA-00918: column ambiguously defined [message #689778 is a reply to message #689777] Wed, 17 April 2024 16:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9091
Registered: November 2002
Location: California, USA
Senior Member
Whenever I am trying to understand what causes an error, I try to narrow it down to the simplest example that I can that reproduces the error and the simplest thing that resolves it.  Please see the simplified demonstration below in which the problem is identified as identical column names, whether derived from data of unnamed columns or provided aliases, but only when accessed through an outer query, which is resolved by either providing a different column alias or modifying the representation of the data in such a way that it produces a different column name.  The following does not involve a table or an insert or a union or "connect by" or an identity column, just a query of one row with two identical column names, with or without an outer query.  Exactly why that occurs, I do not know, as I see only fast dual in any explain plan, no view.  Apparently, the original poster's code has the same effect as an outer query.


-- There is no problem with simple queries with identical column names whether derived from data or supplied aliases:
SCOTT@orcl_12.1.0.2.0> select 1, 1 from dual
  2  /

         1          1
---------- ----------
         1          1

1 row selected.

SCOTT@orcl_12.1.0.2.0> select 1 a, 2 a from dual
  2  /

         A          A
---------- ----------
         1          2

1 row selected.
-- When you access either of the above through an outer query, then the ambiguous column error occurs:
SCOTT@orcl_12.1.0.2.0> select * from
  2  (
  3  select 1, 1 from dual
  4  )
  5  /
select * from
       *
ERROR at line 1:
ORA-00918: column ambiguously defined


SCOTT@orcl_12.1.0.2.0> --
SCOTT@orcl_12.1.0.2.0> select * from
  2  (
  3  select 1 a, 2 a from dual
  4  )
  5  /
select * from
       *
ERROR at line 1:
ORA-00918: column ambiguously defined
-- The error is resolved if you provide a different column alias to either of the identical column names:
SCOTT@orcl_12.1.0.2.0> select * from
  2  (
  3  select 1, 1 a from dual
  4  )
  5  /

         1          A
---------- ----------
         1          1

1 row selected.

SCOTT@orcl_12.1.0.2.0> select * from
  2  (
  3  select 1 a, 2 b from dual
  4  )
  5  /

         A          B
---------- ----------
         1          2

1 row selected.
-- The error is qlso resolved if you modify the representation of the data without a column name, such that it results in a different name,
--   in this example by changing 1 to +1 resulting in changing the column name from 1 to +1 as Solomon initially did,
--   resulting in a rather comical eye test for some of us.
SCOTT@orcl_12.1.0.2.0> select * from
  2  (
  3  select 1, +1 from dual
  4  )
  5  /

         1         +1
---------- ----------
         1          1

1 row selected.

[Updated on: Wed, 17 April 2024 17:10]

Report message to a moderator

Re: Oracle ORA-00918: column ambiguously defined [message #689779 is a reply to message #689778] Wed, 17 April 2024 17:31 Go to previous message
Barbara Boehmer
Messages: 9091
Registered: November 2002
Location: California, USA
Senior Member
It seems that the minimal pieces of the factors in the original post that generates the error are insertion of a query with identical column names with union all into a table with a default identity column.  The "connect by" is not a factor.  This seems to have the same effect as the outer query in the simpler reproduction.


--- minimal reproduction of error with factors from original post:
C##SCOTT@XE_21.3.0.0.0> create table test_tab
  2    (col0 number generated by default as identity (start with 1) not null,
  3  	col1 varchar2(4),
  4  	col2 varchar2(4))
  5  /

Table created.

C##SCOTT@XE_21.3.0.0.0> insert into test_tab (col1, col2)
  2  select 1, 1 from dual
  3  union all
  4  select 1, 1 from dual
  5  /
insert into test_tab (col1, col2)
       *
ERROR at line 1:
ORA-00918: column ambiguously defined 


C##SCOTT@XE_21.3.0.0.0> insert into test_tab (col1, col2)
  2  select 1 a, 2 a from dual
  3  union all
  4  select 1, 2 from dual
  5  /
insert into test_tab (col1, col2)
       *
ERROR at line 1:
ORA-00918: column ambiguously defined 



-- same resolution by changing one of the identical column names:

C##SCOTT@XE_21.3.0.0.0> insert into test_tab (col1, col2)
  2  select 1, 1 a from dual
  3  union all
  4  select 1, 1 from dual
  5  /

2 rows created.

C##SCOTT@XE_21.3.0.0.0> insert into test_tab (col1, col2)
  2  select 1 a, 2 b from dual
  3  union all
  4  select 1, 2 from dual
  5  /

2 rows created.

C##SCOTT@XE_21.3.0.0.0> insert into test_tab (col1, col2)
  2  select 1, +1 from dual
  3  union all
  4  select 1, 1 from dual
  5  /

2 rows created.
Previous Topic: correct treatment of date across different timezones
Next Topic: Getting ancestors in hierarchies
Goto Forum:
  


Current Time: Mon Apr 29 18:37:03 CDT 2024