Pular para o conteúdo

Capturando Erros em DML’s

Capturando Erros em DML’s

É de extrema necessidade se precaver dos erros que podem ocorrer em cargas de dados, transformando-os e/ou sanitizando-os para uma melhor qualidade dos registros. Mas ora ou outra surgem linhas que trazem alguma informação incompatível com o tipo de dados de alguma coluna da tabela. É aí que está o problema.

Na verdade é prática comum de mercado criar tabelas acessórias para aglutinar estes possíveis erros para uma posterior auditoria e tratativa. Mas grande parte das vezes vejo que o processo é feito de forma manual, através de Exception’s no código PL/SQL. Não é errado, contudo deixamos de utilizar uma ótima feature do Oracle, o qual automatiza o processo dentro do próprio comando DML.

As cláusulas LOG ERRORS, REJECT e LIMIT auxilia no preenchimento dos erros ocasionalmente ocorridos na tabela acessória criada pela procedure DBMS_ERRLOG.CREATE_ERROR_LOG. Ao utilizar estas opções o Oracle irá fazer todo o trabalho sujo.

Vamos para a parte prática para entender como este processo funciona:

  • Inicialmente criaremos a tabela TEMP_TESTE_1 para receber uma posterior carga de dados:
CREATE TABLE temp_teste_1
  (
    ID     NUMBER,
    NOME VARCHAR2(10),
    CLASSE NUMBER
  );
  • Com a utilização da procedure DBMS_ERRLOG.CREATE_ERROR_LOG cria-se a tabela, a qual será gravados os log’s.
EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('temp_teste_1', 'temp_teste_1_log');

Em suma, a procedure DBMS_ERRLOG.CREATE_ERROR_LOG faz uma cópia da tabela principal, adicionando as colunas ORA_ERR_NUMBER$, ORA_ERR_MESG$, ORA_ERR_ROWID$, ORA_ERR_OPTYP$ e ORA_ERR_TAG$. Outro ponto interessante é que todas as demais colunas pertencente a tabela principal não são criadas considerando o mesmo tipo de dados, mas sim como VARCHAR2(4000), tendo como objetivo desfazer quaisquer eventuais incompatibilidade de dados.

Outro adendo é que a procedure DBMS_ERRLOG.CREATE_ERROR_LOG apenas automatiza o processo da criação da tabela. Contudo, pode-se criá-la manualmente através do CREATE TABLE, respeitando apenas a obrigatoriedade da existência dos campos de final $.

Capturando Erros em DML
  • Agora se cria a tabela que conterá os dados que serão utilizados para a carga com 1000 registros. Com intuito de promover erro intencional, 200 linhas possuem 11 caracteres no campo NOME, gerando incompatibilidade de tamanho de campo com a respectiva coluna da tabela TEMP_TESTE_1, a qual tem tamanho máximo de 10 caracteres.
CREATE TABLE temp_teste_carga AS
SELECT ROUND(dbms_random.value(1,10)) ID,
  (
    CASE MOD(ROWNUM,5)
      WHEN 0
      THEN CAST (dbms_random.STRING('U',11) AS VARCHAR2(11))
      ELSE CAST (dbms_random.STRING('U',10) AS VARCHAR2(10))
    END) NOME,
  MOD(ROWNUM,5) CLASSE
FROM DUAL
  CONNECT BY LEVEL <= 1000;

SELECT count(*)
FROM temp_teste_carga;
5aV2gVywf Q53xD0iWIDpMusMq8SOJ1 d6GT u82bSTXk3C5h8yzoKnpihx8 GISkdsLXXjct5wh11zSqM iBWoMpTpPylH9c3Gd7rhv6WXfz

Em decorrência de existirem 200 linhas com tamanho incompatível para o campo NOME, será emitido o INSERT abaixo. Logo em seguida percebemos que o erro ORA-12899 é emitido e é feito ROLLBACK para a transação como um todo, ou seja, nenhuma linha é gravada na tabela TEMP_TESTE_1.

INSERT INTO TEMP_TESTE_1
SELECT *
FROM TEMP_TESTE_CARGA;
QemSXOMo04HZ 4cKyGMxTX5znP O3o9SmK5mBV3mUo8DwchML

Agora se utilizando das instruções LOG ERRORS, REJECT e LIMIT será emitido um novo INSERT. Nota-se que foram inseridas 800 linhas das 1000 originais. Os demais 200 registros com tamanho incompatível não provoca ROLLBACK da instrução como um todo, sendo apenas registrados na tabela TEMP_TESTE_1_LOG, conforme podemos ver nas evidências abaixo:

INSERT INTO TEMP_TESTE_1
SELECT *
FROM TEMP_TESTE_CARGA
LOG ERRORS INTO temp_teste_1_log REJECT LIMIT UNLIMITED;
zPvibFgmmcCSlxExRn 59iZEz6z2EhdkkrJ21ZUUZ7jl05aoJ8jKnJIoxulIe 7COZ3VvIyWiT6LmXj iKOErNwec9vuh6ES9uzFG76YnJg4SCHPEOMlmzGbzAwsYkyBW2QORp5VzC2f cOqSA
SELECT COUNT(*) FROM temp_teste_1_log;
X040qo4cf45QNwsFbXiIncQ0JxiFHizrMKVfKvTV0K9g5 7HNSZ2USL1a2REbYJy kA7BYf9l HesZW0t7Qde VK7TZgbok9rD8HzREWvm1k3qKVeceSz4s IWdv2a3Aa89vtyV4YXHq8y85Mg

Os logs de erros são gravados para cada uma das linhas na tabela TEMP_TESTE_1_LOG, nos dando total rastreabilidade para auditoria e tratativas futuras.

SELECT ORA_ERR_NUMBER$,
  ORA_ERR_MESG$,
  ORA_ERR_OPTYP$,
  ID,
  NOME,
  CLASSE
FROM temp_teste_1_log;
b3a2Vll7jGWlRaQPXZIc5CFQCdU8utV3usdmqBEgmBZntFrWYNbB8L5pZHgYKp6qM TB1TP17UrSV4SCqAw3iOatmMWn61eadJpIrmMD4nnab07Oici5qN7z0Dbk3FlnyB jr2MikMN9VdgvFA

Analisando individualmente cada linhas vê-se que é registrado:

  • Código do erro;
  • A mensagem descritiva do erro;
  • O tipo do processo, sendo: Insert, Update, Delete;
  • Os dados das linhas da tabela principal coluna a coluna;
99jlTUvzYrboxZuiAhhJTiNNeVjV9yrMu chmkrQ9H4HuAHI6O9sFBxu6Ni27hMc70na r4xOGk09kxGqQVbq7wNkr7Ikd0f8btmBYOwwOzQc4LUT NVur9bqb4JgLHrPJ6EkaiIbnkyw9rdaw

Outros pontos relevantes são:

  • A instrução UNLIMITED pode ser substituída por um número, onde o mesmo controlará a quantidade de erros que pode ser emitido antes de um eventual ROLLBACK de toda a instrução;
  • Ainda que o INSERT seja através de Direct Path a gravação na tabela de log será em Conventional Path. Contudo, isto não deve ser um problema para performance como um todo, haja vista que deverá ser logado apenas erros ocasionais. Se existirem números parecidos de inserts com sucesso e com erro, pare tudo o que tiver fazendo e volte para o passo de transformação das informações.
  • A tabela de log é manipulada através de AUTONOMOUS_TRANSACTION, ou seja, possíveis ROLLBACK na instrução principal não irá influenciará e preservará os registros da tabela de log.
  • LONGs, LOBs e tipo de dados Objeto não deverão ser criados na tabela de log;

Referências

Jefferson de Almeida Costa

Jefferson de Almeida Costa

Jefferson de Almeida Costa, formado em Ciência da Computação, com pós-graduação em MBA em Engenharia de Software Orientada a Serviços – SOA pela FIAP (Faculdade de Informática e Administração Paulista) e pós-graduando em MBA em Gestão de Projetos pela USP/Esalq. É desenvolvedor PL/SQL, com foco em Tuning/Performance e grandes volumes de dados, com certificações Oracle Certified Associate (OCA) e Oracle Certified Professional (OCP) em PL/SQL e Oracle Certified Expert (OCE) em SQL e SQL Tuning, bem como Agile Scrum Master (ASM) e ITIL Foundation (ITFL) pela Exin. Site: https://www.jeffersonacosta.com/

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

plugins premium WordPress