0 Replies Latest reply: Oct 2, 2012 3:51 AM by 664517 RSS

    BindArrayCount bulk insert, does the insert in wrong order & violates FK

    664517
      Hi.

      A dev asked for input for a program that inserts contracts and sub-contracts into a table. He says it's working in every environment we have bar one where we get ORA-02291 Integrity constraint violation on CONTRACT_FK21. The one that fails is 11.2.0.2 64bit Standard Edition Windows with Jan 2012 CPU. The databases that work are 11.2.0.1 x64/Std/Win no CPU and 11.2.0.2 x64/Std/Win May 2011 CPU.

      The application is the same version in all environments.

      Table looks like this:

      Name Null? Type
      ----------------------------------------------------------------------- -------- -------------------
      CONTRACTID NOT NULL NUMBER(38)
      CONTRACTCODE NOT NULL VARCHAR2(50)
      BUSINESSUNITID NOT NULL NUMBER(38)
      ...
      INVOICECONDITIONCONTRACTID NOT NULL NUMBER(38)

      There is a foreign key constraint within this table on INVOICECONDITIONCONTRACTID that references the primary key (CONTRACTID) in the same table:

      TABLE_NAME CONSTRAINT_NAME C R_OWNER R_CONSTRAINT_NAME DELETE_RU STATUS COLUMN_NAME POSITION
      -------------------- ------------------------- - -------- ---------------------------- --------- -------- ---------------------------- ----------
      CONTRACT CONTRACT_FK21 R UF CONTRACT_PK NO ACTION ENABLED INVOICECONDITIONCONTRACTID

      The program code uses BindArrayCount in ODP.net to create an array that is used when sending the insert to Oracle. My understanding is that this uses bulk inserts (FORALL) to perform the actual inserts within the database. The array is built so that the main contract is sent first (first index in array), then the subcontract that references the main contract.

      For some reason, in this one particular environment, the sub-contract is getting inserted first and fails on the foreign key constraint. In several other environments the main contract is always inserted first.

      I did a diff of the parameters for both databases and there are no 'engine' differences, just stuff like names and paths.

      Has anyone experienced this? Any tips? Does BindArrayCount send the data in random order or is there a parameter or method of sorting used in the sql engine when performing a FORALL insert that needs to be changed?

      I traced the session and formatted the file using tkprof and could see only one single insert statement but 2 rows were inserted (that was in the environment that works). Unfortunately, I could not get the bind variable info from v$sql_bind_capture...there were no values in string_value column and WAS_CAPTURED was NO for all rows.

      EDIT:

      Another question... If the first row insert in the FORALL fails, do we get that error code or is it only the last error code of the last insert that gets printed/sent back?

      Edited by: finite9 on 02-Oct-2012 01:50