CODEWITHSUNDEEP
sqloracle-databaseplsql
My Code Made Me Suicidal
My Code Made Me Suicidal

Reputation: 579

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?

Upvotes: 2

Views: 274

Answers (4)

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

That's because , You cannot use the RETURNING BULK COLLECT from an INSERT. You can achieve your requirement using For loop 

CREATE OR replace PROCEDURE Run_simulation 
IS 
  t_order_no NUMBER; 
BEGIN 
    FOR my_rec IN (SELECT oid, 
                          cust_id, 
                          stock, 
                          action, 
                          tot_shares, 
                          rem_shares 
                   FROM   stock_order) LOOP 
        INSERT INTO orders 
                    (order_no, 
                     id, 
                     stock, 
                     action, 
                     tot_shares, 
                     rem_shares) 
        VALUES     (my_rec.oid, 
                    my_rec.cust_id, 
                    my_rec.stock, 
                    my_rec.action, 
                    my_rec.tot_shares, 
                    my_rec.rem_shares) 
        RETURNING order_no INTO t_order_no; 

        dbms_output.Put_line (t_order_no); 
    END LOOP; 
END; 
/

CHECK DEMO HERE

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142720

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.

Upvotes: 1

Pavel Brziák
Pavel Brziák

Reputation: 93

I'm not sure if RETURNING INTO has any meaning inside this query. It would have been useful in case you would like to manipulate the data further inside the procedure, which doesn't seem to be happening.

What about simple INSERT INTO SELECT?

CREATE OR REPLACE PROCEDURE run_simulation IS 
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 
END; 
/

You might also want to COMMIT; inside the procedure.

Upvotes: 2

Coskun Ozogul
Coskun Ozogul

Reputation: 2469

You should review your sp structure. Insead of IS, use AS.

Instead of RETURNING use RETURN

Here is an exemple :

https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm

Upvotes: 0

Related Questions