sql> create table test as select * from dba_objects;

table created.

sql> create or replace procedure deletetab
  2  /**
  3   ** usage: run the script to create the proc deletetab
  4   **        in sql*plus, type "exec deletetab('foo','id>=1000000','3000');"
  5   **        to delete the records in the table "foo", commit per 3000 records.
  6   **       condition with default value '1=1' and default commit batch is 10000.
  7   **/
  8  (
  9    p_tablename    in    varchar2,    -- the tablename which you want to delete from
 10    p_condition    in    varchar2 default '1=1',    -- delete condition, such as "id>=100000"
 11    p_count        in    varchar2 default '10000'    -- commit after delete how many records
 12  )
 13  as
 14   pragma autonomous_transaction;
 15   n_delete number:=0;
 16  begin
 17   while 1=1 loop
 18     execute immediate
 19       'delete from '||p_tablename||' where '||p_condition||' and rownum <= :rn'
 20     using p_count;
 21     if sql%notfound then
 22     exit;
 23     else
 24          n_delete:=n_delete + sql%rowcount;
 25     end if;
 26     commit;
 27   end loop;
 28   commit;
 29   dbms_output.put_line('finished!'); 
 30   dbms_output.put_line('totally '||to_char(n_delete)||' records deleted!');
 31  end;
 32  /

procedure created.


sql> insert into test select * from dba_objects;

6374 rows created.

sql> /

6374 rows created.

sql> /

6374 rows created.

sql> commit;

commit complete.

sql> exec deletetab('test','object_id >0','3000')
finished!
totally 19107 records deleted!

pl/sql procedure successfully completed.

create or replace procedure deletetab
(                                                                                                
  p_tablename    in    varchar2,    
-- the tablename which you want to delete from               
  p_condition    in    varchar2 default '1=1',   
 -- delete condition, such as "id>=100000"                    
  p_count        in    varchar2 default '10000'    
-- commit after delete how many records                      
)                                                                                                
as                                                                                               
 pragma autonomous_transaction;                                                                  
 n_delete number:=0;                                                                             
begin                                                                                            
 while 1=1 loop                                                                                  
   execute immediate                                                                             
     'delete from '||p_tablename||' 
where '||p_condition||' and rownum <= :rn'                   
   using p_count;                                                                                
   if sql%notfound then                                                                          
   exit;                                                                                         
   else                                                                                          
        n_delete:=n_delete + sql%rowcount;                                                       
   end if;                                                                                       
   commit;                                                                                       
 end loop;                                                                                       
 commit;                                                                                         
 dbms_output.put_line('finished!');                                                              
 dbms_output.put_line('totally '||to_char(n_delete)||' records deleted!');