GPO ( Grupo de Profissionais Oracle )
A maior comunidade Oracle do Brasil !

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.

Para efetuarmos uma comparação, vamos montar nosso cenário e analisar 2 blocos PL/SQL com a mesma finalidade:

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

Share

You may also like...

Deixe um comentário

O seu endereço de e-mail não será publicado.