1 Reply Latest reply: Jun 28, 2012 8:48 AM by 946372 RSS

    Constraint error with array binding OracleCommand

    946372
      My database contains FIELD IS NOT NULL constraint, this constraint is deferrable.

      I run bulk insert/update operations using Array Binding with the OracleCommand object, the OracleCommand.ExecuteNonQuery() executed within OracleTransaction context, I expect that the constraint checking will be done only on OracleTransaction.Commit but instead the check is done in OracleCommand.ExecuteNonQuery() resulting constraint error.

      My code flow look something like this:

      Connection.Open()
      Connection.BeginTransaction()
      Loop
      OracleCommand.Text = "My Query"
      OracleCommand.Connection = Connection
      OracleCommand.ArrayBindCount = n
      OracleCommand.Parameters.Add()
      OracleCommand.ExecuteNonQuery() //ORA-02290: check constraint (FIELD_NOTNULL) violated. Why? this constraint is deferrable
      End Loop
      OracleTransaction.Commit();

      TIA,
      Yair