Error when inserting in batch using OracleCommand

Question

Error when inserting in batch using OracleCommand

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I need to optimize a load application that runs through a database and writes to another database, but that has to be done through a Windows Forms application.

I am doing this using ArrayBindCount of OracleCommand , but it is returning null reference error when passing the parameter, however I checked and the input vector when creating the parameter is normal and has no null object in the line that triggers the problem:

OracleConnection con = new OracleConnection(txtDestino.Text);
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.BindByName = true;
cmd.ArrayBindCount = totalRegistros;
cmd.CommandText = @"insert into solicitacao (cd_codigo) values (:cdCodigo)";
List<int> array_CodSolicitacaoNovo = new List<int>();
array_CodSolicitacaoNovo.Add(12);
array_CodSolicitacaoNovo.Add(13);
array_CodSolicitacaoNovo.Add(14);
//ERRO NESTA LINHA ABAIXO
cmd.Parameters.Add(new OracleParameter("v_soli_cd_codigo", array_CodSolicitacaoNovo.ToArray());

//Executado apenas uma vez
con.Open();
cmd.ExecuteNonQuery();
con.Close();

If it has no null object, would the error be inside the new OracleParameter method? I am using the Oracle.DataAccess 4.112.3.0 dll .

Note: I can not use Stored Procedures to do the inserts.

c# oracle .net

asked by anonymous 15.01.2016 / 17:09

2 answers

sending email with pause of 2 seconds between emails Mavem project error warning

score 0 · Answer 1

Rodrigo, why do not you use foreach to populate with primitive types, or by passing a string array to a stored procedure?

Take a look at what the foreach looks like:

OracleConnection con = new OracleConnection(txtDestino.Text);
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.BindByName = true;
cmd.ArrayBindCount = totalRegistros;
cmd.CommandText = @"insert into solicitacao (cd_codigo) values (:cdCodigo)";
List<int> array_CodSolicitacaoNovo = new List<int>();
array_CodSolicitacaoNovo.Add(12);
array_CodSolicitacaoNovo.Add(13);
array_CodSolicitacaoNovo.Add(14);


foreach (var item in array_CodSolicitacaoNovo)
{
     cmd.Parameters.Add(new OracleParameter("v_soli_cd_codigo", item.ToString());
}

//Executado apenas uma vez
con.Open();
cmd.ExecuteNonQuery();
con.Close();

I hope I have helped. Abs

score 0 · Answer 2

I was able to find a solution, the problem was to specify the data type and whether it was input or output at the time of creating the parameter:

cmd.Parameters.Add(new OracleParameter("v_soli_cd_codigo", OracleDbType.Int32, array_CodSolicitacaoNovo.ToArray(), System.Data.ParameterDirection.Input));

When executing the insert, it did not give more error and recorded all the data of the vector. I do not know why when the input is a vector I need to use the method overload, but it worked. \ o /