create or replace PROCEDURE AGENDA_LIMPEZA_DADOS(P_OBJETO IN VARCHAR2,
                                              P_DTAGENDAMENTO IN DATE,
                                              P_HREXECINIC IN VARCHAR2,
                                              P_ACAO  IN VARCHAR2,
                                              P_ERRO OUT VARCHAR2)
AS
V_JOB       NUMBER;
V_NEXT_DATE DATE;
V_WHAT      VARCHAR2(5000);
V_EXCEPTION VARCHAR2(5000);
V_JOBNO     NUMBER;

CURSOR C_JOBS(V_OBJETO IN VARCHAR2) IS
SELECT JOB
  FROM ALL_JOBS
 WHERE WHAT LIKE '%'||V_OBJETO||'%' ;                                              

BEGIN
   OPEN C_JOBS (P_OBJETO);
      LOOP
      FETCH C_JOBS INTO V_JOB;
      EXIT  WHEN C_JOBS%NOTFOUND;
      
      DBMS_JOB.REMOVE(V_JOB);
   
   END LOOP;
   
   IF P_ACAO = 'INCLUSAO' THEN
      IF P_HREXECINIC IS NOT NULL THEN 
   
        V_WHAT      := NULL;
        V_WHAT      := P_OBJETO||'();';
        V_NEXT_DATE := NULL;
        V_NEXT_DATE := TO_DATE((to_char(TO_DATE(P_DTAGENDAMENTO) ,'DD/MM/YYYY')||' '||SUBSTR(P_HREXECINIC,0,2)||':'||SUBSTR(P_HREXECINIC,3)||':00'),'DD/MM/YYYY HH24:MI:SS');
                  
        BEGIN
           DBMS_JOB.SUBMIT(V_JOBNO, V_WHAT, V_NEXT_DATE);
           V_EXCEPTION := '';
        EXCEPTION
           WHEN OTHERS THEN
              V_EXCEPTION := CHR(13)||'******TRATAMENTO DE EXCESSAO CUSTOMIZADO******'||CHR(13)||'<<SQLERRM>> '  ||SQLERRM||CHR(13)||'<<SQLCODE>> '  ||SQLCODE||CHR(13)||'***********************************************'||CHR(13)||CHR(13);
        END;   
     
        COMMIT;     
      END IF;
   END IF; 
   
   COMMIT;
   P_ERRO := V_EXCEPTION;
END;
/
-- VERSAO --
BEGIN ATUVEROBJ('AGENDA_LIMPEZA_DADOS','6.39.006'); END;
/
