PLSQL Insert into with subquery and returning clause

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 g开发者_StackOverflowenerated 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?

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;
   /

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

You can't, BUT at least in Oracle 19c, you can specify a SELECT subquery inside the VALUES clause and so use RETURNING! This can be a good workaround, even if you may have to repeat the WHERE clause for every field:

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

Because the insert is based on a select, Oracle is assuming that you are permitting a multiple-row insert with that syntax. In that case, look at the multiple row version of the returning clause document as it demonstrates that you need to use BULK COLLECT to retrieve the value from all inserted rows into a collection of results.

After all, if your insert query creates two rows - which returned value would it put into an single variable?

EDIT - Turns out this doesn't work as I had thought.... darn it!

This isn't as easy as you may think, and certainly not as easy as it is using MySQL. Oracle doesn't keep track of the last inserts, in a way that you can ping back the result.

You will need to work out some other way of doing this, you can do it using ROWID - but this has its pitfalls.

This link discussed the issue: http://forums.oracle.com/forums/thread.jspa?threadID=352627