How To Count Total number of rows deleted by DELETE and reset counter after some condition [message #668801] |
Thu, 15 March 2018 03:13 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/b80831ab52b38aed2eaf41e59e7fc54b?s=64&d=mm&r=g) |
ssyr
Messages: 65 Registered: January 2017
|
Member |
|
|
Hi All,
I need some suggestion on my below PL?SQL block. Where I have store all delete stamen in one table and after that I call one after another but my question is I have to reset count variable once it deles 10000 rows form table and commit. But using my block it is not going into the If LOOP
PROCEDURE DEL_PROC AS
recordsDeleted number(10):=0;
counter number(10):=0;
BEGIN
for j IN ( select COUNT_sql,del_sql_qry,base_tbl_order from
Store_Del_tab _form )loop
execute immediate J.del_sql_qry;
recordsDeleted:=SQL%ROWCOUNT;
counter:=SQL%ROWCOUNT;
dbms_output.put_line('Out IF LOOP counter : '|| counter);
if(counter=10000) then --Not Cheking this condition once counter reaches to 10000
dbms_output.put_line('In IF LOOP counter : '|| counter);
commit;
counter:=0;
END IF;
dbms_output.put_line('After IF LOOP counter : '|| counter);
commit;
update del_log_tab
set total_row_deleted=recordsDeleted,
qry_end_date=TO_char(sysdate, 'yyyy/mm/dd hh24:mi:ss')
where post_sql_qry=J.del_sql_qry;commit;
recordsDeleted:=0;
end loop;
END
How can I check the counter in above loop /Please suggest some help on this?
Thanks in advance.
|
|
|
|
|
|
|
|
|
|
Re: How To Count Total number of rows deleted by DELETE and reset counter after some condition [message #668813 is a reply to message #668811] |
Thu, 15 March 2018 06:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You are looping over some delete statements
In each iteration of the loop you issue a delete that will delete n rows.
You set the counter variable to n - which is the number of rows the most recent delete statement deleted, not the total number of rows deleted so far.
If counter = 10000 (not more, not less, exactly 10000) then you do a commit.
Now unless the delete statements you are executing contain a rownum restriction like John suggested the chance of any of them deleting exactly 10000 rows has got to be close to zero.
If your delete deletes 10001 rows then the IF will not be true and it won't commit.
You need to:
a) add the current sql%rowcount to counter instead of overwriting it each time.
b) check if counter >= 100000
|
|
|
|