Alternative to RETURNING with INSERT...SELECT

Question

There's this scenario which involves inserting into a table by copying some columns from another table and returning the generated key out of this insert. Using Oracle Database.

Which basically by instinct result to writing this query.

INSERT INTO TBL_XXX
SELECT COLA, COLB, COLC FROM TBL_YYY
RETURNING COLA INTO COL_RES

Which is not allowed for some valid reason.

Is there an alternative to this?

Answer 1

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).