CREATE OR REPLACE PROCEDURE PROC_LIMPA_ZHU_LOG
AS
BEGIN
DELETE FROM ZHU_LOG WHERE TIMESTAMP < SYSDATE-31;
END;
/


DECLARE
   V_JOB_EXISTS NUMBER;
BEGIN
   SELECT COUNT(*) INTO V_JOB_EXISTS
     FROM USER_SCHEDULER_JOBS
    WHERE JOB_NAME = 'LIMPA_ZHU_LOG';

   IF V_JOB_EXISTS > 0 THEN
      DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'LIMPA_ZHU_LOG');
   END IF;

   DBMS_SCHEDULER.CREATE_JOB (
      job_name             => 'LIMPA_ZHU_LOG',
      job_type             => 'PLSQL_BLOCK',
      job_action           => 'BEGIN PROC_LIMPA_ZHU_LOG(); END;',
      repeat_interval      => 'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=4;BYMINUTE=15', 
      enabled              =>  TRUE,
      comments             => 'Apagar registro de mais do 31 dias na tabela de log do zeedhi');
END;
/