OraFAQ Forum

Today's Messages (on) | Unanswered Messages (off)

Topic: Convert input from user into UPPERCASE

Re: Convert input from user into UPPERCASE [message #689766 is a reply to message #689764]

Wed, 17 April 2024 01:12

watisditnou@msn.com
Messages: 2
Registered: April 2024

Junior Member

Thank you all very much for your reply. At last it was much easier then i thought. I tried (also) the suggestion from Michel Cadot and remove the " around the user name did the trick. Pff can't believe i didn't tried that Smile

Report message to a moderator

Re: Convert input from user into UPPERCASE [message #689767 is a reply to message #689766]

Wed, 17 April 2024 01:20

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Generally speaking, when you give an identifier (user name, table name, column name or any object name), if you specify it with " Oracle keeps it as it, otherwise it converts it in upper case.

Thank you for your feedback.

Report message to a moderator

Topic: Oracle ORA-00918: column ambiguously defined

Re: Oracle ORA-00918: column ambiguously defined [message #689770 is a reply to message #689765]

Wed, 17 April 2024 04:35

cookiemonster
Messages: 13920
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)

Report message to a moderator

Re: Oracle ORA-00918: column ambiguously defined [message #689771 is a reply to message #689770]

Wed, 17 April 2024 08:13

Unclefool
Messages: 85
Registered: August 2021

Member

SY and cookemonster thank you both. I couldn't figure it out

Report message to a moderator

Re: Oracle ORA-00918: column ambiguously defined [message #689772 is a reply to message #689771]

Wed, 17 April 2024 09:24

Michel Cadot
Messages: 68641
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

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.

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: 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.

Report message to a moderator

Re: Oracle ORA-00918: column ambiguously defined [message #689775 is a reply to message #689773]

Wed, 17 April 2024 12:36

Michel Cadot
Messages: 68641
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.

Report message to a moderator

Re: Oracle ORA-00918: column ambiguously defined [message #689776 is a reply to message #689775]

Wed, 17 April 2024 14:34

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.

Report message to a moderator

Re: Oracle ORA-00918: column ambiguously defined [message #689777 is a reply to message #689776]

Wed, 17 April 2024 14:51

Michel Cadot
Messages: 68641
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.

Report message to a moderator

Re: Oracle ORA-00918: column ambiguously defined [message #689778 is a reply to message #689777]

Wed, 17 April 2024 16:46

Barbara Boehmer
Messages: 9088
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

Barbara Boehmer
Messages: 9088
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.

Report message to a moderator

Current Time: Thu Apr 18 08:30:37 CDT 2024