ODP.NET Array Bind Error Index Not Set For Single Element Arrays

Posted on June 30, 2021

In a previous post, I showed that ODP.NET does not report which row caused an error when using array binding to perform DML on a view with an INSTEAD OF trigger. Unfortunately for .NET developers, there is more unexpected behavior.

ODP.NET does not report which row caused an error when ArrayBindCount is 1. This means that programmers need to handle this special case if they want to consistently be able to identify the row that caused a DML error. Of course, most operations that use array binding will process more than one row, but there are cases where the same code that could handle many rows will also handle single rows and will incorrectly assume identical behavior.

Example

I use Oracle.ManagedDataAccess.Core 3.21.1 and Oracle Database 12.1.0.2 EE for this example.

Consider the following database schema:

create table test (col1 number);

alter table test add constraint test_uq unique (col1);

insert into test (col1) values (1);

Run the following program:

using System;

using Oracle.ManagedDataAccess.Client;

namespace odp_test
{
  class Program
  {
    static void Main(string[] args)
    {
      string connectString = args[0];

      using OracleConnection conn = new OracleConnection(connectString);
      conn.Open();

      using OracleCommand cmd = conn.CreateCommand();
      cmd.CommandText = "insert into test (col1) values (:x)";
      cmd.BindByName = true;
      cmd.ArrayBindCount = 1;

      OracleParameter prm = new OracleParameter();
      prm.ParameterName = "x";
      prm.Value = new int[] {1};

      cmd.Parameters.Add(prm);

      try
      {
        cmd.ExecuteNonQuery();
      }
      catch (OracleException e)
      {
        System.Console.WriteLine("Main exception:");
        System.Console.WriteLine(e.ToString());
        System.Console.WriteLine("End main exception");
        System.Console.WriteLine();
        System.Console.WriteLine();

        foreach (OracleError error in e.Errors)
        {
          System.Console.WriteLine("OracleError:");
          System.Console.WriteLine(error.Message);
          System.Console.WriteLine();
          System.Console.Write("Caused by array bind index ");
          System.Console.WriteLine(error.ArrayBindIndex);
          System.Console.WriteLine();
          System.Console.WriteLine();
        }
      }
    }
  }
}

The output shows that the array bind index is -1 instead of 0:

Main exception:
Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00001: unique constraint (PRODUCT.TEST_UQ) violated
   at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
   at odp_test.Program.Main(String[] args) in /home/steve/odp-test/Program.cs:line 29
End main exception


OracleError:
ORA-00001: unique constraint (PRODUCT.TEST_UQ) violated


Caused by array bind index -1