C# oracle execute non query with array bind count error with OracleDbType

Question

I have the following function to update a lot of registers of any table:

using (var conn = new Oracle.ManagedDataAccess.Client.OracleConnection(connString))
{
    conn.Open();
    foreach (KeyValuePair<string, BulkTableHelper> taula in taulesUpdate)
    {

        int j = taula.Value.Parameters.FirstOrDefault().Value.Count;
        int take = 1;
        int offset = 0;
        if (take > j)
            take = j;
            while (j > offset)
            {

                var command = conn.CreateCommand();

                List<Oracle.ManagedDataAccess.Client.OracleParameter> parameters = new List<Oracle.ManagedDataAccess.Client.OracleParameter>();

                var snakeCaseStrategy = new SnakeCaseNamingStrategy();
                string values = "";
                string where = "";


                foreach (KeyValuePair<string, List<object?>> parameter in taula.Value.Parameters)
                {
                    var paramName = snakeCaseStrategy.GetPropertyName(parameter.Key, false);

                    Oracle.ManagedDataAccess.Client.OracleParameter par = new Oracle.ManagedDataAccess.Client.OracleParameter();
                    
                    par.OracleDbType = GetOracleDbTypeFromType(taula.Value.ParametersType[parameter.Key]);

                    par.ParameterName = paramName;
                    var paramValues = parameter.Value.ToArray().Skip(offset).Take(take).ToArray();
                    par.Value = paramValues;

                    command.Parameters.Add(par);

                    if (taula.Value.PrimaryKeys.Contains(parameter.Key))
                    {
                        if (!string.IsNullOrEmpty(where))
                            where += " AND ";
                        where += $" {paramName} = :{paramName} ";
                    }
                    else
                    {
                        values += $"{paramName} = :{paramName}, ";
                    }
                }

                values = values.Remove(values.LastIndexOf(","), 1);

                string commandText = $"UPDATE {taula.Key} SET {values} WHERE {where}";

                command.CommandText = commandText;
                command.ArrayBindCount = take;
                int result = command.ExecuteNonQuery();

                offset += take;
                if (offset + take > j)
                {
                    take = j - offset;
                    offset = j;
                }

    }
}

This works fine and I'm able to update 65k rows in seconds, but I have a problem with a table:

  CREATE TABLE "SYS_MODIF_REG" 
   (    
    "CODI_INTERN" NUMBER(16,0) NOT NULL ENABLE, 
    "SINCRONITZAR" CHAR(1 BYTE) DEFAULT 'N', 
    "TIPUS" VARCHAR2(3 BYTE), 
    "CODI_SINCRO" NUMBER(16,0), 
    "VALOR1" VARCHAR2(40 BYTE), 
    "VALOR2" VARCHAR2(40 BYTE), 
    "DATA_A" DATE, 
    "DATA_E" DATE, 
    "DATA_M" DATE, 
    "DATA_B" DATE, 
    "DATA_R" DATE, 
    "ENTRADA_A" NUMBER(16,0), 
    "ENTRADA_E" NUMBER(16,0), 
    "ENTRADA_M" NUMBER(16,0), 
    "ENTRADA_B" NUMBER(16,0), 
    "ENTRADA_R" NUMBER(16,0),
CONSTRAINT "PK_SYS_MODIF_REG" PRIMARY KEY ("CODI_INTERN")
)

When updating this table it throws the following error: ORA-00932: inconsistent datatypes: expected NUMBER got DATE.

The oracle db type is OracleDbType.Date for the Date fields and the values are DateTime objects.

If I add the following code in order to not update the date fields:

if (paramName.StartsWith("data")
   continue;

It works and updates the table correctly, the problem is that it should not throw and error when adding the date fields.

I tried another table:

  CREATE TABLE "REF_TAR_FAM" 
   (   "EMPRESA" NUMBER(4,0) NOT NULL ENABLE, 
       "TARIFA" VARCHAR2(3 BYTE) NOT NULL ENABLE, 
       "FAMILIA" VARCHAR2(6 BYTE) NOT NULL ENABLE, 
       "COMPTADOR" NUMBER(16,0) NOT NULL ENABLE, 
       "QUANTITAT" NUMBER(15,5), 
       "DATA_MODIFICACIO" DATE, 
       "DTE_CLI_FAM" CHAR(1 BYTE) DEFAULT 'N', 
       CONSTRAINT "PK_REF_TAR_FAM" PRIMARY KEY ("COMPTADOR")
)

And when executing the update now the error is the following: ORA-00932: inconsistent datatypes: expected DATE got NUMBER.

The oracle db types is Date and the values are DateTime objects, it should be exactly the same as the other table. I do not understand whats happenning.

Other information that might be helpfull: The definition of the function GetOracleDbTypeFromType:

private Oracle.ManagedDataAccess.Client.OracleDbType GetOracleDbTypeFromType(Type t)
{
    if (t.Equals(typeof(long))) return Oracle.ManagedDataAccess.Client.OracleDbType.Int64;
    if (t.Equals(typeof(string))) return Oracle.ManagedDataAccess.Client.OracleDbType.NVarchar2;
    if (t.Equals(typeof(DateTime))) return Oracle.ManagedDataAccess.Client.OracleDbType.Date;
    if (t.Equals(typeof(Int64))) return Oracle.ManagedDataAccess.Client.OracleDbType.Int64;
    if (t.Equals(typeof(Int32))) return Oracle.ManagedDataAccess.Client.OracleDbType.Int32;
    if (t.Equals(typeof(Int16))) return Oracle.ManagedDataAccess.Client.OracleDbType.Int16;
    if (t.Equals(typeof(sbyte))) return Oracle.ManagedDataAccess.Client.OracleDbType.Byte;
    if (t.Equals(typeof(byte))) return Oracle.ManagedDataAccess.Client.OracleDbType.Int16; // <== unverified
    if (t.Equals(typeof(decimal))) return Oracle.ManagedDataAccess.Client.OracleDbType.Double;
    if (t.Equals(typeof(float))) return Oracle.ManagedDataAccess.Client.OracleDbType.Double;
    if (t.Equals(typeof(double))) return Oracle.ManagedDataAccess.Client.OracleDbType.Double;
    if (t.Equals(typeof(byte[]))) return Oracle.ManagedDataAccess.Client.OracleDbType.Blob;

    return Oracle.ManagedDataAccess.Client.OracleDbType.Varchar2;
}

The definition of BulkTableHelper:

        struct BulkTableHelper
        {
            public Dictionary<string, List<object?>> Parameters { get; set; }
            public Dictionary<string, Type> ParametersType { get; set; }
            public List<string> PrimaryKeys { get; set; }
            public List<PrimaryKeysHelper> DeletePrimaryKeys { get; set; }
            public List<string> SequenceKeys { get; set; }
        }

Images of the debugger:

I also tried to manually adding the values and it did work:

Oracle.ManagedDataAccess.Client.OracleParameter par1 = new Oracle.ManagedDataAccess.Client.OracleParameter();
par1.ParameterName = "data_a";
par1.OracleDbType = GetOracleDbTypeFromType(typeof(DateTime));
par1.Value = new List<DateTime?>() { DateTime.Now, null, DateTime.Now }.ToArray();

Oracle.ManagedDataAccess.Client.OracleParameter par2 = new Oracle.ManagedDataAccess.Client.OracleParameter();
par2.ParameterName = "codi_intern";
par2.OracleDbType = GetOracleDbTypeFromType(typeof(long));
par2.Value = new List<long>() { 2945548, 2945548, 2945548 }.ToArray();
command.Parameters.Add(par1);
command.Parameters.Add(par2);

string commandText = $"UPDATE {taula.Key} SET data_a = :data_a WHERE codi_intern = :codi_intern";

command.CommandText = commandText;
command.ArrayBindCount = 3;
int result = command.ExecuteNonQuery();

I don't know what's happening, if I debug this code the types look the same.

Answer 1

I found the solution, when binding parameters I have to set the property BindByName of the command to true:

command.BindByName = true;

If not it will bind the parameters in the order that are added to the command, and that's why it was throwing the error of incoherent datatype, cause the first parameter added was codi_intern which is a number and the first parameter on the sql was the parameter data_a which expected a date.