ORA-00918: column ambiguously defined - INSERT INTO table

Question

Please help me with this error, I spent hours on this.

Maybe the problems is somewhere on those fields that highlighted ( on the image ), because when I remove it, the code works.

I check good on column names to make sure there is no wrong typing.

This is the table design:

This is my command text:

INSERT INTO PRODUCTS(
    idz,category_id,brand_id,
    namez,
    computed_name,
    unit_price,discount,
    url_key,
    short_description,
    
    rate_avg,
    rate_total,
    rate_avg_recent,
    rate_total_recent,
    rate_total_counting,
    rate_avg_counting,
    views,
    views_recent,
    views_counting,
    purchases,
    purchases_recent,
    purchases_counting,
    search_recent, 
    search_counting
    
    )
    
WITH recs AS (  
            
    SELECT 52333193, 1811, 111461, 
    'Tai nghe bluetooth không dây F9 True wireless Dock Sạc có Led Báo Pin Kép', 
    'Tai nghe bluetooth không dây F9 True wireless Dock Sạc có Led Báo Pin Kép',
    169000,581000,
    'tai-nghe-bluetooth-khong-day-f9-true-wireless-cam-ung-van-tay-dock-sac-co-led-bao-pin-kep-p52333193/52333193',
    '\n \n\n\n\n\n\n \nSử dụng Chip Bluetooth 5.0 mạnh mẽ',
    
    0,0,0,0,0,0,0,0,0,0,0,0,0,0    -- I think the problem is here, It work when I remove this line (and 
                                   -- columns defined above )

FROM   dual   
)
SELECT * FROM recs

Thank guys!!

Answer 1

I think the problem is that Oracle sets a default alias for each column in the subquery, based its value. Since you have multiple columns with the same value, they get the same alias, and this causes SELECT * to generate the error.

This produces the same error:

with recs as (select 1,2,3,0,0,0 from dual) select * from recs;

But providing explicit unique aliases for the columns that have the same value works:

with recs as (select 1,2,3,0 as a, 0 as b, 0 as c from dual) select * from recs