Alternative to RETURNING with INSERT...SELECT

You're using the insert into ... select from construct. So potentially your statement will insert more than one row, which means your RETURNING clause will return more than one row. Consequently you need to use the BULK COLLECT syntax to populate a collection of new keys.

So we try something like this ...

declare    /* NB: define this collection using the appropriate name  */    type new_keys is table of table_xxx.cola%type;    col_res new_keys;begin    INSERT INTO TBL_XXX    SELECT COLA * 10, COLB, COLC FROM TBL_YYY    RETURNING table_xxx.COLA bulk collect INTO COL_RES;end;/

... only to get:

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

Well that sucks.

Unfortunately, while RETURNING BULK COLLECT INTO works with updates and deletes it does not work with inserts (or merges come to that). I'm sure there are very sound reasons in the internal architecture of the Oracle kernel but this ought to work, and that it doesn't is most annoying.

Anyway, as @PonderStibbons pointed out there is a workaround: the FORALL construct.

declare    type new_rows is table of tbl_xxx%rowtype;    rec_xxx new_rows;    type new_keys is table of tbl_xxx.cola%type;    col_xxx new_keys;begin    select cola * 10, colb, colc     bulk collect into rec_xxx    from tbl_yyy;    forall idx in 1 .. rec_xxx.count()        insert into tbl_xxx        values rec_xxx(idx)        returning tbl_xxx.cola bulk collect into col_xxx    ;    for idx in 1 .. rec_xxx.count() loop        dbms_output.put_line('tbl_xxx.cola = ' || col_xxx(idx));   end loop;end;/

Here is a LiveSQL demo (free OTN login required).