Returning clause with insert does not work!

On the web there is a lot of material about the new 10g feature returning clause with examples.
However it looks like those who have published these examples have not tested the code and just typed it out.
See : http://orafaq.com/node/34

Returning clause works fine with Update and Delete.

Ex:

declare
tot_count number;
begin
delete from pn_1
RETURNING count(a1) INTO tot_count;

dbms_output.put_line(tot_count);
end;

However with insert

table pn_1 is created with two columns s1 varchar2(30), a2 number

declare
tot_count number;
begin

insert into pn_1
select table_name,1
from user_tables
RETURNING count(a1) INTO tot_count;

dbms_output.put_line(tot_count);
end;

it gives syntax error
ORA-06550: line 5, column 16:
PL/SQL: ORA-00933: SQL command not properly ended

See Metalink Note:302910.1

So still the best way to note how many records have been inserted :


declare

begin
insert into pn_1
select table_name,1 b
from user_tables;
--RETURNING sum(b) INTO tot_count;
dbms_output.put_line(sql%rowcount);
exception
when others then
dbms_output.put_line(sqlerrm);
end;