Direct-Path INSERT query generates ORA-00918 error

Question

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.

Answer 1

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

Answer 2

I don't see any "Direct-Path" insert. Anyway, try this one

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 1, 1, 0, '2222' from dual UNION ALL
SELECT 2, 2, 0, '4444' from dual UNION ALL
SELECT 3, 3, 0, '6666' from dual

btw, why do you use string from numbers?