Re: INSERT INTO...RETURNING doesn't work ( ORA-00933)

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Mon, 11 Apr 2005 00:25:11 +0100
Message-ID: <e3cj515bg922fhq4cj74o3517fofs8kct3@4ax.com>

On 10 Apr 2005 15:37:30 -0700, agonenil_at_gmail.com wrote:

>hi , i get the following problem :
>
>SQL> Variable tot_sal number;
>SQL> begin
> 3 INSERT INTO emp select * from emp
> 5 RETURNING sum(sal) INTO :tot_sal;
> 7 dbms_output.put_line('Total Company Payroll now : ' ||
>to_char(:tot_sal,'$9
>99,999.00'));
> 9 end;
> 11 /
>RETURNING sum(sal) INTO :tot_sal;
> *
>ERROR at line 5:
>ORA-06550: line 5, column 11:
>PL/SQL: ORA-00933: SQL command not properly ended
>ORA-06550: line 3, column 1:
>PL/SQL: SQL Statement ignored
>
>altough accrding to http://www.remote-dba.cc/10g_65.htm , it should
>work.
>
>I'm using 10.1.0.4 on windows.

The INSERT statement under PL/SQL has this for the RETURNING INTO clause:

http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10807/13_elems025.htm#sthref1697 "returning_clause

Returns values from inserted rows, eliminating the need to SELECT the rows afterward. You can retrieve the column values into variables or into collections. You cannot use the RETURNING clause for remote or parallel inserts. If the statement does not affect any rows, the values of the variables specified in the RETURNING clause are undefined. For the syntax of returning_clause, see "DELETE Statement". "

So, have to go to the DELETE statement for the full definition of the RETURNING INTO clause:

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_8005.htm#i2085984

"Restrictions

The following restrictions apply to the RETURNING clause:

The expr is restricted as follows: o Each expr must be a simple expression or a single-set aggregate function expression. You cannot combine simple expressions and single-set aggregate function expressions in the same returning_clause. "

Trying that on DELETE (with a simple table t (c number), with a single row, c=1, 10.1.0.4, Linux):

SQL> declare
2 c number;
3 begin

  4      DELETE FROM t
  5      RETURNING sum(c) INTO c;
  6      dbms_output.put_line('sum now : ' || c);

7 end;
8 /
sum now : 1

PL/SQL procedure successfully completed.

OK, so you can get the sum of rows deleted.

So, for INSERTs, what's it supposed to be summing? The rows inserted (this would make more sense) or as implied by the broken code you quoted, the new total for the entire table? (but that'd be inconsistent with how RETURNING INTO normally works). It'd only be the "Total Company Payroll" if the table were empty... and since it's inserting into itself, duplicating all rows, then it could only be correct when it outputted zero...

And anyway, it doesn't work:

SQL> declare
2 c number;
3 begin

  4      INSERT INTO t (c) values (1)
  5      RETURNING sum(c) INTO c;
  6      dbms_output.put_line('sum now : ' || c);

  7 end;
  8 /
    RETURNING sum(c) INTO c;

*
ERROR at line 5:
ORA-06550: line 5, column 15:
PL/SQL: ORA-00934: group function is not allowed here ORA-06550: line 4, column 5:
PL/SQL: SQL Statement ignored

SQL> declare
2 c number;
3 begin

  4      INSERT INTO t (c) 
  5      select c from t
  6      RETURNING sum(c) INTO c;
  7      dbms_output.put_line('sum now : ' || c);

  8 end;
  9 /
    RETURNING sum(c) INTO c;

*
ERROR at line 6:
ORA-06550: line 6, column 15:
PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 4, column 5:
PL/SQL: SQL Statement ignored

Looks a lot like Burleson didn't run the code before posting it - it doesn't work - which is not entirely surprising since the semantics aren't really clear, although the manual could be clearer on the restriction, rather than just referring to the definition in DELETE.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Received on Sun Apr 10 2005 - 18:25:11 CDT