Oracle multi-row insert with returning into clause?

Question

Oracle doesn't have easy support for multi-row inserts. However, I did find few workarounds (1, 2) to this. However, I didn't find an option where I can do multiple inserts and get the ID values back.

For e.g. here is a statement in postgresql:

insert into student (name, age) values ('monica', 23), ('saroop', 34) returning id

Assuming here the student table has some auto-generating id field, the above query will return as list of id values. (Two, in this case).

Does oracle pl/sql have something to this same effect?

Note: oracle pl/sql has something for single row inserts that return into a variable as shown here. However, this will fail for multi-row inserts.

Answer 1

This is a sample table:

SQL> CREATE TABLE student
  2  (
  3     id     NUMBER GENERATED ALWAYS AS IDENTITY,
  4     name   VARCHAR2 (20),
  5     age    NUMBER
  6  );

Table created.

If you try to use the RETURNING clause with a multi-row insert, it won't work (that's just how it is in Oracle; it would work with update or delete, though):

SQL> DECLARE
  2     l_ids  SYS.odcinumberlist;
  3  BEGIN
  4     INSERT INTO student (name, age)
  5        SELECT 'monica', 23 FROM DUAL
  6        UNION ALL
  7        SELECT 'saroop', 34 FROM DUAL
  8       RETURNING id
  9            BULK COLLECT INTO l_ids;
 10  END;
 11  /
     RETURNING id
               *
ERROR at line 8:
ORA-06550: line 8, column 16:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 4, column 4:
PL/SQL: SQL Statement ignored


SQL>

---

There's a workaround: first "prepare" rows to be inserted, use forall to insert them into the target table (now you can use returning), do whatever you want to do with returned values.

Types first:

SQL> CREATE TYPE r_stu AS OBJECT (name VARCHAR2 (20), age NUMBER);
  2  /

Type created.

SQL> CREATE TYPE t_stu AS TABLE OF r_stu;
  2  /

Type created.

SQL> SET SERVEROUTPUT ON

Procedure (read comments within the code):

SQL> DECLARE
  2     l_ids  SYS.odcinumberlist;
  3     l_tab  t_stu;
  4  BEGIN
  5     -- prepare rows to be inserted into the target STUDENT table
  6     WITH
  7        temp (name, age)
  8        AS
  9           (SELECT 'monica', 23 FROM DUAL
 10            UNION ALL
 11            SELECT 'saroop', 34 FROM DUAL)
 12     SELECT r_stu (name, age)
 13       BULK COLLECT INTO l_tab
 14       FROM temp;
 15
 16     -- L_TAB now contains all rows we need; insert them and use the RETURNING clause
 17     FORALL i IN l_tab.FIRST .. l_tab.LAST
 18        INSERT INTO student (name, age)
 19             VALUES (l_tab (i).name, l_tab (i).age)
 20          RETURNING id
 21               BULK COLLECT INTO l_ids;
 22
 23     -- these values have been inserted into the ID column
 24     FOR i IN l_ids.FIRST .. l_ids.LAST
 25     LOOP
 26        DBMS_OUTPUT.put_line ('Inserted ID value: ' || l_ids (i));
 27     END LOOP;
 28  END;
 29  /
Inserted ID value: 1
Inserted ID value: 2

PL/SQL procedure successfully completed.

Table contents:

SQL> SELECT * FROM student;

        ID NAME                        AGE
---------- -------------------- ----------
         1 monica                       23
         2 saroop                       34

SQL>

---

[EDIT, regarding your worries about uniqueness being violated]

You said:

If one record had a column with unique value constraint violated, then the whole insert fails. With this above code, I think this is not the case

You're wrong. Here's a demo.

SQL> CREATE TABLE student
  2  (
  3     id     NUMBER GENERATED ALWAYS AS IDENTITY,
  4     name   VARCHAR2 (20) CONSTRAINT uk_stu_name UNIQUE,
  5     age    NUMBER
  6  );

Table created.

SQL> CREATE TYPE r_stu AS OBJECT (name VARCHAR2 (20), age NUMBER);
  2  /

Type created.

SQL> CREATE TYPE t_stu AS TABLE OF r_stu;
  2  /

Type created.

Procedure; monica violates uniqueness

SQL> DECLARE
  2     l_ids  SYS.odcinumberlist;
  3     l_tab  t_stu;
  4  BEGIN
  5     -- prepare rows to be inserted into the target STUDENT table
  6     WITH
  7        temp (name, age)
  8        AS
  9           (SELECT 'monica', 23 FROM DUAL
 10            UNION ALL
 11            SELECT 'saroop', 34 FROM DUAL
 12            UNION ALL
 13            SELECT 'monica', 44 FROM DUAL)  --> monica is duplicated, and will violate unique key
 14     SELECT r_stu (name, age)
 15       BULK COLLECT INTO l_tab
 16       FROM temp;
 17
 18     -- L_TAB now contains all rows we need; insert them and use the RETURNING clause
 19     FORALL i IN l_tab.FIRST .. l_tab.LAST
 20        INSERT INTO student (name, age)
 21             VALUES (l_tab (i).name, l_tab (i).age)
 22          RETURNING id
 23               BULK COLLECT INTO l_ids;
 24
 25     -- these values have been inserted into the ID column
 26     FOR i IN l_ids.FIRST .. l_ids.LAST
 27     LOOP
 28        DBMS_OUTPUT.put_line ('Inserted ID value: ' || l_ids (i));
 29     END LOOP;
 30  END;
 31  /
DECLARE
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UK_STU_NAME) violated
ORA-06512: at line 19

Exception has been raised; you though that rows that didn't violate uniqueness will be inserted. Let's see:

SQL> select * from student;

no rows selected

SQL>

Happy?

Answer 2

Basically a variation of the @Littelfodds proposal, ommiting the PL/SQL stuff.

TLDR - use a temporary table, fill it first with your data enriched with the ID fetched from the sequence and publish the data in the real table in the second step.

Table Setup

CREATE TABLE student
 (
     id     NUMBER GENERATED BY DEFAULT AS IDENTITY,
     name   VARCHAR2 (20),
     age    NUMBER
);

SELECT sequence_name FROM ALL_TAB_IDENTITY_COLS
WHERE table_name = 'STUDENT';

-- TMP Table
CREATE TABLE student_tmp
 (
     id     NUMBER,
     name   VARCHAR2 (20),
     age    NUMBER
);

The important part is GENERATED BY DEFAULT AS IDENTITY, so you can both let the DB to assign the key or you may pass it in the insert statement.

Under to hood of the identoty is a plain sequence, the name you get with the following query:

SELECT sequence_name FROM ALL_TAB_IDENTITY_COLS
WHERE table_name = 'STUDENT';

Prepare Data

INSERT INTO student_tmp (id, name, age)
WITH dt(name, age) AS (
SELECT 'monica', 23 FROM DUAL
UNION ALL
SELECT 'saroop', 34 FROM DUAL
)
SELECT 
  ISEQ$$_85678.nextval,  -- use here the IDENTITY sequence
  name, age 
FROM dt;

Now you have all data in the TMP table, you may check and process the new ID's with

SELECT id, name, age FROM student_tmp;

Publish Data

This is a plain INSERT

INSERT INTO student(id, name, age)
SELECT id, name, age 
FROM student_tmp;

I'm leaving out details as exceptions, transactions and hint for direct insert - set it on your requirements.