Today's Messages (on)
| Unanswered Messages (off)
Forum: Server Administration
|
Topic: ALLOW_LEGACY_RECO_PROTOCOL
|
|
Re: ALLOW_LEGACY_RECO_PROTOCOL [message #689769 is a reply to message #689768] |
Wed, 17 April 2024 02:49 |
John Watson
Messages: 8929 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Thank you for replying, I had not thought that it might be to do with raft.
From the Admin guide chapters on distributed databases,
https://docs.oracle.com/en/database/oracle/oracle-database/23/admin/distributed-database-concepts.html
we have:
Quote:31.1.1.3 Distributed Databases Versus Replicated Databases
The terms distributed database system and database replication are related, yet distinct.
In a pure (that is, not replicated) distributed database, the system manages a single copy of all data and supporting database objects. Typically, distributed database applications use distributed transactions to access both local and remote data and modify the global database in real-time.
All the discussion is about guaranteeing ACID compliance across a distributed database by means of the with the 2PC commit or rolback process. Raft, as described in the article you referenced, seems to me to be very different: it is about "convergence" of replicated data sets. A critical sentence (which he has copied straight from the docs) may be
Quote:The application of DML change records to followers, however, is done asynchronously to minimize the impact on transaction latency.
I think this means that at any one moment, the replicated sets will be out of sync (and potentially seen to be out if sync) with each other but over time they will achieve a degree of consistency. To a relational engineer the whole raft concept, if I have it correctly, is anathema. Ted Codd would be spinning in his grave. However a transaction should never be lost, as could certainly happen with Adv Replication or Streams conflict resolution (I don't know how GoldenGate handles conflict resolution).
Perhaps this parameter is part of raft, which would mean that the documentalists have misunderstood it and described it in the wrong place. Quite possible.
|
|
|
Forum: SQL & PL/SQL
|
Topic: Convert input from user into UPPERCASE
|
|
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.
|
|
|
Topic: Oracle ORA-00918: column ambiguously defined
|
|
|
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 #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.
|
|
|
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.
|
|
|
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.
|
|
|
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.
|
|
|
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.
|
|
|
Current Time: Thu Apr 18 21:58:09 CDT 2024
|