Page 1 of 1
Showtopper: OracleLoader with self-referenced tables
Posted: Tue 06 May 2014 06:59
by Dennis Wanke
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).
Re: Showtopper: OracleLoader with self-referenced tables
Posted: Thu 08 May 2014 13:46
by Pinturiccio
We will investigate the possibility to use array bindings feature without constraint check and post here about the results as soon as possible.
Re: Showtopper: OracleLoader with self-referenced tables
Posted: Wed 14 May 2014 14:26
by Pinturiccio
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.
Re: Showtopper: OracleLoader with self-referenced tables
Posted: Thu 15 May 2014 14:33
by Pinturiccio
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
Re: Showtopper: OracleLoader with self-referenced tables
Posted: Fri 16 May 2014 18:59
by Dennis Wanke
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.
Re: Showtopper: OracleLoader with self-referenced tables
Posted: Mon 19 May 2014 11:10
by Pinturiccio
We have reproduced this issue as well. We will investigate it and post here about the results as soon as possible.
Re: Showtopper: OracleLoader with self-referenced tables
Posted: Tue 20 May 2014 08:58
by Pinturiccio
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.
Re: Showtopper: OracleLoader with self-referenced tables
Posted: Fri 30 May 2014 07:59
by Dennis Wanke
This issue seems to be fixed now.
Re: Showtopper: OracleLoader with self-referenced tables
Posted: Wed 25 May 2016 08:09
by Pinturiccio
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