Code Example.Multi Delete.

General
PL/SQL block to DELETE, UPDATE, INSERT many rows to a Table in a Loop.

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

DELETE in Loop 

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

Save below PL/SQL block into a file named multi_delete.sql.

DECLARE
  v_effected_rows NUMBER;
  v_delete_rows NUMBER;
  v_row_counter NUMBER;
  v_sql_str VARCHAR2(1000);

BEGIN
  v_delete_rows :=10001;
  v_effected_rows := 1; 
  v_row_counter := 0; 

  v_sql_str := 'DELETE FROM MY_TABLE WHERE ROWNUM < '|| v_delete_rows ||' AND my_column_value = '||'''kuku''';
  DBMS_OUTPUT.put_line(v_sql_str); 
  WHILE v_effected_rows > 0 LOOP
   
    EXECUTE IMMEDIATE v_sql_Str;
    v_effected_rows := SQL%rowcount;
    v_row_counter := v_row_counter + v_effected_rows;
    commit;
  END LOOP;   
  commit;
  DBMS_OUTPUT.put_line('Number of Deleted rows: '||v_row_counter); 
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('Error in PL/SQL Block.'|| SUBSTR(SQLERRM, 1, 1000)); 
END ;
/

@multi_delete.sql

The problem with with DBMS_OUTPUT, is that the flash takes place only when PL/SQL block completes.
To log the ongoing updates, need to perform writes to log table.

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

DELETE in Loop with with logging to a log table

===================================
Save below PL/SQL block into a file, multi_delete.sql

multi_delete.sql

DECLARE
  v_date VARCHAR2(8);
  v_effected_rows NUMBER;
  v_delete_rows NUMBER;
  v_row_counter NUMBER;
  v_table_name VARCHAR2(30);
  v_sql_str VARCHAR2(1000);
  v_module_name VARCHAR2(30);
  v_msg_text VARCHAR2(1000);

BEGIN
  v_table_name :='MY_TABLE';
  v_date := '20140101';
  v_delete_rows :=1001;
  v_effected_rows := 1; 
  v_row_counter := 0;
  v_module_name := 'MULTI DELETE';

  v_sql_str := 'DELETE FROM '||v_table_name||' WHERE ROWNUM < '|| v_delete_rows ||' AND last_analyzed < TO_DATE('||v_date||','||'''YYYYMMDD'''||')';
  v_msg_text  := 'MANUAL_DELETE FROM '||v_table_name|| '. Running SQL: '||v_sql_str;
  --INSERT INTO MY_LOG (PROCEDURE_NAME, DATA, UPDATE_DATE) 
  --VALUES(v_module_name,v_msg_text, SYSDATE);
  writeTrace(v_module_name,v_msg_text);

  WHILE v_effected_rows > 0 LOOP
    EXECUTE IMMEDIATE v_sql_str;
    v_effected_rows := SQL%ROWCOUNT;
    v_row_counter := v_row_counter + v_effected_rows;
    v_msg_text := v_row_counter||' rows deleted from table '||v_table_name;
    --INSERT INTO MY_LOG (PROCEDURE_NAME ,DATA, UPDATE_DATE) 
    --VALUES(v_module_name, v_msg_text, SYSDATE);
    writeTrace(v_module_name,v_msg_text);

    commit;
  END LOOP; 
  
  v_msg_text := 'MANUAL_DELETE FROM '||v_table_name||' Finished Successfully';
  --INSERT INTO MY_LOG (PROCEDURE_NAME ,DATA, UPDATE_DATE) 
  --VALUES(v_module_name, v_msg_text, SYSDATE);
  writeTrace(v_module_name,v_msg_text);
  commit;

EXCEPTION
  WHEN OTHERS THEN
    v_msg_text := 'Error in procedure '||v_module_name||'. Error Details: '|| SUBSTR(SQLERRM, 1, 900); 
    writeTrace(v_module_name,v_msg_text);
    DBMS_OUTPUT.put_line(v_msg_text ); 
END ;
/


To run the SQL:

@multi_delete.sql

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

Update, and if no data found do INSERT

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

Example of doing UPDATE, and if no data found then doing INSERT.

DECLARE    
  v_customerId    CUSTOMER_GMT_OFFSET.customer_id%TYPE;
  v_gmtOffset     CUSTOMER_GMT_OFFSET.gmt_offset%TYPE;

BEGIN
  -- update record in the table
  UPDATE MY_TABLE
     SET column_A = 'A'
  WHERE column_B = 'B';
    
    -- In case no data found
    IF (SQL%ROWCOUNT = 0) THEN
      INSERT INTO MY_TABLE (column_A, column_b)
             VALUES ('A', 'B');
    END IF;
    writeTrace('updateOrInsert', 'MY_TABLE was updated');
    
  EXCEPTION
    WHEN OTHERS THEN    
      writeTrace('updateOrInsert',
                     'Error updating MY_TABLE '||' ; ERROR: '||SQLERRM);
  END;


writeTrace() performs logging to a log table.

==========================================
Multi Delete using BULK COLLECT and FORALL
==========================================
1. MULTI_DELETE Procedure
-------------------------------------------------------------
-- Input Parameters:
-- param1: How many days back to purge
-- param2: How many rows to commit
-------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY ADMIN_UTIL_PKG IS

  PROCEDURE MULTI_DELETE(pDaysToKeep IN NUMBER, pRowsToCommit IN NUMBER) IS
  -------------------------------------------------------------
  -- Propose : Procedure to delete the old record from TABLE MY_TALE.
  -- Input Parameters:
  -- param1: How many days back to purge
  -- param2: the size of a bulk to delete, default is 10,000
  -------------------------------------------------------------
    v_date_to_check        DATE := SYSDATE - pDaysToKeep;
    v_counter              NUMBER(12):=0;
    v_str                  VARCHAR2(100):='';
    v_bulk                 NUMBER(10):=0;
    v_module_name          VARCHAR2(30) := 'MULTI_DELETE';
    v_msg_str              VARCHAR2(1000);

    CURSOR purge_population is
    SELECT customer_id FROM CUSTOMER
    WHERE (effective_date <= v_date_to_check);

    TYPE aat_key1 IS TABLE OF CUSTOMER.customer_id%TYPE INDEX BY PLS_INTEGER;

    aat_key1s aat_key1;

    BEGIN
       v_msg_str := 'Procedure Started at: '||TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS');
       UTIL_PKG.write_log(v_module_name,v_msg_str);
       v_msg_str := 'Parameters: days_to_keep: '||pDaysToKeep||' rows_to_commit: '||pRowsToCommit;       
       UTIL_PKG.write_log(v_module_name,v_msg_str);
       
       v_str:='DELETE from CUSTOMER where customer_id=:1';

       OPEN purge_population;
       LOOP
       BEGIN
        FETCH purge_population BULK COLLECT INTO aat_key1s limit pRowsToCommit;

        FORALL i in 1..aat_key1s.count SAVE EXCEPTIONS

          EXECUTE IMMEDIATE v_str USING aat_key1s(i);
          v_bulk:=v_bulk+1;
          v_counter := v_counter + aat_key1s.count;

          COMMIT;
          UTIL_PKG.write_log(v_module_name,'current bulk: '||to_char(v_bulk));
          EXCEPTION
               WHEN others THEN
                  COMMIT;
                  UTIL_PKG.write_log(v_module_name,'# of bulks: '||to_char(v_bulk));
                  UTIL_PKG.write_log(v_module_name,'# of rows deleted: ' || TO_CHAR(v_counter));
                  UTIL_PKG.write_log(v_module_name,'Procedure Failed with Error: '||SQLCODE||' '||SQLERRM);
                  RAISE_APPLICATION_ERROR(-20000,v_module_name||' '||SQLCODE||' '||SQLERRM);
         END;
         EXIT WHEN purge_population%NOTFOUND;
      END LOOP;
      CLOSE purge_population;
      COMMIT;
      UTIL_PKG.write_log(v_module_name,'# of bulks: '||to_char(v_bulk));
      UTIL_PKG.write_log(v_module_name,'# of rows deleted: ' || TO_CHAR(v_counter));
      UTIL_PKG.write_log(v_module_name,'Procedure Ended: ' || TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));

    EXCEPTION
      WHEN others then
         RAISE_APPLICATION_ERROR(-20000,'SGA_W_PSMS_SUBSCRIBER ' || SQLCODE || ' ' || SQLERRM);
    END SGA_SUBSCRIBER_PURGE;

END ADMIN_UTIL_PKG;

2. MULTI_DELETE Procedure job

DECLARE

   v_job_number NUMBER(10);

BEGIN

 DBMS_JOB.SUBMIT (JOB => v_job_number, 

                  WHAT => 'ADMIN_UTIL_PKG.multi_delete(365,10000);', 

                  NEXT_DATE => TRUNC(SYSDATE + 1)+4/24, 

                  INTERVAL => 'TRUNC(SYSDATE + 1)+4/24'

 );

 COMMIT;

END;

/

3. MULTI_DELETE Utility write_log procedure.
PROCEDURE WRITE_LOG(p_procedure_name IN VARCHAR2,
                p_msg            IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO TRACE_LOG(PROCEDURE_NAME, DATA, TS_LAST_MODIFIED)
  VALUES(p_procedure_name, p_msg, SYSDATE);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END WRITE_LOG;

4. Log table definition

CREATE TABLE TRACE_LOG

(

  procedure_name   VARCHAR2(100) NOT NULL,

  data             VARCHAR2(4000) NOT NULL,

   ts_last_modified DATE NOT NULL

)

TABLESPACE MY_TBS

  (

    initial 1M

    next 1M

    minextents 1

    maxextents unlimited

  );

Multi Delete with Bulk Collect and FORALL

MULTI_DELETE_WITH_BULK PROCEDURE IS

  v_last_day_to_run DATE;

  TYPE DATE_S IS TABLE OF MY_TABLE.start_date%TYPE;

  date_to_delete DATE_S;

  CURSOR to_delete IS

  SELECT trunc(start_date)

    FROM MY_TABLE

   WHERE TRUNV(start_date) < v_last_day_to_run;

BEGIN

  DATE v_last_day_to_run := TRUNC(sysdate);

  OPEN to_delete;

  LOOP

    BEGIN

      FETCH to_delete BULK COLLECT INTO date_to_delete LIMIT 50000;

      FORALL i IN 1 .. date_to_delete.COUNT

      DELETE FROM MY_TABLE MY_TABLE

       WHERE TRUNC(MY_TABLE.start_date) = date_to_delete(i);

      COMMIT;

      EXIT WHEN to_delete%NOTFOUND;

    END;

  END LOOP;

  COMMIT;

  CLOSE to_delete;

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

Anonymous block, Delete in Loop 

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

--==============================

--Pre execution steps:

--==============================

CREATE TABLE TEMP_SUBS_TO_DELETE AS SELECT current_vlr, imsi, msisdn, is_subscriber FROM SGA_W_PSMS_SUBSCRIBER WHERE 1=2;

INSERT  /*+ APPEND */  INTO TEMP_SUBS_TO_DELETE (current_vlr, imsi, msisdn,is_subscriber)

SELECT current_vlr, imsi, msisdn, 0 FROM SGA_W_PSMS_SUBSCRIBER WHERE current_vlr LIKE '1%';

commit;

--==============================
-- actual delete

--==============================

DECLARE

  v_rows_handled NUMBER;

  v_error_msg        VARCHAR2(1000);

  v_error_code       NUMBER;  

BEGIN

  v_rows_handled := 1;

  WHILE v_rows_handled > 0 LOOP

    UPDATE TEMP_SUBS_TO_DELETE SET is_subscriber = 1 WHERE is_subscriber = 0 AND ROWNUM < 10001;

    commit;

    DELETE FROM SGA_W_PSMS_SUBSCRIBER_TEMP WHERE SGA_W_PSMS_SUBSCRIBER_TEMP.msisdn IN (SELECT DISTINCT msisdn FROM TEMP_SUBS_TO_DELETE WHERE is_subscriber = 1);

    commit;

    UPDATE TEMP_SUBS_TO_DELETE SET is_subscriber = 2 WHERE is_subscriber=1;

    v_rows_handled := SQL%ROWCOUNT;

  commit;

  

  INSERT INTO SGA_W_LOG(procedure_name, data, ts_last_modified)

  VALUES ('MANUAL_DELETE_FROM_SUBS', 'Rows Deleted: '||TO_CHAR(v_rows_handled),SYSDATE);

  commit;

  END LOOP;

EXCEPTION

  WHEN OTHERS THEN

    v_error_code := SQLCODE;

     v_error_msg := SUBSTR(SQLERRM,1,1000);

    INSERT INTO SGA_W_LOG(procedure_name, data, ts_last_modified)

    VALUES ('MANUAL_DELETE_FROM_SUBS', 'Unexpected Error: '||v_error_msg, SYSDATE);

    commit;  

END;  

/

--==============================

--Post execution steps:

DROP TABLE TEMP_SUBS_TO_DELETE;

--==============================