Code Example - Delete rows with BULK COLLECT

=====================
General
=====================

This is a code example of a job which is calling a generic procedure, which is calling a dedicated procedure, to delete data from tables using BULK_COLLECT

=====================
Code
=====================

Job: 

Generic Procedure:

Dedicated Procedure:  

=====================
Job
=====================
DECLARE
   v_job_number NUMBER(10);
BEGIN
 DBMS_JOB.SUBMIT (JOB => v_job_number, 
                  WHAT => 'ADMIN_UTIL.CALL_PURGE_COUNT_HIST(5,20000);', 
                  NEXT_DATE => TRUNC(SYSDATE + 1)+3/24+30/1440, 
                  INTERVAL => 'TRUNC(SYSDATE + 1)+3/24+30/1440'
 );
 COMMIT;
END;
/

=====================
Generic Procedure
=====================
CREATE OR REPLACE PACKAGE BODY ADMIN_UTIL  IS
  ------------------------------------
  PROCEDURE CALL_PURGE_COUNT_HIST(p_days IN NUMBER, p_delete IN NUMBER) IS
    v_module_name    SGA_W_LOG.procedure_name%TYPE;
    v_msg_text      SGA_W_LOG.data%TYPE;
    v_rows_deleted        NUMBER;
    v_total_rows_deleted  NUMBER;

  BEGIN
    v_module_name := 'ADMIN_UTIL.CALL_PURGE_COUNT_HIST';
    v_rows_deleted := 1;
    v_total_rows_deleted := 0;

    v_msg_text := 'Produre is Starting';
    SGA_PKG.write_sga_w_log(v_module_name, v_msg_text);

    v_msg_text := 'Running Delete with Parameters: Keep Days: '||p_days||' '||'Delete Chunk: '||p_delete;
    SGA_PKG.write_sga_w_log(v_module_name, v_msg_text);

    WHILE v_rows_deleted > 0 LOOP
      GA_PKG.PurgeCountersHistory(v_rows_deleted,p_days, p_delete);
      v_total_rows_deleted := v_total_rows_deleted + v_rows_deleted;
    END LOOP;
    
    v_msg_text := 'Number of Deleted Rows: '||TO_CHAR(v_total_rows_deleted);
    SGA_PKG.write_sga_w_log(v_module_name, v_msg_text);

    v_msg_text := 'Produre has Finished';
    SGA_PKG.write_sga_w_log(v_module_name, v_msg_text);

  EXCEPTION
    WHEN OTHERS THEN
      v_msg_text := 'Error in procedure '||v_module_name||'. Error Details: '|| SUBSTR(SQLERRM, 1, 900);
      SGA_PKG.write_sga_w_log(v_module_name,v_msg_text);
  END CALL_PURGE_COUNT_HIST;
  ------------------------------------
END ADMIN_UTIL;

=====================
Dedicated Procedure
=====================

CREATE OR REPLACE PACKAGE BODY GA_PKG  IS

  PROCEDURE PurgeCountersHistory(p_retvalue OUT NUMBER,p_days IN NUMBER, p_delete IN NUMBER) IS

   last_date DATE;

   TYPE MYROWIDRESULT IS TABLE OF  ROWID INDEX BY BINARY_INTEGER ;

   v_rowidresult  MYROWIDRESULT;

  BEGIN

    last_date := SYSDATE - p_days;

p_retvalue := 0;

   -- SELECT  ROWID FOR RECORDS NEED TO BE DELETED

    SELECT  ROWID

      BULK COLLECT

      INTO v_rowidresult

      FROM COUNTERS_HISTORY

     WHERE ts_last_modified < last_date

       AND rownum < (p_delete +1); -- SO THE DELETE WILL EXACTLY AS REQUESTED

   -- DELETE ROWS WHICH THEIR LAST LOCATION UPDATE WAS P_HOURS BEFORE.

   BEGIN

        FORALL ROWIDINDEX IN NVL(V_ROWIDRESULT.FIRST,1)..NVL(V_ROWIDRESULT.LAST,0)

        DELETE FROM COUNTERS_HISTORY WHERE ROWID = V_ROWIDRESULT(ROWIDINDEX) ;

        p_retvalue :=  SQL%ROWCOUNT;

COMMIT;

   EXCEPTION

      WHEN OTHERS THEN

   p_retvalue := -1;

         RAISE_APPLICATION_ERROR(-20000,'PURGE COUNTERS_HISTORY, DELETE ROWS ' || SQLCODE || SQLERRM);

   END;

   COMMIT;

-- INSERT ROW TO LOG TABLE WITH THE NUMBER OF ROWS WHICH WERE DELETED

   BEGIN

      INSERT INTO SGA_W_LOG (procedure_name, data, ts_last_modified)

 VALUES ('GA_PKG.PURGECOUNTERSHISTORY', TO_CHAR(p_retvalue), SYSDATE);

          COMMIT;

   EXCEPTION

      WHEN OTHERS THEN

         RAISE_APPLICATION_ERROR(-20000,'PURGE COUNTERS_HISTORY, INSERT ROW TO THE LOG ' || SQLCODE || ' ' || SQLERRM);

   END;

  EXCEPTION

   WHEN OTHERS THEN

      RAISE_APPLICATION_ERROR(-20000,'PURGE COUNTERS_HISTORY :' || SQLCODE || SQLERRM);

  END;

END GA_PKG ;