title: Commit every 1000 rows
body: <p>I am looking to do a commit every 1000 rows until all records are deleted, we have over a million records to be deleted.</p>
  
  <p>Originally:</p>
  
  <pre><code>private static final String DELETE_OLD_REPORTS_FROM_REPORTING =
  -            "DELETE FROM A_REPORTING\n" +
  -            "WHERE ID IN(" +
  -            "SELECT ID FROM A_REPORTING\n" +
  -            "WHERE STATUS = 'LOADED'\n" +
  -            "AND CREATE_DT &lt; TO_DATE(:createdDate, 'dd-mon-yyyy'))";
  </code></pre>
  
  <p>I was thinking of doing something like this:</p>
  
  <pre><code>"BEGIN\n" +
                  "LOOP\n" +
                      "DELETE FROM A_REPORTING\n" +
                      "WHERE ID IN(" +
                      "SELECT ID FROM A_REPORTING\n" +
                      "WHERE STATUS = 'LOADED'\n" +
                      "AND CREATE_DT &lt; TO_DATE(:createdDate, 'dd-mon-yyyy')\n+" +
                      "AND ROWNUM &lt;= 10000);\n" +
                      "EXIT WHEN SQL%rowcount &lt; 9999;\n" +
                      "COMMIT;\n" +
                  "END LOOP;\n"+
              "COMMIT;\n" +
              "END";
  </code></pre>
  
  <p>However, is there a better approach to doing this? The reason for this is because we were getting a ORA-01555 error:</p>
  
  <blockquote>
    <p>ORA-01555: snapshot too old: rollback segment number %n with name "%segname" too small.</p>
  </blockquote>