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 $.
- 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;
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;
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;
SELECT COUNT(*) FROM temp_teste_1_log;
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;
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;
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