Inserindo dados no Oracle em Bulk (utilizando odp.net)
Muitas vezes é necessário utilizar diferentes BDs para o armazenamento de dados. No RiskSystem, por exemplo, utilizamos Sql Server, mas recentemente tivemos que fazer uma integração com o Oracle. Conto aqui minha experiência.
Ao inserir muitos registros é ideal fazer inserts em Bulks, visando o ganho de performance. Em Sql o framework fornece a classe SqlBulkCopy, para o Oracle o namespace System.Data.OracleClient do framework possuí suas principais classes marcadas como deprecated. Uma solução é utilizar o Oracle Data Provider for .Net (odp.net).
Foram utilizados:
- BD Oracle 10g;
- .Net 3.5 sobre o framework 2.0
- ODAC 11.2 Release 3 (11.2.0.1.2). Importante lembrar que não basta referenciar a dll Oracle.DataAccess no projeto, a instalação do odp.net deve ser feita na máquina a ser utilizada e de acordo com a estrutura do software (No nosso caso, 32 bits).
A primeira tentativa foi utilizar o OracleBulkCopy, muito similar ao SqlBulkCopy:
public class OracleRecordWithOracleBulkCopy
{
public void DeleteOldResults(OracleConnection connection)
{
using (var connection = new OracleConnection(ConnectionString))
{
connection.Open();
using (var command = new OracleCommand(
"delete from DBTest.DateCode", connection))
{
command.ExecuteNonQuery();
}
}
}
public void Record(...)
{
using (var connection = new OracleConnection(ConnectionString))
{
connection.Open();
var table = new DataTable();
table.Columns.Add("Data", typeof(DateTime));
table.Columns.Add("Code", typeof(string));
var bulkCopy = new OracleBulkCopy(connection);
bulkCopy.DestinationTableName = "DBTest.DateCode";
bulkCopy.BatchSize = 2000;
for (int i = 0; i < table.Columns.Count; i++)
{
bulkCopy.ColumnMappings.Add(
table.Columns[i].ColumnName,
table.Columns[i].ColumnName);
}
foreach(var item in list)
{
table.Rows.Add(item.Date, item.Code);
//Para não utilizar muita memória,
//faço o dump assim que a tabela atingir o BatchSize
if (table.Rows.Count >= bulkCopy.BatchSize)
{
bulkCopy.WriteToServer(table);
table.Rows.Clear();
}
}
bulkCopy.WriteToServer(table);
}
}
}
A princípio tudo funcionou bem. Quando o número de registros inseridos aumentou, o trecho passava a lançar exceção:
System.NullReferenceException: object reference not set to an instance of an object. in Oracle.DataAccess.Client.OracleBulkCopy.PerformBulkCopy() in Oracle.DataAccess.Client.OracleBulkCopy.WriteDataSourceToServer() in Oracle.DataAccess.Client.OracleBulkCopy.WriteToServer(DataTable table, DataRowState rowState) in Oracle.DataAccess.Client.OracleBulkCopy.WriteToServer(DataTable table)
Ao executar o programa logo depois recebíamos outro erro. Desta vez:
OracleDataAccess.Client.OracleException ORA-24795: Illegal COMMIT attempt made in Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck) in Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck) in Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
Após a primeira chamada, um recurso continuava a acessar a tabela. Esperando algum tempo antes de roda-lo novamente a exceção original voltava a ocorrer.
Ao pesquisar o primeiro erro, diferentes opniões em diferentes datas foram encontrados. Alguns acusavam falta de permissão no BD ou na pasta de instalação do odp.net, outros apontavam que para um número de registros inseridos em cada Batch menor, o problema cessava. Mas o consenso era que o provider precisa de correções.
No BD, 10.005 registros eram inseridos antes do erro. Ao fazer testes em programas apartados, com diferentes números de registros e tamanhos do batch, tudo funcionava, e o erro acontecia somente no ambiente de nosso Cliente.
Enquanto os testes eram feitos olhei alternativas ao OracleBulkCopy e programei uma nova classe, utilizando ArrayBinding:
public class OracleRecordWithArrayBinding
{
public void DeleteOldResults(OracleConnection connection)
{
using (var connection = new OracleConnection(ConnectionString))
{
try
{
connection.Open();
using (var command = new OracleCommand(
"delete from DBTest.DateCode", connection))
{
command.ExecuteNonQuery();
}
}
finally
{
//Se não fizeram o dispose corretamente, melhor garantir
if(connection != null)
{
connection.Close();
}
}
}
}
public void Record(...)
{
using (var connection = new OracleConnection(ConnectionString))
{
try
{
using (var command = connection.CreateCommand())
{
FillCommand(command);
var list = new List();
foreach (var result in priceReportResults)
{
list.Add(result);
if (list.Count >= command.FetchSize)
{
Flush(list, command);
list.Clear();
}
}
Flush(list, command);
}
}
finally
{
//Se não fizeram o dispose corretamente, melhor garantir
if(connection != null)
{
connection.Close();
}
}
}
}
private void Flush(ICollection- list, OracleCommand command)
{
if (list.Count < = 0) return;
command.ArrayBindCount = list.Count;
command.Parameters.Clear();
command.Parameters.Add(":Data", OracleDbType.Date,
list.Select(x => x.Date).ToArray(),
ParameterDirection.Input);
command.Parameters.Add(":Code", OracleDbType.Varchar2,
list.Select(x => x.Code).ToArray(),
ParameterDirection.Input);
command.ExecuteNonQuery();
}
private void FillCommand(OracleCommand command)
{
const string insertCommandText = @"insert into DBTest.DateCode
(Data, Code)
values
(:Data, :Code)";
command.CommandText = insertCommandText;
command.BindByName = true;
command.FetchSize = 2000;
}
}
Os testes indicaram que a nova classe executava entre 2x e 3x mais rápido que a anterior. (Obviamente os testes foram feitos levando em conta apenas os Inserts). A versão com a nova classe foi carregada no servidor e passou a funcionar sem maiores problemas.
Quanto ao segundo erro, verificado ao tentar remover registros, acredito que seja por causa do esquecimento ao chamar “Dispose()” no OracleBulkCopy, mas ainda não pude confirmar. A classe original foi alterada e fica no aguardo caso sejam necessários mais testes.
Fica um lembrete para sempre chamar “Dispose()” nos objetos e verificar usos de objetos, para evitar a temida “System.NullReferenceException” com Oracle.
