Home » SQL & PL/SQL » SQL & PL/SQL » How To Count Total number of rows deleted by DELETE and reset counter after some condition (Oracle11g)
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
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 #668803 is a reply to message #668801] Thu, 15 March 2018 04:21 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
I am not at all sure what you are trying to achieve with this. However, unless your DELETE statement deletes exactly 10000 rows, the test counter=10000 will never pass.
Can you not use a DELETE statement such as
 delete from .... where ... and rownum <=10000;
Re: How To Count Total number of rows deleted by DELETE and reset counter after some condition [message #668804 is a reply to message #668803] Thu, 15 March 2018 04:51 Go to previous messageGo to next message
ssyr
Messages: 65
Registered: January 2017
Member
Thanks for your suggestion.

Please tell me some solution on this its very urgent for me.

Re: How To Count Total number of rows deleted by DELETE and reset counter after some condition [message #668805 is a reply to message #668804] Thu, 15 March 2018 04:53 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
I have already given you one suggesdtion, which you have ignored.

If you have an urgent problem, you will have to pay for consultancy services.
Re: How To Count Total number of rows deleted by DELETE and reset counter after some condition [message #668806 is a reply to message #668805] Thu, 15 March 2018 05:01 Go to previous messageGo to next message
ssyr
Messages: 65
Registered: January 2017
Member
Hi ,

But ROWNUM<=10000 is it good option?
How to retrieve next records then?
Re: How To Count Total number of rows deleted by DELETE and reset counter after some condition [message #668807 is a reply to message #668804] Thu, 15 March 2018 05:02 Go to previous messageGo to next message
flyboy
Messages: 1886
Registered: November 2006
Senior Member
Why do you think that the counter "reaches" some value? It is set to the same value as RECORDSDELETED in each LOOP iteration.

In order to find out its value, what about introducing ELSE part and use DBMS_OUTPUT also there? Or simply DBMS_OUTPUT its value before that IF statement?

As it is not used in the code snippets, I cannot suggest any "solution" of this mystery code.
Re: How To Count Total number of rows deleted by DELETE and reset counter after some condition [message #668808 is a reply to message #668806] Thu, 15 March 2018 05:04 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
I do not know (a) what you are trying to do, (b) why you are doing it, (c) the code you are running.

When you hire your consultant, you will need to tell him all of this, and more.
Re: How To Count Total number of rows deleted by DELETE and reset counter after some condition [message #668811 is a reply to message #668808] Thu, 15 March 2018 05:57 Go to previous messageGo to next message
ssyr
Messages: 65
Registered: January 2017
Member
Hi ,

My requirement is I have to delete the records from all tables .At the same time I have to commit after every 10000 rows deletion that why I have use counter in the loop. But That is not checked at runtime/

Some logical errors in that .
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 messageGo to next message
cookiemonster
Messages: 13894
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
Re: How To Count Total number of rows deleted by DELETE and reset counter after some condition [message #668814 is a reply to message #668813] Thu, 15 March 2018 06:13 Go to previous message
ssyr
Messages: 65
Registered: January 2017
Member
Thank you for your suggestion I will modify the code .And will check.

Thanks once again.
Previous Topic: Help on running difference sql query (2 merged by CM)
Next Topic: Arranging row data in column
Goto Forum:
  


Current Time: Fri Sep 18 09:54:29 CDT 2020