Showtopper: OracleLoader with self-referenced tables

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Dennis Wanke
Posts: 57
Joined: Tue 11 Mar 2014 07:49

Showtopper: OracleLoader with self-referenced tables

Post by Dennis Wanke » Tue 06 May 2014 06:59

Another issue we encountered using OracleLoader (in Direct Mode, i.e. using array binding) is copying a self-referenced table (i.e. defining a foreign key like "ParentId" pointing to the same table). On closing the loader instance, we get an error “ORA-24381: error(s) in array DML” containing many nested errors “ORA-02291: integrity constraint (Xxx_Parent) violated - parent key not found”.

Can OracleLoader be improved to properly handle this scenario? This either could be done by disabling and re-enabling table constraints (similar to handling UNIQUEs) or by reorganizing table values supplied, i.e. performing a topological sort of the array entries based on the foreign key definitions (a rather sophisticated approach).

Note that SqlClient.SqlBulkCopy class supports this scenario by default (by not specifying "CheckConstraints" option).

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Showtopper: OracleLoader with self-referenced tables

Post by Pinturiccio » Thu 08 May 2014 13:46

We will investigate the possibility to use array bindings feature without constraint check and post here about the results as soon as possible.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Showtopper: OracleLoader with self-referenced tables

Post by Pinturiccio » Wed 14 May 2014 14:26

We have changed the behaviour: when using OracleLoader in the array binding mode, the constraint check is disabled for the time of data loading and is enabled again only when closing the OracleLoader object. We will post here when the corresponding build of dotConnect for Oracle is available for download.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Showtopper: OracleLoader with self-referenced tables

Post by Pinturiccio » Thu 15 May 2014 14:33

New build of dotConnect for Oracle 8.3.161 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=29592

Dennis Wanke
Posts: 57
Joined: Tue 11 Mar 2014 07:49

Re: Showtopper: OracleLoader with self-referenced tables

Post by Dennis Wanke » Fri 16 May 2014 18:59

Unfortunately, the fix contains a nasty bug, causing "ORA-02431: cannot disable constraint (PK_XXXX) - no such constraint". The problem is that a constraint name is not quoted in the script generated by OracleLoader:

Code: Select all

BEGIN
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.table_name = 'XXXX'
   AND c.status = 'ENABLED'
   ORDER BY c.constraint_type DESC)
  LOOP
    DBMS_UTILITY.EXEC_DDL_STATEMENT('ALTER TABLE "' || c.owner || '"."' || c.table_name || '" DISABLE CONSTRAINT ' || c.constraint_name);
  END LOOP;
END;
As one can see, both owner and table name are quoted, but the constraint name is not.

Please fix it ASAP and provide some kind of hotfix if possible, since the entire dotConnect build is not usable for us due to this problem. There also seems to be no simple workaround, as the constraint names are generated by EF Code First model builder.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Showtopper: OracleLoader with self-referenced tables

Post by Pinturiccio » Mon 19 May 2014 11:10

We have reproduced this issue as well. We will investigate it and post here about the results as soon as possible.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Showtopper: OracleLoader with self-referenced tables

Post by Pinturiccio » Tue 20 May 2014 08:58

We have fixed the bug with not quoting a constraint name when OracleLoader is used in array binding mode. We will post here when the corresponding build of dotConnect for Oracle is available for download.
Dennis Wanke wrote:Please fix it ASAP and provide some kind of hotfix if possible, since the entire dotConnect build is not usable for us due to this problem.
We have answered you via e-mail.

Dennis Wanke
Posts: 57
Joined: Tue 11 Mar 2014 07:49

Re: Showtopper: OracleLoader with self-referenced tables

Post by Dennis Wanke » Fri 30 May 2014 07:59

This issue seems to be fixed now.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Showtopper: OracleLoader with self-referenced tables

Post by Pinturiccio » Wed 25 May 2016 08:09

We have changed the behavior: the CreateColumns method of the OracleLoader class now does not add GENERATED ALWAYS AS IDENTITY columns to the collection in the OCI mode.

These changes are available starting from dotConnect for Oracle 9.0.7. You can download the latest dotConnect for Oracle version from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to viewtopic.php?t=33571

Post Reply