4 Replies Latest reply: Mar 15, 2008 10:20 AM by J.Kiechle RSS

    INSERT INTO...SELECT ... with RETURNING clause

    J.Kiechle
      I was learning RETURNING...INTO clause. Any idea on why i am getting this error while i try to execute the below block
      create table logan as select * from emp where 1=2;
      SQL> set serveroutput on
      SQL> DECLARE
        2   x emp.empno%TYPE;
        3  BEGIN
        4    INSERT INTO LOGAN (EMPNO,ENAME,sal)
        5    SELECT empno,ename,sal from emp
        6    RETURNING empno
        7    INTO x;
        8    dbms_output.put_line(x);
        9    UPDATE logan
      10    set sal= nvl(sal,0)+12
      11    where empno=x;
      12   
      13   commit;
      14   end;
      15  /
        RETURNING empno
                  *
      ERROR at line 6:
      ORA-06550: line 6, column 13:
      PL/SQL: ORA-00933: SQL command not properly ended
      ORA-06550: line 4, column 3:
      PL/SQL: SQL Statement ignored
      Message was edited by:
      J.Kiechle
        • 1. Re: SQL command not properly ended error
          Rob van Wijk
          The RETURNING INTO clause does not work in combination with an INSERT ... SELECT statement. It's a bummer, I agree, but Oracle did not mention that it should work ...

          In this specific case you can skip the update and use a single insert statement:
          insert into logan (empno,ename,sal)
          select empno
               , ename
               , nvl(sal,0) + 12
            from emp
          Regards,
          Rob.
          • 2. Re: SQL command not properly ended error
            William Robertson
            If you're just testing the RETURNING clause, try it with a single-row insert and update:
            DECLARE
                x emp.empno%TYPE;
                y emp.sal%TYPE;
            BEGIN
                INSERT INTO logan
                ( empno
                , ename
                , sal)
                VALUES
                ( 1
                , 'Logan'
                , 1e4 )
                RETURNING empno INTO x;

                DBMS_OUTPUT.PUT_LINE('New empno: ' || x);

                UPDATE logan
                SET    sal = nvl(sal, 0) + 12
                WHERE  empno = x
                RETURNING sal INTO y;
               
                DBMS_OUTPUT.PUT_LINE('New salary: ' || y);
            END;
            /

            New empno: 1
            New salary: 10012

            PL/SQL procedure successfully completed.
            Or, here's an example of returning an aggregate (requires 10g) in SQL*Plus:
            SQL> var avgsal number
            SQL>
            SQL> UPDATE emp SET sal = sal * 1.15
              2  RETURNING AVG(sal) INTO :avgsal;

            14 rows updated.

            SQL> print :avgsal

                AVGSAL
            ----------
            2384.19643
            • 3. INSERT INTO...SELECT ... with RETURNING clause
              J.Kiechle
              Good to know that. I would have wasted hours trying to implement this(INSERT ...INTO ..SELECT with RETURNING clause) in a code.
              Thank you very much Rob.

              ps: I am changing the subject of this thread to highlight this drawback(or unimplemented feauture) of RETURNING clause
              • 4. Re: SQL command not properly ended error
                J.Kiechle
                Cool. Thank you William