ORA-00918: Column ambiguously defined when inserting multiple rows

Question

I am trying to add multiple rows to a table using this reference, statement 8:

INSERT INTO people (person_id, given_name, family_name, title) 
  WITH names AS ( 
    SELECT 4, 'Ruth',     'Fox',      'Mrs'    FROM dual UNION ALL 
    SELECT 5, 'Isabelle', 'Squirrel', 'Miss'   FROM dual UNION ALL 
    SELECT 6, 'Justin',   'Frog',     'Master' FROM dual UNION ALL 
    SELECT 7, 'Lisa',     'Owl',      'Dr'     FROM dual 
  ) 
  SELECT * FROM names

The problem is that it uses a table called names which hasn't been mentioned until that example. I treated it as a placeholder and proceeded with my own query, which is as follows:

INSERT INTO guest (first_name, last_name, address, phone, email, document_id, nationality, status, reservation_id, document_type_id) 
  WITH names AS ( 
    SELECT 'John', 'Doe', 'Grove Street 8', 111222333, '[email protected]', 'JFV5R3', 'English', 1, 1, 1 FROM dual UNION ALL 
    SELECT 'Jane', 'Done', 'Sunrise Avenue 10', 111222335, '[email protected]', 'GFV433', 'English', 1, 2, 1 FROM dual UNION ALL 
    SELECT 'Hannah', 'Drewton', 'Elm Street 8', 551222333, '[email protected]', 'GETER3', 'English', 1, 3, 1 FROM dual UNION ALL 
    SELECT 'David', 'Drewton', 'Elm Street 8', 551225333, '[email protected]', '94TER3', 'English', 1, 4, 1 FROM dual 
  )
  SELECT * FROM names;

As expected, I get the following error: SQL Error: ORA-00918: column ambiguously defined. What could be the problem? I guess it has something to do with that names column, but I cannaot figure out what. Here's my DDL for the table I'm trying to insert data to:

CREATE TABLE guest (
    id                 INTEGER
        GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 CYCLE CACHE 1000 ORDER )
    NOT NULL,
    first_name         VARCHAR2(50) NOT NULL,
    last_name          VARCHAR2(50) NOT NULL,
    address            VARCHAR2(200) NOT NULL,
    phone              NUMBER,
    email              VARCHAR2(100),
    document_id        VARCHAR2(30) NOT NULL,
    nationality        VARCHAR2(50) NOT NULL,
    status             INTEGER DEFAULT 1 NOT NULL,
    reservation_id     INTEGER NOT NULL,
    document_type_id   INTEGER NOT NULL
)
LOGGING;

ALTER TABLE guest
    ADD CONSTRAINT ck_guest_status CHECK ( status BETWEEN 1 AND 5 );

COMMENT ON TABLE guest IS
    'Stores information about guests.';

ALTER TABLE guest ADD CONSTRAINT guest_pk PRIMARY KEY ( id );

ALTER TABLE guest
    ADD CONSTRAINT gst_dcmnt_tp_fk FOREIGN KEY ( document_type_id )
        REFERENCES document_type ( id )
    NOT DEFERRABLE;

In general, I want my table to automatically increment assign an id to the guest id when a row is inserted.

Answer 1

The issue here is that you haven't given the columns in your placeholder query any names. Try:

INSERT INTO guest (first_name, last_name, address, phone, email,
                   document_id, nationality, status, reservation_id, document_type_id) 
  WITH names (first_name, last_name, address, phone, email, 
              document_id, nationality, status, reservation_id, document_type_id) AS ( 
    SELECT 'John', 'Doe', 'Grove Street 8', 111222333, '[email protected]', 'JFV5R3', 'English', 1, 1, 1 FROM dual UNION ALL 
    SELECT 'Jane', 'Done', 'Sunrise Avenue 10', 111222335, '[email protected]', 'GFV433', 'English', 1, 2, 1 FROM dual UNION ALL 
    SELECT 'Hannah', 'Drewton', 'Elm Street 8', 551222333, '[email protected]', 'GETER3', 'English', 1, 3, 1 FROM dual UNION ALL 
    SELECT 'David', 'Drewton', 'Elm Street 8', 551225333, '[email protected]', '94TER3', 'English', 1, 4, 1 FROM dual 
  )
  SELECT * FROM names;

When you don't specify any names, Oracle names the columns after the values in the first row: 'JOHN', 'DOE' etc. This causes ambiguity for you because you have 3 columns containing a value 1 in the first row, resulting in 3 columns named 1!

This query illustrates how Oracle is naming the columns:

  WITH names  AS ( 
    SELECT 'John', 'Doe', 'Grove Street 8', 111222333, '[email protected]', 'JFV5R3', 'English', 1 FROM dual UNION ALL 
    SELECT 'Jane', 'Done', 'Sunrise Avenue 10', 111222335, '[email protected]', 'GFV433', 'English', 1 FROM dual UNION ALL 
    SELECT 'Hannah', 'Drewton', 'Elm Street 8', 551222333, '[email protected]', 'GETER3', 'English', 1 FROM dual UNION ALL 
    SELECT 'David', 'Drewton', 'Elm Street 8', 551225333, '[email protected]', '94TER3', 'English', 1 FROM dual 
  )
  SELECT * FROM names where "'DOE'" = 'Drewton';

Results:

'JOHN' 'DOE'   'GROVESTREET8'     111222333 '[email protected] 'JFV5R 'ENGLIS          1
------ ------- ----------------- ---------- ---------------- ------ ------- ----------
Hannah Drewton Elm Street 8       551222333 [email protected]  GETER3 English          1
David  Drewton Elm Street 8       551225333 [email protected]  94TER3 English          1