Category: plsql

De DBMS_JOB para DBMS_SCHEDULER: o Deploy

Nos últimos tempos tenho trabalhado com jobs em base de dados Oracle.
Mesmo que a base de dados utilizada fosse a 10g é ainda utilizado o DBMS_JOB. Recentemente li o [1] e descobri o DBMS_SCHEDULER.

Dou dois

exemplos de dificuldades que senti com o DBMS_JOB:

  • definir o intervalo de execução: fraca legibilidade, demasiado código para conseguir o agendamento desejado;
  • configurar o deploy de um job: apenas um campo númérico identifica univocamente o job na lista.

Neste post vou falar sobre o segundo ponto: o deploy de um job.

Oracle 9i: o DBMS_JOB

Na versão 9i a gestão de jobs é feita com o package DBMS_JOB.
Eis o script de deploy mais simples:

DECLARE
  V_JOB NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(
		JOB => V_JOB,
		WHAT => 'BEGIN MY_PKG.MY_EXEC_PROC; END;',
		NEXT_DATE => SYSDATE);
  commit;
  dbms_output.put_line('Created MY_PKG.MY_EXEC_PROC  job: '||v_job);
END;
/

Este script tem o seguinte problema: se executarmos o script outra vez o resultado vai ser a criação de outro job igual ao primeiro. Este foi alias a situação com que me deparei de início: a base de dados tinha dezenas de jobs a realizar a mesma tarefa!

Posto o problema de apenas o campo job (do tipo número) identificar univocamente o job, utilizei o campo what para o identificar. Criei o seguinte script de deploy:

DECLARE
  V_JOB NUMBER;
  V_WHAT VARCHAR2(100) := 'BEGIN MY_PKG.MY_EXEC_PROC; END;';
  V_NEXT_DATE DATE := SYSDATE;
BEGIN
  dbms_output.put_line('Deleting all MY_PKG.MY_EXEC_PROC jobs:');
	FOR jobs_to_delete IN (
    SELECT  job
	    FROM user_jobs 
	    WHERE what like V_WHAT
   ) LOOP
     dbms_output.put_line('...removing MY_PKG.MY_EXEC_PROC job ' || jobs_to_delete.job);
     dbms_job.remove(jobs_to_delete.job);
  END LOOP;
  dbms_output.put_line('Deleted all MY_PKG.MY_EXEC_PROC jobs.');
  commit;
  
  DBMS_JOB.SUBMIT(V_JOB, V_WHAT, V_NEXT_DATE);
  commit;
  dbms_output.put_line('Created MY_PKG.MY_EXEC_PROC job: '||v_job);
END;
/

Reparar no loop para resolver o caso de fazerem deploy do mesmo job.
Este script não resolve outro problema: se numa fase de desenvolvimento alterarmos o what do job não iremos remover os jobs antigos – claro que esta situação tem pouco impacto com algum controlo regular sobre o que existe na base de dados.

Este script poderia ser isolado num procedimento com a assinatura

  DEPLOY_JOB(
		job       OUT BINARY_INTEGER,
		what      IN  VARCHAR2,
		next_date IN  DATE DEFAULT sysdate,
		interval  IN  VARCHAR2 DEFAULT 'null',
		job_name  IN  VARCHAR2);   -- 'job_name' serve apenas para dbms_output

que não é mais do que estender as funcionalidades do DBMS_JOB.SUBMIT.

Oracle 10g: o DBMS_SCHEDULER

Mas eis que a 10g apresenta uma solução muito mais completa: o package DBMS_SCHEDULER.

Eis o novo script de deploy proposto

DECLARE
  V_JOB_NAME VARCHAR2(100) := 'MY_PKG_MY_EXEC_PROC';
BEGIN
  DBMS_SCHEDULER.DROP_JOB( 
    job_name          =>  V_JOB_NAME );
  DBMS_SCHEDULER.CREATE_JOB(
		job_name          =>  V_JOB_NAME,
		job_type          =>  'PLSQL_BLOCK',
		job_action        =>  'BEGIN MY_PKG.MY_EXEC_PROC; END;');
		start_date        =>  SYSDATE );
END;
/

job_name
This attribute specifies the name of the job and uniquely identifies the job.
The name has to be unique in the SQL namespace. For example, a job cannot have the same name as a table in a schema.

If job_name is not specified, an error is generated. If you want to have a name generated by the Scheduler, you can use the GENERATE_JOB_NAME procedure to generate a name and then use the output in the CREATE_JOB procedure. The GENERATE_JOB_NAME procedure call generates a number from a sequence, which is the job name. You can prefix the number with a string. The job name will then be the string with the number from the sequence appended to it.

Além do job ter um identificador descritivo, não é apenas único na lista de jobs mas também em todo o schema.

E com o DBMS_SCHEDULER.GENERATE_JOB_NAME nem precisariamos de nos preocupar com o nome do job, similar ao DBMS_JOBS.
E óptimo disponibilizar a geração de valores para job_name com prefixo, muito útil em aplicações que geram internamente jobs e que teriam de implementar esta geração do nome.

Nota importante: ao contrário do DBMS_JOB.SUBMIT, o DBMS_SCHEDULER.CREATE_JOB faz commit, tentando ser similar a um statement DDL.

Leituras