Home » SQL & PL/SQL » SQL & PL/SQL » SQL%ROWCOUNT reporting 1 even though there is no records affected
SQL%ROWCOUNT reporting 1 even though there is no records affected [message #218293] Wed, 07 February 2007 11:25 Go to next message
vinu_raj
Messages: 26
Registered: April 2005
Location: Bangalore INDIA
Junior Member
Hi,
I have a PL/SQL script to remove records from some table in our application based on some criteria. I am using bulk collect and forall delete in the procedure. Code for one table looks as below.
-----------------------------------------------------------
.
.
v_cr_count number(10) := 0;
begin
.
.
forall idx in 1..v_IDArray.count
delete from customer_requests where id = v_IDdArray(idx);
v_cr_count := v_cr_count + sql%rowcount;
dbms_output.put_line(v_cr_count);
.
.
end;
/

-----------------------------------------------------------

Problem here is that eventhough there are no records in the table, SQL%rowcount is reported as one. Any idea ? Is this a bug, or is it a bug in my code ? Oracle version is a below

Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

Platform is
Linux 2.4.21-32.ELsmp #1 SMP i686 i686 i386 GNU/Linux

regards
Vinu
Re: SQL%ROWCOUNT reporting 1 even though there is no records affected [message #218300 is a reply to message #218293] Wed, 07 February 2007 12:06 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Please post back with a full example, including properly formatted commands and output, of what you are doing. Like this:

MYDBA@orcl > create table test(id number);

Table created.

MYDBA@orcl >
MYDBA@orcl > declare
  2          type num_arr_type is table of number index by binary_integer;
  3          num_arr num_arr_type;
  4  begin
  5
  6          num_arr(1) := -100;
  7          num_arr(2) := 200;
  8          num_arr(3) := 0;
  9
 10          forall i in indices of num_arr
 11                  delete from test where id = num_arr(i);
 12
 13          dbms_output.put_line('FIRST TIME: ' || sql%rowcount);
 14
 15          insert into test (id) values (1);
 16          insert into test (id) values (2);
 17          commit;
 18
 19          forall i in indices of num_arr
 20                  delete from test where id = num_arr(i);
 21
 22          dbms_output.put_line('SECOND TIME: ' || sql%rowcount);
 23  end;
 24  /
FIRST TIME: 0
SECOND TIME: 0

PL/SQL procedure successfully completed.

MYDBA@orcl >
MYDBA@orcl > drop table test;

Table dropped.

Re: SQL%ROWCOUNT reporting 1 even though there is no records affected [message #218308 is a reply to message #218293] Wed, 07 February 2007 12:48 Go to previous messageGo to next message
vinu_raj
Messages: 26
Registered: April 2005
Location: Bangalore INDIA
Junior Member
Here is what I am doing. There are no records in tables customer_request and customer_request_audit and still the SQL%rowcount is reporting 1 instead of 0.

SQL> declare
  2  type t_IdArray is table of customer_request.id%type;
  3  v_IdArray t_IdArray;
  4  v_retention_period number(6) := 999999;
  5  v_array_limit number(5) := 100;
  6  cursor c_archId is
  7  select distinct id
  8  from customer_request
  9  where update_time < (sysdate - v_retention_period);
 10  v_cr_count    number(10) := 0;
 11  v_cra_count   number(10) := 0;
 12  begin
 13    begin
 14      select to_number(parameter_value)
 15          into v_retention_period
 16          from app_params
 17          where parameter_name = 'DATA_RETENTION_PERIOD';
 18    exception
 19      when NO_DATA_FOUND then
 20            v_retention_period := 999999;
 21    end;
 22    open c_archId;
 23    loop
 24      fetch c_archId bulk collect into v_IdArray limit v_array_limit;
 25          forall idx in 1..v_IdArray.count
 26            delete from customer_request_audit where id = v_IdArray(idx);
 27          v_cra_count := v_cra_count + sql%rowcount;
 28
 29          forall idx in 1..v_IdArray.count
 30               delete from customer_request where id = v_IdArray(idx);
 31          v_cr_count := v_cr_count + sql%rowcount;
 32          exit when c_archId%notfound;
 33    end loop;
 34    close c_archId;
 35    dbms_output.put_line(to_char(v_cra_count,'99999')||' rows deleted from customer_request_audit.');
 36    dbms_output.put_line(to_char(v_cr_count,'99999')||' rows deleted from customer_request.');
 37    commit;
 38  exception
 39    when others then
 40      dbms_output.put_line(sqlerrm);
 41      rollback;
 42  end;
 43  /

1 rows deleted from customer_request_audit.
1 rows deleted from customer_request.

PL/SQL procedure successfully completed.

SQL>
Re: SQL%ROWCOUNT reporting 1 even though there is no records affected [message #218313 is a reply to message #218293] Wed, 07 February 2007 13:09 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Could the 1 be coming from the select statement?
Re: SQL%ROWCOUNT reporting 1 even though there is no records affected [message #218317 is a reply to message #218293] Wed, 07 February 2007 13:21 Go to previous messageGo to next message
vinu_raj
Messages: 26
Registered: April 2005
Location: Bangalore INDIA
Junior Member
Possible !!
I tried the same thing, not using bulk collect and forall. It's setting the sql%rowcount to 0. So what's "forall" doing. It bundles and sends the statement to SQL engine. In this case there is nothing to send as the array is empty and no implicit cursor is open for delete and hence sql%rowcount not updated.

Is that a valid theory ? Smile
Re: SQL%ROWCOUNT reporting 1 even though there is no records affected [message #218320 is a reply to message #218293] Wed, 07 February 2007 13:33 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I don't know the exact why of it, but your theory sounds nice. My example above did have values in the array.

It is behavior to watch out for though, so thanks for posting it.
Re: SQL%ROWCOUNT reporting 1 even though there is no records affected [message #218333 is a reply to message #218293] Wed, 07 February 2007 15:35 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


With collections you should be using SQL%BULK_ROWCOUNT.

Srini
Re: SQL%ROWCOUNT reporting 1 even though there is no records affected [message #218335 is a reply to message #218333] Wed, 07 February 2007 15:42 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
srinivnp wrote on Wed, 07 February 2007 15:35


With collections you should be using SQL%BULK_ROWCOUNT.

Srini



Excellent point.
Re: SQL%ROWCOUNT reporting 1 even though there is no records affected [message #218338 is a reply to message #218293] Wed, 07 February 2007 16:05 Go to previous messageGo to next message
vinu_raj
Messages: 26
Registered: April 2005
Location: Bangalore INDIA
Junior Member
But SQL%BULK_ROWCOUNT is again a collection and do I need sum the values in BULK_ROWCOUNT to find the total number of rows deleted ? In the exmaple I posted, in case there are records, the SQL%ROWCOUNT is giving correct results, meaning SQL%ROWCOUNT is set when a row is actually deleted, but not when there is not a record for delete.

I have seen in some of the Oracle Documentation example itself using SQL%ROWCOUNT for bulk operations.

[Updated on: Wed, 07 February 2007 16:09]

Report message to a moderator

Re: SQL%ROWCOUNT reporting 1 even though there is no records affected [message #218510 is a reply to message #218293] Thu, 08 February 2007 08:55 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


Yes , sql%bulk_rowcount is another collection.

Here is an example.

----
SQL> @foo;
SQL> drop table foo1;

Table dropped.

SQL> drop table foo2;

Table dropped.

SQL> drop type footype;

Type dropped.

SQL> create type footype as table of varchar2(30);
2 /

Type created.

SQL> create table foo1 as select owner from dba_tables
2 group by owner ;

Table created.

SQL> create table foo2 as select owner,table_name from dba_tables
2 where 1=2;

Table created.

SQL> begin
2 insert into foo2 values ('SYS','T1');
3 insert into foo2 values ('SYS','T2');
4 insert into foo2 values ('SYS','T3');
5 insert into foo2 values ('SYSTEM','Z2');
6 insert into foo2 values ('SYSTEM','Z3');
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select * from foo1;

OWNER
------------------------------
SYSTEM
FSYS
PATROL
SYS
QADOD_OWNER
EDIB2B
TSMSYS
QAOWNER
APPDBA
QATOOL
QAC1_OWNER
PERFSTAT
OUTLN
DBSNMP
ISOFT

15 rows selected.

SQL> select * from foo2;

OWNER TABLE_NAME
------------------------------ ------------------------------
SYS T1
SYS T2
SYS T3
SYSTEM Z2
SYSTEM Z3

SQL>
SQL>
Quote:
declare
tabvar footype ;
delcount number :=0 ;
begin
select cast(collect(owner) as footype ) into tabvar from foo1;
forall idx in 1..tabvar.count
delete from foo2 where owner=tabvar(idx);
for ctr in 1 .. tabvar.count
loop
delcount:=sql%bulk_rowcount(ctr)+delcount;
dbms_output.put_line( ctr||'---'||SQL%BULK_ROWCOUNT(CTR));
end loop ;
dbms_output.put_line('total-'||delcount);
end;
/
1---2
2---0
3---0
4---3
5---0
6---0
7---0
8---0
9---0
10---0
11---0
12---0
13---0
14---0
15---0
total-5

PL/SQL procedure successfully completed.

SQL>

Re: SQL%ROWCOUNT reporting 1 even though there is no records affected [message #218560 is a reply to message #218293] Thu, 08 February 2007 11:45 Go to previous message
vinu_raj
Messages: 26
Registered: April 2005
Location: Bangalore INDIA
Junior Member
Thanks for example. I understand this. I can sum up BULK_ROWCOUNT to get the total number of rows deleted. Issue was that the SQL%ROWCOUNT is updated when there are records to delete and not getting updated when there are NO records to delete. Meaning I am getting the proper count using SQL%ROWCOUNT when there are records in the table to delete, but not when there is no record in the table to delete.
Previous Topic: Truncate date string to mm/yyyy
Next Topic: Finding IDs which have different CODE
Goto Forum:
  


Current Time: Sat Dec 10 03:32:38 CST 2016

Total time taken to generate the page: 0.05207 seconds