Error entering in batch using Oraclecommand

Question

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

I’m doing using the ArrayBindCount of Oraclecommand, but is returning zero reference error when passing the parameter, however I have already checked and the vector to enter when creating the parameter is normal and has no null object in the line that fires 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 the error would be inside the method new OracleParameter? I am using the dll Oracle.Dataaccess 4.112.3.0.

Note: I can’t use Stored Procedures to do the Inserts.

Answer 1

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

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

When running Insert no longer gave error and saved all vector data. I don’t know why when the input is a vector I need to use the method Overload, but it worked. the/

Answer 2

Rodrigo, why don’t you use foreach to fill in primitive types, or pass a string array to a stored Procedure?

Take a look at what the foreach would look 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 helped. Abs