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