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.