How do I use both a SELECT query and RETURNING inside the same SQLPLUS INSERT INTO statement

I’m working on this project I’m using sqlplus and I’m making this procedure in PL/SQL:

CREATE OR REPLACE PROCEDURE run_simulation IS 
t_order_no stock_order.order_no%type;
BEGIN
    INSERT INTO order VALUES (order_no, id, stock, action, tot_shares, rem_shares)
    SELECT OID, cust_id, stock, action, shares, shares
    FROM stock_order 
    RETURNING order_no INTO t_order_no;
    END; 
    /
    show errors 

I just get the error: SQL command not ended properly. I can’t find any information online about how to properly do this. I tried to have t_order_no as a table. I tried having it RETURNING BULK COLLECT INTO. I tried putting the RETURNING before the select. I don’t know what else to try anymore. Does anyone have any suggestions?

Answer

That’s because – in PL/SQL – you can’t use RETURNING with SELECT:

insert into table (col1, col2, ...)
select val1, val2 ...
from ...
returning val1 into ...

It works only for VALUES:

insert into table (col1, col2, ...)
  values (val1, val2, ...)
  returning val1 into ...

---

Here’s an example:

This won’t work:

SQL> CREATE OR REPLACE PROCEDURE p_test
  2  AS
  3     l_id  NUMBER;
  4  BEGIN
  5     INSERT INTO dept (deptno, dname, loc)
  6        SELECT deptno + 1 AS deptno, dname || 'x', loc
  7          FROM dept
  8         WHERE deptno = 10
  9       RETURNING deptno
 10            INTO l_id;
 11
 12     DBMS_OUTPUT.put_line ('returned ' || l_id);
 13  END;
 14  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE P_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/4      PL/SQL: SQL Statement ignored
9/6      PL/SQL: ORA-00933: SQL command not properly ended
SQL>

But this will:

SQL> CREATE OR REPLACE PROCEDURE p_test
  2  AS
  3     l_id  NUMBER;
  4  BEGIN
  5     INSERT INTO dept (deptno, dname, loc)
  6          VALUES (85, 'Sales', 'London')
  7       RETURNING deptno
  8            INTO l_id;
  9
 10     DBMS_OUTPUT.put_line ('returned ' || l_id);
 11  END;
 12  /

Procedure created.

SQL> EXEC p_test;
returned 85

PL/SQL procedure successfully completed.