Nenhum resultado encontrado

Sua pesquisa não corresponde a nenhum resultado.

Sugerimos que você tente o seguinte para ajudar a encontrar o que procura:

  • Verifique a ortografia da sua pesquisa por palavra-chave.
  • Use sinônimos para a palavra-chave digitada; por exemplo, tente “aplicativo” em vez de “software.”
  • Tente uma das pesquisas populares mostradas abaixo.
  • Inicie uma nova pesquisa.
Perguntas Frequentes

Entendendo e Utilizando os atributos SQL%BULK_ROWCOUNT e SQL%BULK_EXCEPTIONS

Por Tércio Costa,
Postado em Fevereiro 2016

Revisado por Marcelo Pivovar - Solution Architect

Trabalhar com cursores é algo extremamente comum em blocos PL/SQL entre os desenvolvedores. E todos conhecemos muito bem alguns atributos dos cursores, explícitos como implícitos, como o %ROWCOUNT que nos retorna a quantidade de linhas retornada por um SELECT ou a quantidade de linhas modificadas por outra operação DML.

E como todo bom desenvolvedor, utilizamos operações em BULK para melhorar a performance das instruções SQL e usar o mínimo possível do famoso Context Switch. Mas ao utilizar o %ROWCOUNT em operações com bulk o resultado retornado talvez não seja o esperando, pois ele irá retornar a quantidade total de linhas que foram retornadas ou modificadas e não a quantidade por cada uma das operações realizadas. Para isto, as operações em bulk possui alguns atributos a mais, como o %BULK_ROWCOUNT que exibe a quantidade por cada uma das operações realizadas em um bulk. Vejamos como fazer uso desse atributo. Para este exemplo, utilizei a tabela employees do esquema HR, veja na Listagem 1.

Listagem 1: Uso do %BULK_ROWCOUNT.

  
01  DECLARE 
02  TYPE salarios IS TABLE OF NUMBER; 
03  lv_sal SALARIOS := salarios(2100, 2200,  2500, 2600); 
04  BEGIN 
05  FORALL i IN lv_sal.FIRST..lv_sal.LAST 
06  UPDATE employees 
07  SET salary = salary*2 
08  WHERE salary = lv_sal(i); 
09     
10  dbms_output.put_line('Quantidade total de linhas modificadas:  '||SQL%ROWCOUNT); 
11  FOR i IN lv_sal.FIRST..lv_sal.LAST LOOP 
12  dbms_output.put('Quantidade de linhas afetadas com salário de '); 
13  dbms_output.put(lv_sal(i)); 
14  dbms_output.put_line(' foram:  '||SQL%BULK_ROWCOUNT(i)); 
15  END LOOP; 
16  END; 
 
 

Nas linhas de 5 à 8 foi feito um FORALL, uma operação em BULK, em que foi atualizado a coluna salary com base nas linhas que possuíam um salário igual à algum dos 4 elementos da variável lv_sal. Foi feito então 4 updates. Se utilizarmos o %ROWCOUNT, a quantidade retornada será a total modificado nas 4 instruções feitas no FORALL, isto foi realizado na linha 10. A partir da linha 11 foi feito uso do atributo %BULK_ROWCOUNT, que armazena a quantidade de linhas retornadas ou modificadas em uma operação em BULK. Foi utilizado um FOR com os mesmos índices do FORALL, lv_sal.FIRST e lv_sal.LAST, para imprimir cada um dos resultados conforme mostra a Listagem 2.

Listagem 2. Resultado obtido com o  %BULK_ROWCOUNT.

  
Quantidade total de linhas modificadas: 13 
Quantidade de linhas afetadas com salário de 2100  foram: 1 
Quantidade de linhas afetadas com salário de 2200  foram: 2 
Quantidade de linhas afetadas com salário de 2500  foram: 6 
Quantidade de linhas afetadas com salário de 2600  foram: 4
 
 

Com o FORALL, podemos realizar operações em BULK utilizando instruções DML, mas infelizmente se uma dessas instruções resultar em uma exception, tudo que foi feito pela operação em bulk sofrerá um rollback caso não tenha um tratamento de exceções, ou seja, o bloco exception. Caso exista um tratamento para a exceção, tudo que foi feito antes será mantido, mas nada depois da exceção será executado. Existe um outro atributo de operações em bulk que pode contornar essa situação, o %BULK_EXCEPTIONS, que armazena informações sobre as exceções para consulta e permitindo assim que a operação em bulk continue mesmo após um erro. Para isto esse atributo conta com 3 campos. o COUNT que contem a quantidade de exceções. O  SQL_CODE que é o código do erro, mas positivo, então deverá ser multiplicado por -1 para associar a um erro existente no Oracle e assim, utilizar com  a função SQLERRM que retorna a mensagem de erro associado ao código. E por último o ERROR_INDEX, que é o index em que o erro foi gerado.

Para demonstrar um exemplo simples, crie a seguinte tabela, com uma restrição UNIQUE conforme a Listagem 3.

Listagem 3. CREATE TABLE

  
CREATE  TABLE bulk_ex( 
   numero NUMBER UNIQUE 
); 
 
 

Agora vamos testar algumas inserções nessa tabela violando a constraint UNIQUE. Para isto, repetimos dois números na variável do tipo table na linha 3, os números 3 e 6. Ao tentar inserir tudo no FORALL da linha 7 e 8 será gerado um erro para cada número repetido. Para que a operação não pare e continue mesmo com um erro, devemos colocar no final do FORALL a seguinte instrução: SAVE EXCEPTIONS conforme a Listagem 4.

Listagem 4. Utilizando o SQL%BULK_EXCEPTIONS

  
01  DECLARE 
02  TYPE tabela IS TABLE OF NUMBER; 
03  lv_numeros  TABELA := tabela(1,2,3,3,5,6,6); 
04  lv_exception EXCEPTION; 
05  PRAGMA EXCEPTION_INIT(lv_exception, -24381); 
06  BEGIN 
07  FORALL i IN  lv_numeros.FIRST..lv_numeros.LAST SAVE EXCEPTIONS 
08  INSERT INTO bulk_ex VALUES(lv_numeros(i)); 
09  EXCEPTION 
10  WHEN lv_exception THEN 
11  FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP 
12  dbms_output.put('Erro número '||i); 
13  dbms_output.put(' no index: '||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX); 
14  dbms_output.put_line(' com a seguinte  mensagem:   '||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)); 
16  END LOOP; 
17  END; 
 
 

No tratamento da exceção, utilizamos todos os 3 atributos do BULK_EXCEPTIONS. O COUNT foi utilizado no index do FOR para imprimir todos os erros. O ERROR_INDEX para exibir em qual index ocorreu o erro e por último, o ERROR_CODE. Tornamos ele negativo e utilizamos a função SQLERRM para exibir informações sobre o erro ocorrido. Tudo isto pode ser visto no retorno gerado na Listagem 5.

Listagem 5:

  
Erro número 1 No index: 4 Com a seguinte mensagem:  ORA-00001: restrição exclusiva (.) violada 
Erro número 2 No index: 7 Com a seguinte mensagem:  ORA-00001: restrição exclusiva (.) violada

 
 

Apesar de ocorrer dois erros, tudo fora os erros, até mesmo após o erro, foi adicionado na tabela. A tabela no final desse bloco em PL/SQL ficou com 5 linhas com os seguintes números: 1, 2, 3, 5 e 6.

Tércio Costa Formado em Ciências da Computação pela UFPB com experiência em Servidores Windows Server e Linux e banco de dados Oracle desde 2008 juntamente com os seus serviços. Desenvolvimento de Sistemas em Java SE com banco de dados Oracle e MySQL. Certificado Oracle Certified SQL Expert, mantendo o blog https://oraclepress.wordpress.com/ reconhecido pela OTN e articulista no portal http://www.profissionaloracle.com.br/gpo

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.