Criando Jobs no PL/sql Developer

[mauryrv]

E aí galera, tudo tranquilo?
Preciso criar um schedule no pl/sql developer, porem como sou novo em pl/sql não sei como fazer. Preciso criar um job que seja executado todo dia 5 de cada mês as 23:00.

Espero que possam me ajudar!

[stcoutinho]

Olá mauryrv,

Não estou acostumado a usar o PL/SQL Developer para criar JOBS oracle. Eu prefiro executar eu mesmo a package DBMS_JOB para agendar um job.

Independente disso, dei uma xeretada na minha ferramenta e me parece que o processo é bem simples.Vou detalhar os passos, e lhe informo que a versão do meu PL/SQL Developer é a 8.04.1514.

Bem, vamos lá:

1) Entre no PL/SQL Developer, conectando-se à base de dados;
2) Na tela principal do seu PL/SQL, você vai notar uma coluna à esquerda, como uma espécie de "Object Explorer";
3) Navegue pela "árvore de objetos" dentro desta coluna, posicionando na pasta "DBMS_Jobs";
4) Posicionado nesta pasta, pressione o botão direito do mouse para aparecer o menu de opções. Escolha a opção NEW e pressione enter;
5) Deve aparecer uma pequena janela com o titulo CREATE DBMS_JOB. Você será solicitado a preencher algumas informações:
5.1)WHAT: O que deve ser executado (procedure), com eventuais parâmetros. Exemplo:

Selecionar tudo

BEGIN  SP_PURGA_XPTO_LOG_OPRO; END;

5.2)NEXT: A proxima data de execução. Informe a data e hora. Ex: 5/12/2012 23:00:00
5.3)INTERVAL : O intervalo de execução da rotina. Você deseja a cada dia cinco do mês, às 23 horas. Neste caso, informe este comando: TO_DATE('05/'||TO_CHAR(ADD_MONTHS(SYSDATE,1),'MM/RRRR')||' 23:00:00','DD/MM/RRRR HH24:MI:SS').
Para você entender o que esta função retorna, execute em uma janela à parte esta querie:

Selecionar tudo

SELECT SYSDATE,TO_DATE('05/'||TO_CHAR(ADD_MONTHS(SYSDATE,1),'MM/RRRR')||' 23:00:00','DD/MM/RRRR HH24:MI:SS') FROM DUAL

Você vai notar que independente da data atual, a querie retorna sempre o dia 05 do proximo mês, às 23 horas;
6) Feito tudo isso, aperte o botão APPLY da janela. Se existir algum erro de sintaxe, você deve receber uma mensagem de erro;
7) Se estiver tudo OK, você vai notar que abaixo da pasta DBMS_Job irá aparecer uma nova pastinha com um número. Este número corresponde ao JOB que você acabou de criar.

Lembre-se que você precisa monitorar periodicamente a rotina, para ver se o JOB está se auto agendando com sucesso. Eu sugiro até que a sua rotina grave logs de execução quando é disparada, especialmente quando ocorre algum erro.

Eu pessoalmente não gosto muito de deixar um job rodando "ad-eternum" no banco. Talvez seja algum trauma da antiga versão 8i - rsss. Um job agendado desta forma (auto-repeticao) geralmente ficava BROKEN com o decorrer do tempo e parava de funcionar.

O que eu costumo fazer é adicionar o agendamento do JOB na própria procedure.

Fiz um exemplo - logo abaixo- resumido de como isso poderia ser feito. A procedure abaixo se auto-agenda para executar uma unica proxima vez no futuro, tanto no caso de ter sucesso ou não em deletar dados de uma tabela. A cada nova execução, uma única data futura é novamente agendada para ser executada mais uma única vez. A vantagem é que sempre posso conferir no DBMS_JOB qual será a próxima data de execução da procedure.

Segue o código de exemplo:

Selecionar tudo

CREATE OR REPLACE PROCEDURE SP_PURGA_XPTO_LOG_OPRO
IS
  --
  wnum_job                     NUMBER;
  wprox_data                   DATE         := NULL;
  --- Cursor para remover JOB agendado da USER_JOBS
  CURSOR cv_remove_job_coleta IS
     SELECT job
       FROM user_jobs
      WHERE what LIKE 'BEGIN SP_PURGA_XPTO_LOG_OPRO%';
  --
  r_sql VARCHAR2(1000);

BEGIN
  ------
  OPEN cv_remove_job_coleta;
  LOOP
    FETCH cv_remove_job_coleta INTO wnum_job;
    EXIT WHEN cv_remove_job_coleta%NOTFOUND;
    DBMS_JOB.REMOVE(wnum_job);
    COMMIT;
  END LOOP;
  CLOSE cv_remove_job_coleta;

  DELETE
    FROM XPTO_LOG_OPRO
   WHERE DH_OPRO < SYSDATE-5;
  COMMIT;

  --- REAGENDANDO PARA PROXIMO DIA - 2:00 A
  SELECT TRUNC(SYSDATE+1)+2/24
    INTO wprox_data
    FROM DUAL;
  DBMS_JOB.SUBMIT(wnum_job,'BEGIN SP_PURGA_XPTO_LOG_OPRO; END;',wprox_data);
  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
       ROLLBACK;
       --- REAGENDANDO PARA PROXIMO DIA - 2:00 A
       SELECT TRUNC(SYSDATE+1)+2/24
         INTO wprox_data
         FROM DUAL;
       DBMS_JOB.SUBMIT(wnum_job,'BEGIN SP_PURGA_XPTO_LOG_OPRO; END;',wprox_data);
       COMMIT;
       RAISE_APPLICATION_ERROR(-20120,'ERROR ORACLE:SQLCODE: ' || SQLCODE || ' SQLERRM: ' || SQLERRM);
END SP_PURGA_XPTO_LOG_OPRO;

Abraços e boa sorte !

Sergio Coutinho