And then deletes these rows using FORALL i IN 1.. DELETE
Another procedure is writing to a log table.
===========================================
Procedure to delete N rows at a time.
===========================================
CREATE OR REPLACE PROCEDURE PURGE_TABLE(p_num_of_days IN NUMBER) IS
v_updated_rows NUMBER := 0;
v_loop_num NUMBER := 0;
v_rows_to_process NUMBER := 50000;
TYPE rowid_type is table of rowid index by binary_integer;
rowids rowid_type;
CURSOR rowid_cursor(p_num_of_days IN NUMBER) IS
SELECT ROWID
FROM MY_TABLE
WHERE upd_date < SYSDATE - p_num_of_days;
BEGIN
UTILS.WRITE_LOG('PURGE_TABLE', 'Procedure Started, p_num_of_days = ' || p_num_of_days);
v_loop_num := v_loop_num + 1;
OPEN rowid_cursor(p_num_of_days);
LOOP
FETCH rowid_cursor BULK COLLECT
INTO rowids
LIMIT v_rows_to_process;
IF rowids.COUNT = 0 THEN
UTILS.WRITE_LOG('PURGE_TABLE','Cursor finished');
EXIT;
ELSE
UTILS.WRITE_LOG('PURGE_TABLE',
'Cursor opened:|| rowids.COUNT || ' rows'); '
FORALL i IN 1 .. rowids.COUNT
DELETE MY_TABLE
WHERE ROWID = rowids(i);
v_updated_rows := v_updated_rows + SQL%ROWCOUNT;
COMMIT;
UTILS.WRITE_LOG('PURGE_TABLE',
'Loop: ' || v_loop_num|| ' - ' ||v_updated_rows);
v_loop_num := v_loop_num + 1;
EXIT WHEN rowid_cursor%NOTFOUND;
DBMS_LOCK.SLEEP(5);
END IF;
END LOOP;
/* Free cursor used by the query. */
CLOSE rowid_cursor;
UTILS.WRITE_LOG('PURGE_TABLE',
'Procedure Ended, updated rows:' ||v_updated_rows);
EXCEPTION
WHEN OTHERS THEN
UTILS.WRITE_LOG('PURGE_TABLE','Error: ' || SQLERRM);
END PURGE_TABLE;
/
===========================================
Procedure to write to a log table.
===========================================
PROCEDURE WRITE_LOG(p_module_name IN VARCHAR2, p_msg_text IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
-- Purpose : Insert new record to the trace table
BEGIN
INSERT INTO MY_TRACE(MODULE, msg_date, msg_text)
VALUES(p_module_name , SYSDATE, p_msg_text);
COMMIT;
END WRITE_LOG;
