PLSQL Insert into with subquery and returning clause

Question

I can't figure out the correct syntax for the following pseudo-sql:

INSERT INTO some_table            (column1,             column2)      SELECT col1_value,              col2_value         FROM other_table       WHERE ...          RETURNING id        INTO local_var;  

I would like to insert something with the values of a subquery. After inserting I need the new generated id.

Heres what oracle doc says:

Insert Statement

Returning Into

OK i think it is not possible only with the values clause... Is there an alternative?

Answer 1

Unfortunately that's not possible. RETURNING is only available for INSERT...VALUES statements. See this Oracle forum thread for a discussion of this subject.

Answer 2

You cannot use the RETURNING BULK COLLECT from an INSERT. This methodology can work with updates and deletes howeveer:

create table test2(aa number) / insert into test2(aa)       select level         from dual         connect by level<100 /          set serveroutput on declare       TYPE t_Numbers IS TABLE OF test2.aa%TYPE         INDEX BY BINARY_INTEGER;       v_Numbers t_Numbers;       v_count number; begin   update test2   set aa = aa+1 returning aa bulk collect into v_Numbers;      for v_count in 1..v_Numbers.count loop         dbms_output.put_line('v_Numbers := ' || v_Numbers(v_count));     end loop;  end; 

You can get it to work with a few extra steps (doing a FORALL INSERT utilizing TREAT) as described in this article:

returning with insert..select

T

to utilize the example they create and apply it to test2 test table

 CREATE or replace TYPE ot AS OBJECT     ( aa number); /   CREATE TYPE ntt AS TABLE OF ot; /  set serveroutput on  DECLARE         nt_passed_in ntt;        nt_to_return ntt;         FUNCTION pretend_parameter RETURN ntt IS           nt ntt;        BEGIN           SELECT ot(level) BULK COLLECT INTO nt          FROM   dual          CONNECT BY level <= 5;          RETURN nt;       END pretend_parameter;     BEGIN        nt_passed_in := pretend_parameter();        FORALL i IN 1 .. nt_passed_in.COUNT          INSERT INTO test2(aa)          VALUES          ( TREAT(nt_passed_in(i) AS ot).aa          )          RETURNING ot(aa)          BULK COLLECT INTO nt_to_return;        FOR i IN 1 .. nt_to_return.COUNT LOOP          DBMS_OUTPUT.PUT_LINE(             'Sequence value = [' || TO_CHAR(nt_to_return(i).aa) || ']'             );       END LOOP;     END;    /