A PRAGMA AUTONOMOUS_TRANSACTION é uma funcionalidade avançada do PL/SQL no Oracle que permite a execução de transações independentes dentro de uma função ou procedimento. Essa funcionalidade é particularmente útil para registrar logs ou realizar operações que precisam ser confirmadas independentemente do sucesso ou falha das transações principais. Neste artigo, vamos explorar como utilizá-la através de um exemplo prático.

Exemplo de Uso da PRAGMA AUTONOMOUS_TRANSACTION

Vamos criar uma função que registra cada passo importante de um processo, como a inserção de dados em uma tabela, na tabela LOGS_PASSO_A_PASSO. Isso garantirá que essas informações sejam preservadas, mesmo que a transação principal falhe ou seja realizada um rollback.

1. Criação da Tabela de Logs e da Sequência

Primeiro, vamos criar a tabela LOGS_PASSO_A_PASSO, que será usada para registrar as informações de cada passo, e a sequência LOGS_PASSO_A_PASSO_SEQ, que será utilizada para gerar os identificadores únicos de cada log.

sqlplus hr/hr@pdb1

SQL>  show user
USER is "HR"

SQL> 
create table LOGS_PASSO_A_PASSO
(
    id_log number primary key,        -- Identificador único para cada registro de log
    id_processo number,               -- Identificador do processo ao qual o log pertence
    passo varchar2(100),              -- Descrição do passo executado
    descricao varchar2(255),          -- Detalhes adicionais sobre o passo
    data_hora timestamp,              -- Data e hora em que o passo foi registrado
    status varchar2(50)               -- Status do passo (ex: SUCESSO, FALHA, EM ANDAMENTO)
);

Table created.

SQL>

create table OPERACOES_TESTE
(
    coluna1 varchar2(50) not null,
    coluna2 varchar2(50)
);

Table created.

SQL> 
  create sequence LOGS_PASSO_A_PASSO_SEQ
  start with 1
  increment by 1
  cache 20    
  nocycle;
  
Sequence created.

2. Função com PRAGMA AUTONOMOUS_TRANSACTION

Agora, vamos criar uma procedure que registra cada etapa do processo na tabela LOGS_PASSO_A_PASSO. A rotina usará a PRAGMA AUTONOMOUS_TRANSACTION para garantir que os registros de log sejam preservados, independentemente de outras operações que possam falhar ou se a transação principal for revertida com um rollback.

SQL> 

create or replace procedure registrar_passo(p_id_processo number, p_passo varchar2, p_descricao varchar2, p_status varchar2)
is
  pragma autonomous_transaction;
begin
   insert into LOGS_PASSO_A_PASSO (id_log, id_processo, passo, descricao, data_hora, status)
   values (LOGS_PASSO_A_PASSO_SEQ.nextval, p_id_processo, p_passo, p_descricao, systimestamp, p_status);
   commit;
end registrar_passo;
/

Procedure created.

Cada vez que a proc é chamada, um novo registro é inserido na tabela LOGS_PASSO_A_PASSO. O uso de pragma autonomous_transaction permite que a inserção de log seja feita de maneira independente de outras transações. Isso significa que, mesmo que a transação principal falhe ou seja feita uma reversão (rollback), os logs serão preservados.

3. Teste da Função com PRAGMA AUTONOMOUS_TRANSACTION

Agora, vamos realizar um teste prático utilizando a função registrar_passo com um cursor que simula diferentes cenários:

Cenário: Registro de Passos com Sucessos e Falhas

Vamos registrar os passos de um processo de inserção de dados usando um cursor que gera 10 registros. Alguns desses registros serão inseridos com sucesso, enquanto outros gerarão erros

SQL> 

declare
  cursor c_registros is
    select level as id_processo,
           case when mod(level, 2) = 0 then 'Valor válido' else null end as coluna1,  -- Sucesso para pares, erro para ímpares
           dbms_random.string('U', 10) as coluna2  
    from dual
    connect by level <= 10;
begin
  for rec in c_registros loop
    begin
      -- Registrando o início do processo
      registrar_passo(rec.id_processo, 'PASSO ANTES INSERT', 'Iniciando processo de inserção.', 'SUCESSO');
      
      insert into OPERACOES_TESTE (coluna1, coluna2)
      values (rec.coluna1, rec.coluna2);

      -- Registro de sucesso
      registrar_passo(rec.id_processo, 'PASSO APOS  INSERT', 'Inserção realizada com sucesso.', 'SUCESSO');
      
    exception
      when others then
        -- Registro de falha
		    rollback;
        registrar_passo(rec.id_processo, 'PASSO ERRO  INSERT', 'Erro ao inserir dados.', 'FALHA');
    end;
  end loop;
end;
/

PL/SQL procedure successfully completed.

Registro de Passos: Usa a procedure registrar_passo para registrar tanto o início quanto o resultado de cada tentativa de inserção, seja ela bem-sucedida ou não.

Cursor c_registros: Gera 10 registros, onde os registros pares têm valores válidos e os ímpares contêm valores nulos em coluna1, resultando em erro.

Valor Aleatório em coluna2: A função dbms_random.string('U', 10) gera uma string aleatória de 10 caracteres para coluna2, tornando o teste mais dinâmico.

Loop pelo Cursor: Tenta inserir os valores na tabela OPERACOES_TESTE, registrando cada tentativa na tabela LOGS_PASSO_A_PASSO.

4. Exibindo os Resultados

Após a execução do código, podemos consultar a tabela LOGS_PASSO_A_PASSO para verificar os resultados:

SQL> 

SET LINES       188
SET PAGES       300 

COLUMN id_log FORMAT 99999
COLUMN id_processo FORMAT 99999
COLUMN passo FORMAT A20
COLUMN descricao FORMAT A40
COLUMN data_hora FORMAT A30
COLUMN status FORMAT A10

select * from LOGS_PASSO_A_PASSO order by id_log;

1. ID_LOG:
   • Identificador único para cada registro de log.
   • Os IDs 81 a 100 indicam os registros de log gerados durante as inserções nos processos de teste. Eles são sequenciais, indicando a ordem em que os eventos ocorreram.
2. ID_PROCESSO:
   • Representa o identificador do processo ao qual o log pertence.
   • Os IDs de processo variam de 1 a 10, correspondendo a cada tentativa de inserção de dados. Cada processo de inserção está associado a dois registros de log: um antes da tentativa de inserção e outro após a tentativa (independentemente de sucesso ou falha).
3. PASSO:
   • Descreve a etapa específica do processo.
   • O campo PASSO indica se a ação registrada ocorreu antes (PASSO ANTES INSERT) ou depois da tentativa de inserção (PASSO APOS INSERT) ou se ocorreu um erro durante a tentativa (PASSO ERRO INSERT).
4. DESCRICAO:
   • Fornece uma explicação detalhada do que aconteceu no passo específico.
   • As descrições incluem mensagens como “Iniciando processo de inserção.”, “Erro ao inserir dados.”, e “Inserção realizada com sucesso.”, que indicam exatamente o que aconteceu durante cada etapa do processo.
5. DATA_HORA:
   • Registra a data e hora exatas em que o evento foi registrado no banco de dados.
   • Os timestamps na coluna DATA_HORA mostram que todos os eventos foram registrados quase simultaneamente (dentro do mesmo segundo), refletindo a rápida execução do script de teste.
6. STATUS:
   • Indica o resultado da etapa.
   • O status SUCESSO indica que a ação foi realizada conforme esperado (como iniciar o processo ou concluir uma inserção), enquanto o status FALHA indica que houve um erro ao tentar inserir dados devido à restrição NOT NULL na coluna coluna1.

5. Explicação das Transações Separadas

A importância da PRAGMA AUTONOMOUS_TRANSACTION neste contexto é que cada operação de log é tratada como uma transação separada e independente da transação principal. Isso significa que, mesmo se a transação principal falhar ou for revertida com um rollback, os registros de log ainda serão confirmados no banco de dados. Isso é essencial para manter um histórico preciso das operações, mesmo em situações de falha.

6. Exclusão dos Objetos Criados

Após concluir seus testes, é importante limpar o ambiente excluindo os objetos criados. Aqui estão os comandos para remover as tabelas, a sequência e a procedure utilizadas:

sqlplus hr/hr@pdb1

SQL>  show user
USER is "HR"

SQL> drop table LOGS_PASSO_A_PASSO;

Table dropped.

SQL> drop sequence LOGS_PASSO_A_PASSO_SEQ;

Sequence dropped.

SQL> drop table OPERACOES_TESTE;

Table dropped.

SQL> drop procedure registrar_passo;

Procedure dropped.

A PRAGMA AUTONOMOUS_TRANSACTION no Oracle PL/SQL é uma poderosa ferramenta para gerenciar transações independentes dentro de procedimentos e funções, permitindo que certas operações sejam confirmadas ou revertidas de forma isolada, independentemente do sucesso ou falha das transações principais. Neste artigo, exploramos essa funcionalidade por meio de um exemplo prático que envolve o registro de logs detalhados durante o processo de inserção de dados

🪖🦧 #20240828 #DuplicateDidi #TodosCagaram #GuinaNãoTinhaDó #CaceteDeAgulha 🦧🪖