Saturday, 25 April 2015

Purform Bulk Deletes on Tables with Commit for Every N number of Rows

-- Delete Row from Table Sunil_temp and Commit Every 10000 rows.


CREATE OR replace PROCEDURE Delete_rows_SUNIL
AS

    ln_DelSize                      NUMBER := 10000;
    ln_DelCount                     NUMBER;

  BEGIN
    LOOP
           DELETE FROM SUNIL_TEMP
             WHERE
                  STARTDATETIME between to_date('04-01-2015 00:00:00','MM-DD-YYYY HH24:MI:SS') and to_date('04-25-2015 23:59:59','MM-DD-YYYY HH24:MI:SS')
              AND
                    rownum <= ln_DelSize;

ln_DelCount := SQL%ROWCOUNT;

             dbms_output.put_line(ln_DelCount);

            EXIT WHEN ln_DelCount = 0;
            COMMIT;

   END LOOP;

END;
 /

No comments:

Post a Comment