Trabalhando com Exceções em operações em Bulk
Antes de começarmos a criar nosso cenário e programar, é importante entendermos como a operação em bulk binding – que é o primeiro método que iremos abordar – funciona e no que ela difere de uma operação comum.
Apesar da linguagem PL/SQL estar integrada ao banco de dados, o Oracle possui “Engines” distintas para processamento do código procedural e SQL. O procedimento ocorre da seguinte forma: O bloco é submetido ao processador PL/SQL, que identifica o que é linguagem procedural – e submete ao executor procedural – e o que são instruções SQL, que são submetidas ao processador SQL, conforme descrição na figura abaixo:
A cada instrução SQL contida no bloco PL/SQL, ocorre uma “troca de engines”. Normalmente esta troca não causa um impacto na execução do bloco, porém ao trabalhar com grandes quantidades de dados, a diminuição destas trocas pode melhorar muito o tempo de execução.
Criação da tabela base com informações:
CREATE TABLE GPO_ITEM_NF AS
(SELECT LEVEL ID_NF,
ROUND(DBMS_RANDOM.VALUE(1, 99999999999999)) ID_PRODUTO,
DBMS_RANDOM.STRING('A', 20) DS_PRODUTO,
ROUND(DBMS_RANDOM.VALUE(1, 9999),2) VL_UNITARIO,
ROUND(DBMS_RANDOM.VALUE(1, 10)) QT_PEDIDO
FROM DUAL CONNECT BY LEVEL < 1000000);Atualizar estatísticas da tabela:
EXEC DBMS_STATS.GATHER_TABLE_STATS('<YOUR OWNER>','GPO_ITEM_NF');Criação de uma tabela espelho para inserção dos dados:
CREATE TABLE GPO_ITEM_NF_2
(
ID_NF NUMBER(15),
ID_PRODUTO NUMBER(15),
DS_PRODUTO VARCHAR2(30),
VL_UNITARIO NUMBER(10,2),
QT_PEDIDO NUMBER(10)
);Em seguida, no SQL*Plus, vamos executar o seguinte comando para exibir o tempo de processamento:SET TIMING ON
O Bloco PL/SQL abaixo irá realizar a inserção dos dados na tabela espelho através do método padrão:
DECLARE
CURSOR c_ITEMNF IS
SELECT *
FROM GPO_ITEM_NF;
BEGIN
FOR REC_NF IN c_ITEMNF LOOP
INSERT INTO GPO_ITEM_NF_2 (ID_NF, ID_PRODUTO, DS_PRODUTO,
VL_UNITARIO, QT_PEDIDO)
VALUES (REC_NF.ID_NF, REC_NF.ID_PRODUTO, REC_NF.DS_PRODUTO,
REC_NF.VL_UNITARIO, REC_NF.QT_PEDIDO);
END LOOP;
COMMIT;
END;
/
Vamos executar agora um bloco PL/SQL que irá efetuar a mesma operação acima, porém utilizando bulk binding:
DECLARE
CURSOR c_ITEMNF IS
SELECT *
FROM GPO_ITEM_NF;
TYPE ARRAY_NF IS TABLE OF
c_ITEMNF%ROWTYPE
INDEX BY PLS_INTEGER;
AR_NF ARRAY_NF;
BEGIN
OPEN c_ITEMNF;
LOOP
FETCH c_ITEMNF BULK COLLECT INTO AR_NF LIMIT 10000;
FORALL I IN AR_NF.FIRST .. AR_NF.LAST
INSERT INTO GPO_ITEM_NF_2
VALUES AR_NF(I);
EXIT WHEN c_ITEMNF%NOTFOUND;
END LOOP;
CLOSE c_ITEMNF;
COMMIT;
END;
/Neste bloco, declaramos um associative array que será responsável por armazenar os dados que o cursor retornar. Dentro da seção de execução, abrimos o cursor e efetuamos um loop para realizar o fetch dos registros em bulk. Utilizamos ainda a cláusula LIMIT para restringir o número de registros que iremos manter em memória. Este é um detalhe que devemos nos atentar ao realizar este tipo de operação – O associative array se encontra na PGA (Program Global Area) e caso a quantidade de memória disponível seja insuficiente para armazenar todos os registros, o erro “ORA-04030: out of process memory when trying to allocate < N > bytes” ocorrerá.
Perceba a diferença no tempo de execução e na quantidade de execuções da instrução INSERT contida no bloco PL/SQL. Como tivemos menos troca de engines, ganhamos muito tempo na operação. Há também uma grande diferença no número de execuções – devido a utilização da cláusula FORALL – que indica que todo o conteúdo do array (10000) será atrelado a uma única instrução SQL.
Como podemos observar através deste simples exemplo, operações em bulk podem fazer a diferença ao se trabalhar com grandes quantidades de dados. Um dos problemas que encontramos ao lidar com instruções em Bulk é ao lidar com Exceptions. Caso ocorra um erro durante a DML, toda a operação será perdida. Abaixo, iremos analisar uma forma de se trabalhar com exceptions em operações de bulk binding sem que haja parada do processamento:
SQL%BULK_EXCEPTION / SAVE EXCEPTIONS
Para testar este método, vamos efetuar as seguintes alterações em nossas tabelas:
– Limpar a tabela espelho para receber uma nova carga de dados e adicionar uma constraint PRIMARY KEY:
TRUNCATE TABLE GPO_ITEM_NF_2; ALTER TABLE GPO_ITEM_NF_2 ADD CONSTRAINT GPO_ITEM_NF_2_PK PRIMARY KEY (ID_NF, ID_PRODUTO) USING INDEX;– Em seguida, vamos executar o bloco PL/SQL abaixo para alterar alguns registros na tabela original e provocar erros durante a inserção:
DECLARE
v_ID_NF NUMBER(15);
v_ID_PRODUTO NUMBER(15);
BEGIN
SELECT ID_NF, ID_PRODUTO
INTO v_ID_NF, v_ID_PRODUTO
FROM GPO_ITEM_NF
WHERE ID_NF = 5000;
UPDATE GPO_ITEM_NF
SET ID_NF = v_ID_NF,
ID_PRODUTO = v_ID_PRODUTO
WHERE ID_NF = 5001;
UPDATE GPO_ITEM_NF
SET ID_PRODUTO = NULL
WHERE ID_NF = 12000;
COMMIT;
END;
/Com as alterações no cenário concluídas, vamos analisar o seguinte código, que irá efetuar os INSERTs via bulk tratando as exceções:
SET SERVEROUTPUT ON
DECLARE
CURSOR c_ITEMNF IS
SELECT *
FROM GPO_ITEM_NF;
TYPE ARRAY_NF IS TABLE OF
c_ITEMNF%ROWTYPE
INDEX BY PLS_INTEGER;
AR_NF ARRAY_NF;
GPO_BULK_EXC EXCEPTION;
PRAGMA EXCEPTION_INIT(GPO_BULK_EXC, -24381);
v_BULK_ERRCOUNT NUMBER(2);
v_ARRAY_POS NUMBER(10);
BEGIN
OPEN c_ITEMNF;
LOOP
FETCH c_ITEMNF BULK COLLECT INTO AR_NF LIMIT 10000;
BEGIN
FORALL I IN AR_NF.FIRST .. AR_NF.LAST
SAVE EXCEPTIONS
INSERT INTO GPO_ITEM_NF_2
VALUES AR_NF(I);
EXCEPTION
WHEN GPO_BULK_EXC THEN
v_BULK_ERRCOUNT := SQL%BULK_EXCEPTIONS.COUNT;
FOR J IN 1.. v_BULK_ERRCOUNT LOOP
v_ARRAY_POS := SQL%BULK_EXCEPTIONS(J).ERROR_INDEX;
DBMS_OUTPUT.PUT_LINE('ERRO PROCESSAMENTO - ID_NF: '|| AR_NF(v_ARRAY_POS).ID_NF || ' MENSAGEM ERRO: ' || SQLERRM( -SQL%BULK_EXCEPTIONS(J).ERROR_CODE));
END LOOP;
END;
EXIT WHEN c_ITEMNF%NOTFOUND;
END LOOP;
CLOSE c_ITEMNF;
COMMIT;
END;
/Na seção declarativa, criamos uma exception GPO_BULK_EXC e a atrelamos ao erro “ORA-24381: error(s) in array DML”. Na seção de execução, utilizamos a cláusula “SAVE EXCEPTIONS” (disponível a partir da versão 9i) para indicar que as exceções ocorridas durante o FORALL serão capturadas e não deverá haver parada na execução da DML. Na seção de tratamento de erros, utilizamos os atributos do cursor (SQL%BULK_EXCEPTIONS) para manipular as exceções:
ERROR_INDEX: Contém a posição do registro no array que ocasionou o erro.
ERROR_CODE: Contém o código do erro (equivalente a função SQLCODE).
Ao executar o bloco, apesar dos erros ocorridos, não houve parada na execução devido a infringirmos a constraint:
Realizando um COUNT() na tabela de destino, podemos confirmar que os demais registros foram inseridos com sucesso:
Apesar de efetivo, este método adiciona uma certa complexidade a programação. Para operações em Bulk que não possuem necessidade de uma lógica complexa, podemos utilizar um método disponível a partir da versão 10g Release 2, que possui implementação mais simples, porém com algumas limitações:
A funcionalidade não é ativada nas seguintes situações:
- Quando Deferrable Constraints são violadas.
- Em instruções INSERT que utilizam Direct-path (como na utilização da hint APPEND) que infringem uma constraint unique ou violam algum index.
- Em Instruções UPDATE que infringem uma constraint unique ou violam algum index.
DBMS_ERRLOG.CREATE_ERROR_LOG
Esta procedure cria uma tabela de log atrelada a tabela alvo. Quando uma instrução DML na tabela alvo causar uma exception, a instrução DML continuará a ser processada ao invés de seguir o fluxo padrão (abort e rollback) e o erro será automaticamente salvo na tabela de log.
Para utilizarmos esta função em nosso código, precisamos efetuar 2 passos: Primeiro, é necessário chamar a procedure passando como parâmetro o nome da tabela que desejamos salvar as exceções:BEGIN DBMS_ERRLOG.CREATE_ERROR_LOG (DML_TABLE_NAME => ‘GPO_ITEM_NF_2’); END; /
Uma tabela será criada com nome e estrutura baseada na tabela de origem, acrescida das seguintes alterações: A nova tabela conterá o prefixo “ERR$_” no nome e irá conter as seguintes colunas:
ORA_ERR_NUMBER$ = Código do erro Oracle.
ORA_ERR_MESG$ = Mensagem de erro.
ORA_ERR_ROWID$ = Rowid do registro alvo da instrução(Apenas para
Updates e Deletes).
ORA_ERR_OPTYP$ = Tipo de DML: Insert (I), Update (U), Delete (D)
ORA_ERR_TAG$ = Tag que podemos adicionar opcionalmente a cláusula
de log, identificando a instrução DML.
O segundo passo consiste em adicionar a seguinte cláusula a instrução DML:LOG ERRORS INTO < Nome_tabela > (‘< Tag_DML >’) REJECT LIMIT < INTEGER|UNLIMITED >
Onde:
< Nome_tabela > = Nome da tabela criada pela procedure.
< Tag_DML > = Tag de identificação da instrução.
< INTEGER|UNLIMITED > = Indica a quantidade máxima de erros para a operação.
Caso este limite seja atingido, a instrução será abortada.
Para uma operação de INSERT como a acima, podemos substituir todo aquele código apenas pela instrução abaixo:
INSERT INTO GPO_ITEM_NF_2 SELECT * FROM GPO_ITEM_NF LOG ERRORS
INTO ERR$_GPO_ITEM_NF_2 ('GPO_INSERT') REJECT LIMIT UNLIMITED;
Apesar dos erros, a instrução completou com sucesso. Para acelerar estas operações (visto que não é permitido o uso da hint append), podemos utilizar a hint parallel, que continua funcionando normalmente.
Como podemos observar, o Oracle nos fornece uma flexibilidade muito boa para trabalhar com exceções durante operações com grandes quantidades de dados. Espero que o artigo seja útil no entendimento e escolha do melhor método.
Até o próximo artigo!
Referências
- http://www.oracle-base.com/articles/9i/bulk-binds-and-record-processing-9i.php
- http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html
- http://www.oracle-base.com/articles/10g/dml-error-logging-10gr2.php
