INSERT INTO...RETURNING doesn't work ( ORA-00933)
agon...@gmail.com
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.
Any idea ?
Andy Hassall
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 / <an...@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Lewis C
From one part of the SQL manual it looks like it should work but from
a couple of other places, it looks like it can only be simple
expressions, i.e. sal*10 or something of that nature.
From the oracle SQL manual:
<quote>
returning_clause
The returning clause retrieves the rows affected by a DML statement.
You can specify this clause for tables and materialized views and for
views with a single base table.
When operating on a single row, a DML statement with a
returning_clause can retrieve column expressions using the affected
row, rowid, and REFs to the affected row and store them in host
variables or PL/SQL variables.
When operating on multiple rows, a DML statement with the
returning_clause stores values from expressions, rowids, and REFs
involving the affected rows in bind arrays.
<end quote>
I believe you have to use BULK COLLECT when RETURNING INTO with a
multiple record insert or update.
From the PL/SQL manual:
<quote>
By default, you can use this clause only when operating on exactly one
row. When you use bulk SQL, you can use the form RETURNING BULK
COLLECT INTO to store the results in one or more collections.
<end quote>
That's the way I understand it at least. It's very confusing really.
I don't have access to a database right now or I would ty it but I
think the bulk collect is what you need.
I checked out the link to Don Burleson's site and if it does work the
way he says it does, it would be a nice feature. From reading the
docs I don't think it work's that way though. But I haven't
personally tried it.
Hope that helps,
Lewis
-----------------------------------------------------------
Lewis R Cunningham
Author, ItToolBox Blog: An Expert's Guide to Oracle
http://blogs.ittoolbox.com/oracle/guide/
Topic Editor, Suite101.com: Oracle Database
http://www.suite101.com/welcome.cfm/oracle
Sign up for courses here:
http://www.suite101.com/suiteu/default.cfm/416752
-----------------------------------------------------------
Lewis C
>On 10 Apr 2005 15:37:30 -0700, agon...@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.
>>
A link to a question on asktom that I think pertains to this
discussion. The entire thread is worth reading but search on
"returning thing I inserted", no quotes, for a direct answer.
Interesting issue.
Lewis C
>On Sun, 10 Apr 2005 23:49:32 GMT, Lewis C <lew...@excite.com> wrote:
>
>
>A link to a question on asktom that I think pertains to this
>discussion. The entire thread is worth reading but search on
>"returning thing I inserted", no quotes, for a direct answer.
>
Bah! It would probably be helpful to actually post the link. My
second Doi! of the day.
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1176035372649
agon...@gmail.com
select the data (bulk collect) and then insert it (again bulk collect).
This implay using of PGA memory and sum more cpu.
It would have been nice that I could do simple actions during the
Insert as select. (it would reduce resoruce usages and less code
developing)
agon...@gmail.com
I want to use this feature in order to reduce the need to reread the
data again from the table.
I've written down ,your points regarding the usefullness of this
feature.
The main idea is to use that in ETL process which load the entire table
(in my case , partition). The other option which I'm think to use
instead is pipeline functions.