can you please explain why the error ORA-00918 is generated while executing this query
INSERT INTO CLG_TEST_2 (CLG_TEST_2.record_id, CLG_TEST_2.chain_id,
CLG_TEST_2.chain_n,
CLG_TEST_2.contact_info)
select * from (
SELECT 1, 1, 0, '2222' from dual UNION ALL
SELECT 2, 2, 0, '4444' from dual UNION ALL
SELECT 3, 3, 0, '6666' from dual
)
Error at line 1 ORA-00918: column ambiguously defined
Script Terminated on line 2.
The issue is in the fact that you are using a select * over a query without giving aliases to the columns; this will work:
INSERT INTO CLG_TEST_2 (CLG_TEST_2.record_id,
CLG_TEST_2.chain_id,
CLG_TEST_2.chain_n,
CLG_TEST_2.contact_info)
select *
from (
SELECT 1 a, 1 b, 0 c, '2222' d from dual UNION ALL
SELECT 2 , 2 , 0 , '4444' from dual UNION ALL
SELECT 3 , 3 , 0 , '6666' from dual
)
However, you can simplify your code:
INSERT INTO CLG_TEST_2 (record_id, chain_id, chain_n, contact_info)
SELECT 1, 1, 0, '2222' from dual UNION ALL
SELECT 2, 2, 0, '4444' from dual UNION ALL
SELECT 3, 3, 0, '6666' from dual
Something more about the reason of the error.
Your code:
SQL> INSERT INTO CLG_TEST_2 (
2 CLG_TEST_2.record_id,
3 CLG_TEST_2.chain_id,
4 CLG_TEST_2.chain_n,
5 CLG_TEST_2.contact_info)
6 select * from (
7 SELECT 1, 1, 0, '2222' from dual UNION ALL
8 SELECT 2, 2, 0, '4444' from dual UNION ALL
9 SELECT 3, 3, 0, '6666' from dual
10 );
select * from (
*
ERROR at line 6:
ORA-00918: column ambiguously defined
Slightly different:
SQL> INSERT INTO CLG_TEST_2 (
2 CLG_TEST_2.record_id,
3 CLG_TEST_2.chain_id,
4 CLG_TEST_2.chain_n,
5 CLG_TEST_2.contact_info)
6 select * from (
7 SELECT 1, 2, 0, '2222' from dual UNION ALL
8 SELECT 2, 2, 0, '4444' from dual UNION ALL
9 SELECT 3, 3, 0, '6666' from dual
10 );
3 rows created.
What's different?
In the first row, I changed
SELECT 1, 1, 0, '2222' --> SELECT 1, 2, 0, '2222'
^ ^
The reason:
SQL> SELECT 1, 2, 0, '2222' from dual UNION ALL
2 SELECT 2, 2, 0, '4444' from dual UNION ALL
3 SELECT 3, 3, 0, '6666' from dual;
1 2 0 '222
---------- ---------- ---------- ----
1 2 0 2222
2 2 0 4444
3 3 0 6666
SQL> SELECT 1, 1, 0, '2222' from dual UNION ALL
2 SELECT 2, 2, 0, '4444' from dual UNION ALL
3 SELECT 3, 3, 0, '6666' from dual;
1 1 0 '222
---------- ---------- ---------- ----
1 1 0 2222
2 2 0 4444
3 3 0 6666
SQL>
Here you have two columns with the same alias '1', and this is confusing for the external select *.
Also, a direct-path insert is something different