Create or replace procedure Archive_data is

commit_interval NUMBER(10) := 5000;

count_rec NUMBER(10) := 0;

BEGIN

FOR DEL_REC IN(SELECT * from TABLE_A 
where event_time < ADD_MONTHS (SYSDATE, -3)) 

LOOP 

INSERT INTO TABLE_A_ARCHIVE VALUES(DEL_REC.column1,DEL_REC.column2,DEL_REC.column3...);

count_rec := count_rec + 1;
    
IF count_rec >= commit_interval 
    
THEN
        
COMMIT;
        
count_rec := 0;
    
END IF;

COMMIT;

DELETE FROM TABLE_A WHERE event_time = DEL_REC.event_time;
    
count_rec := count_rec + 1;
    
IF count_rec >= commit_interval 
    
THEN
        
COMMIT;
        
count_rec := 0;
    
END IF;

END 
LOOP; 

DBMS_OUTPUT.PUT_LINE('Deleted '||commit_interval||' records from TABLE_A'); 

END;

DECLARE

commit_interval NUMBER(10) := 5000;

count_rec NUMBER(10) := 0;

BEGIN

FOR DEL_REC IN(SELECT * from TABLE_B 
where log_DATE < ADD_MONTHS (SYSDATE, -12)) 

LOOP 

INSERT INTO TABLE_B_ARCHIVE VALUES(DEL_REC.column1,DEL_REC.column2,DEL_REC.column3...);

count_rec := count_rec + 1;
    
IF count_rec >= commit_interval 
    
THEN
        
COMMIT;
        
count_rec := 0;
    
END IF;

COMMIT;

DELETE FROM TABLE_B WHERE log_date = DEL_REC.log_date;
    
count_rec := count_rec + 1;
    
IF count_rec >= commit_interval 
    
THEN
        
COMMIT;
        
count_rec := 0;
    
END IF;

END 
LOOP; 

DBMS_OUTPUT.PUT_LINE('Deleted '||commit_interval||' records from TABLE_B'); 

END;
/