procedure to delete several records from several tables

[PAVB]

P.S.: can't do bulk insert since rows are created in those tables non-stop. If I do bulk insert I'll lose rows that are created when create a new table --> move the desired records --> delete old table --> rename new tables

Okay ... when everything fails you have to rely on "delete" to "delete" rows.

Please find below a template I foiund years ago in Metalink, feel free to customize as needed.

------------------------------------------------------  
-- Create procedure
--
-- Remember to replace empno=' || empno
-- by your own purge condition
CREATE OR REPLACE PROCEDURE delete_tab (tablename IN VARCHAR2, 
                                            empno IN NUMBER  , 
                                            nrows IN NUMBER    ) IS
 sSQL1    VARCHAR2(2000);
 sSQL2    VARCHAR2(2000);
 nCount    NUMBER; 
BEGIN
  nCount := 0;
  sSQL1:='delete from '|| tablename || 
         ' where ROWNUM < ' || nrows || ' and empno=' || empno; 
  sSQL2:='select count(ROWID) from ' || tablename || 
         ' where empno= ' || empno;
  LOOP
    EXECUTE IMMEDIATE sSQL1;
    EXECUTE IMMEDIATE sSQL2 INTO nCount;
    DBMS_OUTPUT.PUT_LINE('Existing records: ' || to_char(ncount) );
    commit;    
    EXIT WHEN nCount = 0;
  END LOOP;
END delete_tab;
/
------------------------------------------------------  
-- Execute above created procedure
--
-- In this example we are asking
-- to delete from table BIG_EMP,
-- all rows belonging to empno=7369
-- in batches of 5000 rows
SQL> execute delete_tab('big_emp',7369,5000)
Existing records: 60537
Existing records: 55538
Existing records: 50539
Existing records: 45540
Existing records: 40541
Existing records: 35542
Existing records: 30543
Existing records: 25544
Existing records: 20545
Existing records: 15546
Existing records: 10547
Existing records: 5548
Existing records: 549
Existing records: 0