sql%rowcount question 2

[sql99]

Hello,

I got some tips on how to use sql%rowcount from a previous thread and was hoping I could get some help on this. Is it possible for me to have a sql%rowcount on only one table in the loop since it's the largest table? I'm not sure if I'm using this correctly but what I want to do is to loop through 2 tables and if there are no more records in the largest table (table1), then exit out of the loop. Would I need sql%rowcount underneath the second delete statement for table2?

begin
loop
delete from table1 where date < '02/15/2003'
and rownum <= 10000;
if sql%rowcount = 0 then exit; end if;
commit;
--
delete from table2 where date < '02/15/2003'
and rownum <= 10000;
commit;
end loop;
end;


Thanks in advance,

sql99

 

[lewisp]

I dont see a problem using SQL%ROWCOUNT in your example. It will only contain the record count of the last transaction, and that is the delete on the large table.

 

[carp]

sql%rowcount is an attribute of an implicit cursor. Consequently, it is reset with every DML statement.
You have no control over its behavior.
Consequently, your code should work fine.

Elbert, CO
1411 MST

 

[sql99]

Thanks lewisp and carp for your help..