ODP.NET Array Binding Error Handling with Views

Posted on May 26, 2021

Although undocumented, the Oracle Data Provider for .NET does not seem to support Error Handling for Array Binding when performing DML operations on views that use INSTEAD OF triggers.

DML Operations on Views

Database users can perform DML on views in many cases, such as:

  1. When a view simply filters a single base table.
  2. When a view joins multiple tables in a way that satisfies certain conditions that make the view an Updatable Join View.
  3. When a more complex view has INSTEAD OF triggers defined on it.

Although users can perform DML operations on these views, there are some undocumented limitations compared to performing DML on a table. One of these limitations concerns error handling when using array binding.

What is array binding?

Array binding is commonly used to reduce database roundtrips when performing bulk DML operations. For example, if an application needs to insert 1000 rows, it can create a single command and bind an array containing the data for the 1000 rows so that only a single database roundtrip is required instead of 1000.

When applications use bulk DML operations, however, it can be hard to determine which row caused an error. To help with this, Oracle data access drivers (such as ODP.NET) provide means to find out which row or rows caused an error. Unfortunately, the ODP.NET driver returns -1 as the error-causing row when performing DML on views with INSTEAD OF triggers, so application developers have to use other means to find out which row caused an error.

Example

For example, consider a schema defined as follows:

create table test (col1 number);

alter table test add constraint test_uq unique (col1);

create view v_test as
select * from test
;

create trigger t_v_test
instead of insert on v_test
begin
    insert into test (col1) values (:new.col1);
end;
/

insert into test (col1) values (1);

Try running 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 v_test (col1) values (:x)";
      cmd.BindByName = true;
      cmd.ArrayBindCount = 2;

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

      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 OracleError.ArrayBindIndex is -1:

Main exception:
Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00001: unique constraint (PRODUCT.TEST_UQ) violated
ORA-06512: at "PRODUCT.T_V_TEST", line 2
ORA-04088: error during execution of trigger 'PRODUCT.T_V_TEST'
   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
ORA-06512: at "PRODUCT.T_V_TEST", line 2
ORA-04088: error during execution of trigger 'PRODUCT.T_V_TEST'


Caused by array bind index -1

Now, let us drop the trigger:

drop trigger t_v_test;

Without the INSTEAD OF trigger, the OracleError shows that the 0th row caused the error:

Main exception:
Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-24381: error(s) in array DML

ORA-00001: unique constraint (PRODUCT.TEST_UQ) violated
   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-24381: error(s) in array DML


Caused by array bind index -1


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


Caused by array bind index 0