Alternative To Returning With Insert...select

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

Solution 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 istableof table_xxx.cola%type;
    col_res new_keys;
beginINSERTINTO TBL_XXX
    SELECT COLA *10, COLB, COLC FROM TBL_YYY
    RETURNING table_xxx.COLA bulk collectINTO 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 istableof tbl_xxx%rowtype;
    rec_xxx new_rows;
    type new_keys istableof tbl_xxx.cola%type;
    col_xxx new_keys;
beginselect cola *10, colb, colc 
    bulk collectinto rec_xxx
    from tbl_yyy;

    forall idx in1 .. rec_xxx.count()
        insertinto tbl_xxx
        values rec_xxx(idx)
        returning tbl_xxx.cola bulk collectinto col_xxx
    ;

    for idx in1 .. 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).