Oracle returning statement for an insert into operation with 'select from' source

Question

We would like to return ids inserted from an insert statement that has a 'select from' clause as its source of values.

Example:

Create table Table1 (Col1 Number)

declare
  vId number;
begin
    insert into Table1 (select 1 from dual) 
    returning COL1 into vId;
end;

Error:

ORA-06550: line 5, column 5:
PL/SQL: ORA-00933: SQL command not properly ended

Is there something missing in the syntax, is it possible to do this? Thank you.

returning into doesn't work with INSERT INTO SELECT * . You should use a FORALL block for that. See this or this — Kaushik Nayak

q4za4 q4za4 · Accepted Answer · 2019-02-12T06:48:08

Returning into works only, when columns are listed before VALUES:

declare
  vId number;
begin
    insert into Table1 (col1) VALUES ((select 1 from dual)) 
    returning COL1 into vId;
    dbms_output.put_line( vId);
end;


==========
vId: 1

Oracle returning statement for an insert into operation with 'select from' source

1 Answers