Processo Assíncrono em PL/SQL

Processo Assíncrono em PL/SQL

Tudo bem ?

Estou tentando implementar um processo assíncrono utilizando EXECUTE IMMEDIATE, mas não cheguei no resultado esperado. Poderia me ajudar ?

Rodolfo

---

Camarada, pelo que conversamos acho que houve um pequeno engano sobre o uso do EXECUTE IMMEDIATE. Ele não cria processos assíncronos e sim executa SQLs dinâmicos ou blocos PL/SQL anônimos. Dê uma olhada na imagem abaixo:

Vamos a um teste utilizando uma versão modificada da versão assíncrona, utilizando o EXECUTE IMMEDIATE:

CREATE TABLE teste
 (
  codigo NUMBER
 ,razao_social VARCHAR2(2000)
 )
 /

Table TESTE criado.

CREATE OR REPLACE PACKAGE jobTeste
 IS
    PROCEDURE insertData;
    PROCEDURE execute;
    PROCEDURE read; 

 END jobTeste;

Package JOBTESTE compilado

CREATE OR REPLACE PUBLIC SYNONYM jobTeste FOR jobTeste
/

SYNONYM JOBTESTE criado.

CREATE OR REPLACE PACKAGE BODY jobTeste
IS
   PROCEDURE insertData
   AS 
   PRAGMA AUTONOMOUS_TRANSACTION;
      v_empresa teste%ROWTYPE;
   BEGIN  
      FOR x IN 1..10
      LOOP
         v_empresa.codigo := x;
         v_empresa.razao_social := 'EMPRESA ' || x;          
         
         INSERT INTO teste VALUES v_empresa;
      
      END LOOP;
      COMMIT;

   END insertData;

   PROCEDURE read
   IS
      x NUMBER := 0;
   BEGIN 
      FOR v_dados IN (SELECT * FROM teste)
      LOOP
         DBMS_OUTPUT.put_line(v_dados.codigo || ' - ' || v_dados.razao_social);
         x := x + 1;

      END LOOP;

      IF x = 0
      THEN
         DBMS_OUTPUT.put_line('Não há dados !');   

      ELSE
         DBMS_OUTPUT.put_line('Número de registros: ' || x);

      END IF;

   EXCEPTION
       WHEN no_data_found THEN
          DBMS_OUTPUT.put_line('Não há dados !');

   END read;

   PROCEDURE execute
   IS
   BEGIN
      DBMS_OUTPUT.put_line('[ Time INSE ] ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));   
  
      insert_data;

      DBMS_OUTPUT.put_line('[ Time READ ] ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));     

      read;

      DBMS_OUTPUT.put_line('[ Time END  ] ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));

   END execute;
  
BEGIN   
   NULL;
END jobTeste;

Package Body JOBTESTE compilado

Vamos executar o nosso código:

BEGIN
   -- Exclui os dados inseridos
   DELETE FROM teste;
   COMMIT;

   JOBTESTE.execute;

END;

[ Time INSE ] 11:22:16
[ Time READ ] 11:22:16
1 - EMPRESA 1
2 - EMPRESA 2
3 - EMPRESA 3
4 - EMPRESA 4
5 - EMPRESA 5
6 - EMPRESA 6
7 - EMPRESA 7
8 - EMPRESA 8
9 - EMPRESA 9
10 - EMPRESA 10
Número de registros: 10
[ Time END ] 11:22:17 

Veja que o processo executou de maneira completamente síncrona. Sendo uma instrução esperando a outra terminar.

Agora, retornando ao assunto principal que é sobre criar um processo assíncrono, eu costumo utilizar JOBS. É um processo bem simples de ser feito.

CREATE OR REPLACE PACKAGE BODY jobTeste
IS
   -- Insert the data
   PROCEDURE insertData
   IS 
      v_empresa teste%ROWTYPE;

   BEGIN  
      -- Wait 1 minute before insert the data
      DBMS_LOCK.sleep(60);

      FOR x IN 1..10   
      LOOP      
         v_empresa.codigo := x;      
         v_empresa.razao_social := 'EMPRESA ' || x;      
   
         INSERT INTO teste VALUES v_empresa;   

      END LOOP;   
      COMMIT;

   END insertData;

   -- Read the data
   PROCEDURE read
   IS
      x NUMBER := 0;
   BEGIN 
      DBMS_OUTPUT.put_line('Lendo os dados da tabela… ' || TO_CHAR(SYSDATE, 'HH24:MI:SS')); 

      FOR v_dados IN (SELECT * FROM teste)   
      LOOP      
         DBMS_OUTPUT.put_line(v_dados.codigo || ' - ' || v_dados.razao_social);      
         x := x + 1;   
      END LOOP;   

      IF x = 0   
      THEN
         DBMS_OUTPUT.put_line('Não há dados !');
      ELSE
         DBMS_OUTPUT.put_line('Número de registros: ' || x);
      END IF;

   EXCEPTION
      WHEN no_data_found THEN
         DBMS_OUTPUT.put_line('Não há dados !');

   END read;

   -- Execute the process using a JOB
   PROCEDURE execute
   AS
      PRAGMA AUTONOMOUS_TRANSACTION;
      v_jobName VARCHAR2(2000) := 'COLJOBTEST';

   BEGIN
      DBMS_SCHEDULER.create_job
         (
          job_name            => v_jobName
         ,job_type            => 'STORED_PROCEDURE'
         ,job_action          => 'JOBTESTE.insertData'
         ,start_date          => SYSTIMESTAMP
         ,enabled             => TRUE
         ,auto_drop           => TRUE -- O Job se auto destruirá
         ,comments            => 'Job Creation Test'
         );

   END execute;

BEGIN   
    NULL;
END jobTeste;

Package Body JOBTESTE compilado

Agora vamos executar o nosso código:

BEGIN
   -- Exclui os dados inseridos
   DELETE FROM teste;
   COMMIT;
   JOBTESTE.execute;
   JOBTESTE.read;

END;

Procedimento PL/SQL concluído com sucesso.

Lendo os dados da tabela… 12:17:21
Não há dados !

Veja que o processo executou, mas não há dados na tabela !

Isso se deve ao fato de eu ter colocado um DBMS_LOCK de 1 minuto no processo, ou seja, enquanto esse tempo não passar, o JOB não terminará o processo e não fará a inserção dos dados.

Passado 1 minuto, vamos executar novamente o READ:

BEGIN
    JOBTESTE.read;

END;

Procedimento PL/SQL concluído com sucesso.

Lendo os dados da tabela… 12:32:29
 1 - EMPRESA 1
 2 - EMPRESA 2
 3 - EMPRESA 3
 4 - EMPRESA 4
 5 - EMPRESA 5
 6 - EMPRESA 6
 7 - EMPRESA 7
 8 - EMPRESA 8
 9 - EMPRESA 9
 10 - EMPRESA 10
 Número de registros: 10

Vamos observar como nosso JOB executou:

SELECT job_name job,status,error#
      ,req_start_date,run_duration 
FROM   dba_scheduler_job_run_details
WHERE  job_name LIKE 'COLJOBTEST'
/

JOB        STATUS          ERROR#  REQ_START_DATE   DURATION       
---------- --------------- ------ ----------------- ------------------------------------- 
COLJOBTEST SUCCEEDED            0 11/05/19 12:30:43 ,744000000 -03:00 +00 00:01:01.000000

Veja o tempo de execução (RUN_DURATION). Foram os 60 segundos do sleep e 1 segundo de execução.

Caso queira ver uma implementação mais robusta de processo assíncrono, veja o artigo PL/SQL – Passando uma collection como parâmetro de um JOB usando ANYDATA.

Espero ter ajudado !

Abraço