Através do script abaixo, é possível realizar a limpeza da tabela de Integração TBINTEGRATIONNFCE, onde os registros são mantidos por 45 dias.
CREATE OR REPLACE
PROCEDURE LIMPARINTEGRATIONNFCE
AS
ROWCOUNT NUMBER;
DATETOCLEAR TIMESTAMP;
BEGIN
DATETOCLEAR := SYSDATE() - 45;
SELECT COUNT(*) INTO ROWCOUNT
FROM TBINTEGRATIONNFCE
WHERE STATUS = 1
AND INSERTDATE <= DATETOCLEAR;
WHILE (ROWCOUNT > 0)
LOOP
delete from TBINTEGRATIONNFCE where STATUS = 1 and INSERTDATE <= DATETOCLEAR AND ROWNUM <= 5000;
SELECT COUNT(*) INTO ROWCOUNT FROM TBINTEGRATIONNFCE WHERE STATUS = 1 AND INSERTDATE <= DATETOCLEAR;
END LOOP;
END;