PL/SQL – Passando uma collection como parâmetro de um JOB usando ANYDATA

Olá pessoal !

Neste exemplo irei demonstrar como se passa uma collection como parâmetro de um job. Para isso, iremos utilizar o nosso velho amigo ANYDATA, que foi alvo de outros dois artigos escritos por mim e que podem ser encontrados aqui no GPO.

Primeiro iremos criar uma tabela para armazenar os dados processados:

CREATE TABLE tJobTeste
(
 DESCRIPTION VARCHAR2(2000)
)
/

CREATE PUBLIC SYNONYM tJobTeste FOR tJobTeste
/

Agora criaremos os types que armazenarão a nossa collection:

CREATE OR REPLACE TYPE rowPipe AS OBJECT (description VARCHAR2(2000))
/

CREATE OR REPLACE TYPE tabPipe AS TABLE OF rowPipe
/

Agora vamos criar a SPEC da Package:

CREATE OR REPLACE PACKAGE jobTest AS

   -- Cria o Job

   PROCEDURE createJob
      (
       p_pipe tabPipe
      );

   -- Processa os Dados

   PROCEDURE insertData
      (
       p_pipe tabPipe
      );

END jobTest;

E finalmente, a BODY !

CREATE OR REPLACE PACKAGE BODY jobTest AS
   -- 
   -- Cria o Job e trata a Collection
   --

   PROCEDURE createJob
      (
       p_pipe tabPipe
      )
   AS PRAGMA AUTONOMOUS_TRANSACTION;
      vJobName VARCHAR2(2000) := 'COLJOBTEST';

   BEGIN
      -- Cria o JOB

      DBMS_SCHEDULER.create_job
         (
          job_name            => vJobName
         ,job_type            => 'STORED_PROCEDURE'
         ,job_action          => 'JOBTEST.insertData'
         ,number_of_arguments => 1
         ,start_date          => SYSTIMESTAMP
         ,job_class           => ''
         ,enabled             => FALSE
         ,auto_drop           => TRUE -- O Job se auto destruirá
         ,comments            => 'Job Creation Test'
         );

      -- Seta o parâmetro como ANYDATA

      DBMS_SCHEDULER.set_job_anydata_value
         (
          job_name          => vJobName
         ,argument_position => 1
         ,argument_value    => SYS.ANYDATA.ConvertCollection(p_pipe)
         );  

      -- Habilita o Job   

      DBMS_SCHEDULER.enable 
         (
          name => vJobName
         );         

   END createJob;

   --
   -- Processa o Collection
   --
   PROCEDURE insertData
      (
       p_pipe tabPipe
      )
   IS
   BEGIN
      FOR x IN p_pipe.FIRST..p_pipe.LAST
      LOOP
         BEGIN
            INSERT INTO tJobTeste VALUES (p_pipe(x).description);

         EXCEPTION
            WHEN OTHERS THEN
               INSERT INTO tJobTeste VALUES (DBMS_UTILITY.format_error_stack);

         END;

         COMMIT;

      END LOOP;

   END insertData;

BEGIN
    NULL;

END jobTest;

O truque para passar a collection como parâmetro, é criar o JOB com o parâmetro ENABLE como FALSE, e utilizar DBMS_SCHEDULER.set_job_anydata_value para setá-lo. Depois, basta utilizar o DBMS_SCHEDULER.enable para que ele comece a execução de imediato.

Agora vamos testar a nossa PACKAGE:

DECLARE
  tPipe tabPipe := tabPipe();
  x NUMBER      := 0;

BEGIN

   -- Preenche a collection

   FOR x IN 1..5 
   LOOP
      tPipe.EXTEND;
      tPipe(x) := rowPipe('TESTE -' || x);
      
   END LOOP;

   -- Executa a rotina

   jobTest.createJob(tPipe);

END;


PL/SQL procedure successfully completed.

Vamos verificar o status de nosso JOB:

SELECT * 
FROM  dba_scheduler_job_run_details
Where job_name LIKE 'COLJOB%'
ORDER BY actual_start_date DESC
/

O JOB executou com sucesso ! Agora vamos ver o resultado do processamento:

SELECT * 
FROM tJobTeste
/

O resultado foi o esperado, e os dados foram processados e inseridos na tabela ! 🙂

O ANYDATA nos dá uma incrível flexibilidade para transportar coleções, como observaram nos últimos artigos escritos, é possível criar soluções inteligentes com boa performance se utilizando desse expediente.

Esse é o último artigo da série ANYDATA. Caso tenham alguma dúvida sobre sua utilização ou quais contextos utilizar, é só entrar em contato !

Um grande abraço