Code Example. Multi Delete from several Tables.

General
PL/SQL block to DELETE, UPDATE, INSERT many rows in several a Tables using FORALL.

===================================

Code

===================================

PROCEDURE MultiDeleteBulk(p_return_value OUT NUMBER,
                          p_chunk_size   IN NUMBER) IS

----------------------------------------------------------------------------------
-- Propose : Delete from Several tables in Bulk.
----------------------------------------------------------------------------------
  TYPE ROWID_RESULT_TABLE  IS TABLE OF ROWID                        INDEX BY BINARY_INTEGER;
  TYPE IMSI_RESULT_TABLE   IS TABLE OF SGA_W_SUBSCRIBER.imsi%TYPE   INDEX BY BINARY_INTEGER; 
  TYPE MSISDN_RESULT_TABLE IS TABLE OF SGA_W_SUBSCRIBER.msisdn%TYPE INDEX BY BINARY_INTEGER;

  v_rowid_result    ROWID_RESULT_TABLE;
  v_imsi_result     IMSI_RESULT_TABLE;
  v_msisdn_result   MSISDN_RESULT_TABLE;
    
  v_row_count      NUMBER;
  v_rows_to_delete NUMBER;

  v_date_to_check  DATE;
v_module_name    VARCHAR2(30);

BEGIN
  v_return_value := 0;
  v_module_name := 'MultiDeleteBulk';

  WRITE_TO_LOG(v_module_name, 'Procedure Started: ' || TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));

  IF p_chunk_size < 10000 THEN
    v_rows_to_delete := 10000
  ELSE
    v_rows_to_delete := p_chunk_size;
  END IF;    
  WRITE_TO_LOG('PurgeHomeBound', 'Deleting in chunks of : '||v_rows_to_delete);

  v_date_to_check := SYSDATE-7;
  WRITE_TO_LOG(v_module_name, 'Deleting Rows older than: '||TO_CHAR(v_date_to_check,'YYYYMMDD hh24:mi:ss'));

  -- Select the rows to be deleted.
  SELECT /*+ PARALLEL (SGA_W_SUBSCRIBER 6) */
         SGA_W_SUBSCRIBER.imsi, 
         SGA_W_SUBSCRIBER.msisdn, 
         SGA_W_SUBSCRIBER.ROWID 
    BULK COLLECT
    INTO v_imsi_result, v_msisdn_result, v_rowid_result
    FROM SGA_W_SUBSCRIBER
   WHERE is_roaming = 0
     AND non_purge_ind = 0
     AND GREATEST(NVL(ts_e_last_lu, ts_last_roamer_event), NVL(ts_last_roamer_event, ts_e_last_lu)) <  v_date_to_check
     AND ROWNUM < (v_rows_to_delete + 1); 

    --Delete from Table A
    FORALL rowidIndex IN NVL(v_rowid_result.FIRST, 1) .. NVL(v_rowid_result.LAST,0)
    DELETE FROM SGA_W_SUBSCRIBER
    WHERE ROWID = v_rowid_result(rowidIndex);

    v_row_count := SQL%ROWCOUNT;
    WRITE_TO_LOG(v_module_name, 'Number of rows Deleted from IPN_W_SRL_CONTROL: ' || v_row_count);                               

    --Delete from Table B
    FORALL imsiIndex IN NVL(v_imsi_result.FIRST, 1) .. NVL(v_imsi_result.LAST,0)
    DELETE FROM IPN_W_SRL_CONTROL
    WHERE imsi = v_imsi_result(imsiIndex);
      
    v_row_count := SQL%ROWCOUNT;
    WRITE_TO_LOG(v_module_name, 'Number of rows Deleted from IPN_W_SRL_CONTROL: ' || v_row_count);                               

    --Delete from Table C
    FORALL msisdniIndex IN NVL(v_msisdn_result.FIRST, 1) .. NVL(v_msisdn_result.LAST,0)
    DELETE FROM SGA_W_NUMBERS
    WHERE SGA_W_NUMBERS.mobile_number = v_msisdn_result(msisdniIndex);

    v_row_count := SQL%ROWCOUNT;
    WRITE_TO_LOG(v_module_name, 'Number of rows Deleted from SGA_W_NUMBERS: ' || v_row_count);                               
 
    COMMIT;
    
    WRITE_TO_LOG(v_module_name,'Number of rows deleted: ' || TO_CHAR(P_RetValue));
    WRITE_TO_LOG(v_module_name,'Completed Successfully at: '||TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));

EXCEPTION
  WHEN OTHERS THEN
    p_return_value := -1;
    RAISE_APPLICATION_ERROR(-20000, v_module_name||' '||'Unexpected Error: ' || SQLCODE || SQLERRM);
END MultiDeleteBulk;