PL/SQL Anonymous Block to delete N rows at a time.

General

   This is an example of a common task, of deleting a large number of rows from a table.

One cannot delete millions of rows from a table with a simple delete, because this might be over rollback segment limits.

   The solution would be to delete and issue a commit every N rows at a time.

   In this example, every 5000 deleted rows there is a commit, and an entry is written to a log table.

PL/SQL Anonymous block  - simple version

  DECLARE    
    v_effectedRows NUMBER;
    v_sqlStr VARCHAR2(1000);
  BEGIN
    v_sqlStr := 'DELETE FROM MY_TABLE WHERE date_of_call = ''20150101'' AND field_a IN (''0'',''000000'',''99'') AND is_completed = 0 AND ROWNUM < 200000';
    DBMS_OUTPUT.put_line('v_sqlStr: '|| v_sqlStr); 
    v_effectedRows := 1;
    WHILE v_effectedRows > 0 LOOP
       EXECUTE IMMEDIATE v_sqlStr;
       v_effectedRows := SQL%rowcount;
       commit;
    END LOOP;   
   
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('Error in procedure deleteFromTable.'|| SUBSTR(SQLERRM, 1, 1000)); 
  END ;

PL/SQL Anonymous block  - with logging to a table

  DECLARE

    v_date VARCHAR2(8);

    v_effectedRows NUMBER;

    v_deleteRows NUMBER;

    msg_text VARCHAR2(1000);

    v_rowCounter NUMBER;

    v_tableName VARCHAR2(30);

    v_sqlStr VARCHAR2(1000);

  BEGIN

    v_tableName :='MY_TABLE';

    v_date := '20140101';

    v_deleteRows :=1001;

   

    v_effectedRows := 1; 

    v_rowCounter := 0; 

   

    v_sqlStr := 'DELETE FROM '||v_tableName||' WHERE ROWNUM < '|| v_deleteRows ||' AND last_analyzed < TO_DATE('||v_date||','||'''YYYYMMDD'''||')';

    INSERT INTO MY_LOG (PROCEDURE_NAME ,DATA,UPDATE_DATE) VALUES('MANUAL_DELETE FROM '||v_tableName,'Runing SQL: '||v_sqlStr, SYSDATE);

   

    WHILE v_effectedRows > 0 LOOP

       EXECUTE IMMEDIATE v_sqlStr;

       v_effectedRows := SQL%rowcount;

       v_rowCounter := v_rowCounter + v_effectedRows;

       msg_text := v_rowCounter||' rows deleted from table '||v_tableName;

       INSERT INTO MY_LOG (PROCEDURE_NAME ,DATA,UPDATE_DATE) VALUES('MANUAL_DELETE FROM '||v_tableName,msg_text, SYSDATE);

       commit;

    END LOOP;   

    INSERT INTO MY_LOG (PROCEDURE_NAME ,DATA, UPDATE_DATE) VALUES('MANUAL_DELETE FROM '||v_tableName,'Finished Successfully', SYSDATE);

    commit;

   

  EXCEPTION

    WHEN OTHERS THEN

      DBMS_OUTPUT.put_line('Error in procedure deleteFromTable.'|| SUBSTR(SQLERRM, 1, 1000)); 

  END ;