RETURNING clause


The RETURNING clause is very cool, but whilst the docs say that you can use for "DML", unfortunately you don't get that luxury for INSERT-SELECT

SQL> declare
  2    type t is table of number;
  3    n t;
  4  begin
  5
  6    insert into my_table
  7    select * from all_Objects
  8    where rownum < 10
  9    returning object_id bulk collect into n;
 10  end;
 11  /
  where rownum < 10
          *
ERROR at line 8:
ORA-06550: line 9, column 3:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 6, column 3:
PL/SQL: SQL Statement ignored

SQL> declare
  2    type t is table of number;
  3    n t;
  4  begin
  5
  6    insert into my_table
  7    select * from all_Objects
  8    where rownum < 10;
  9  --  returning object_id bulk collect into n;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> declare
  2    type t is table of number;
  3    n t;
  4  begin
  5
  6    insert into my_table
  7    select * from all_Objects
  8    where rownum < 10;
  9  --  returning object_id bulk collect into n;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> declare
  2    type t is table of number;
  3    n t;
  4  begin
  5
  6    delete my_table
  7    returning object_id bulk collect into n;
  8  end;
  9  /

PL/SQL procedure successfully completed.