0

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.

user3167162
  • 445
  • 1
  • 6
  • 23
  • 2
    `returning into` doesn't work with `INSERT INTO SELECT * `. You should use a `FORALL` block for that. See [this](https://stackoverflow.com/a/5325311/7998591) or [this](https://stackoverflow.com/a/49196144/7998591) – Kaushik Nayak Feb 12 '19 at 03:37

1 Answers1

0

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
q4za4
  • 630
  • 4
  • 12
  • Thanks @q4za4, this works for a single row insertion but not for multiple. As Kaushik Nayak suggested, using a forall statement worked. – user3167162 Feb 25 '19 at 22:15