Hi guys. I have a PL/SQL procedure as below to use loops to control data purging.Each loop execute to purge old data for 10 days until the remaining records left is only 30 days. However, I notice this code will purge data for exactly 30 days ago (SYSDATE - 30) which should be maintained in database.
DECLARE
days2keep PLS_INTEGER := 30;
startDate VARCHAR2 (50) := SYSDATE - 70;
sql_stmt VARCHAR2 (300);
i VARCHAR2 (50);
BEGIN
i := startDate ;
LOOP
sql_stmt :=
'BEGIN INSERT INTO HIST_TBL '
|| ' (SELECT * FROM TBL'
|| ' WHERE trandt BETWEEN '
|| i
|| 'AND' i + 10
|| '); DELETE FROM TBL'
|| ' WHERE trandt BETWEEN '
|| i
|| 'AND' i + 10
|| '; end;';
EXECUTE IMMEDIATE sql_stmt;
i := i + 10;
EXIT WHEN i >= SYSDATE - 30;
END LOOP;
1st loop: SYSDATE - 70 –> SYSDATE - 60 (Purged)
2nd loop: SYSDATE - 60 –> SYSDATE - 50 (Purged)
3rd loop: SYSDATE - 50 –> SYSDATE - 40 (Purged)
4th loop: SYSDATE - 40 –> SYSDATE - 30 (SYSDATE-30 is purged as well, how can avoid it?)