SQL & PL/SQL

Hi,

I have a question why RETURNING COUNT(*) does not return deleted row count when used with DELETE clause, but it returns correct result when I use COUNT(1). Result should be the same.

Test script four you if you don't beleve me :) (I use Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit)

set serveroutput on

CREATE TABLE dummy_table
(
dummy_id INTEGER
)
/

BEGIN
INSERT INTO dummy_table(dummy_id) VALUES(1);
INSERT INTO dummy_table(dummy_id) VALUES(2);
END;
/

SELECT *
FROM dummy_table
/

DECLARE
v_rows_deleted INTEGER;
BEGIN
DELETE dummy_table
WHERE dummy_id = 1
RETURNING COUNT(*) INTO v_rows_deleted;
dbms_output.put_line('rows_deleted: '||v_rows_deleted);
END;
/

SELECT *
FROM dummy_table
/

DECLARE
v_rows_deleted INTEGER;
BEGIN
DELETE dummy_table
WHERE dummy_id = 2
RETURNING COUNT(1) INTO v_rows_deleted;
dbms_output.put_line('rows_deleted: '||v_rows_deleted);
END;
/

SELECT *
FROM dummy_table
/

drop table dummy_table
/

I have run it my self and it works like this

SQL> set serveroutput on
SQL>
SQL> CREATE TABLE dummy_table
2 (
3 dummy_id INTEGER
4 )
5 /

Table created.

SQL>
SQL> BEGIN
2 INSERT INTO dummy_table(dummy_id) VALUES(1);
3 INSERT INTO dummy_table(dummy_id) VALUES(2);
4 END;
5 /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT *
2 FROM dummy_table
3 /

DUMMY_ID
----------
1
2
--created table with two rows
SQL>
SQL> DECLARE
2 v_rows_deleted INTEGER;
3 BEGIN
4 DELETE dummy_table
5 WHERE dummy_id = 1
6 RETURNING COUNT(*) INTO v_rows_deleted;
7 dbms_output.put_line('rows_deleted: '||v_rows_deleted);
8 END;
9 /
rows_deleted: 0
-- just deleted one row but rows deleted shows 0
PL/SQL procedure successfully completed.

SQL>
SQL> SELECT *
2 FROM dummy_table
3 /

DUMMY_ID
----------
2
-- as we can see row is actually deleted
SQL>
SQL> DECLARE
2 v_rows_deleted INTEGER;
3 BEGIN
4 DELETE dummy_table
5 WHERE dummy_id = 2
6 RETURNING COUNT(1) INTO v_rows_deleted;
7 dbms_output.put_line('rows_deleted: '||v_rows_deleted);
8 END;
9 /
rows_deleted: 1
-- deleting, but this time with count (1)
PL/SQL procedure successfully completed.

SQL>
SQL> SELECT *
2 FROM dummy_table
3 /

no rows selected
--and again row is deleted
SQL>
SQL> drop table dummy_table
2 /

Table dropped.