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  |
 |
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  |
 |
Barbara Boehmer
Messages: 9100
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 #689765 is a reply to message #689760] |
Tue, 16 April 2024 13:19 |
Solomon Yakobson
Messages: 3301
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> |
|
|
|
|
|
|
|
| Re: Oracle ORA-00918: column ambiguously defined [message #689772 is a reply to message #689771] |
Wed, 17 April 2024 09:24 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #689774 is a reply to message #689773] |
Wed, 17 April 2024 12:33 |
Solomon Yakobson
Messages: 3301
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>
|
|
|
|
| Re: Oracle ORA-00918: column ambiguously defined [message #689775 is a reply to message #689773] |
Wed, 17 April 2024 12:36 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
Solomon Yakobson
Messages: 3301
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>
SY.
|
|
|
|
| Re: Oracle ORA-00918: column ambiguously defined [message #689777 is a reply to message #689776] |
Wed, 17 April 2024 14:51 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
Barbara Boehmer
Messages: 9100
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.
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
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.
-- 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 |
|
Barbara Boehmer
Messages: 9100
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.
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 03:54:31 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
