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?

In your query, it does not look like you are returning a generated key `COLA`, as It is part of `select`. Are you using a sequence or any other expression for `COLA`? Or is it something else? — Kaushik Nayak, Mar 09 '18 at 14:30
Is col_res declared as a scalar variable, or as an array? I suspect the former. Either that, or COLA is the name of the TBL_YYY column, but the TBL_XXX has a different name for the first column. (Btw, you should always explicitly declare the names of the columns you're inserting into, to avoid issues with differing column orders between tables, etc) — Boneist, Mar 09 '18 at 14:38
@KaushikNayak I am looking forward to read the generated key of TBL_XXX. Probably will update the question. — arunwithasmile, Mar 12 '18 at 10:39

score 9 · Answer 1 · edited Apr 18 '23 at 15:23

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

I'm afraid that `insert ... select ...` does not work with `returning`, at least I got ORA-00933, we need [forall](http://www.oraclefindings.com/2016/10/20/use-returning-multi-row-insert/) here. — Ponder Stibbons, Mar 09 '18 at 15:28
@PonderStibbons - I have rewritten my answer to demonstrate the use of FORALL to implement a solution — APC, Mar 10 '18 at 09:13

Alternative to RETURNING with INSERT...SELECT

1 Answers1

Linked