Showtopper: OracleLoader with self-referenced tables
-
- Posts: 57
- Joined: Tue 11 Mar 2014 07:49
Showtopper: OracleLoader with self-referenced tables
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).
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).
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Showtopper: OracleLoader with self-referenced tables
We will investigate the possibility to use array bindings feature without constraint check and post here about the results as soon as possible.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Showtopper: OracleLoader with self-referenced tables
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.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Showtopper: OracleLoader with self-referenced tables
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
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
-
- Posts: 57
- Joined: Tue 11 Mar 2014 07:49
Re: Showtopper: OracleLoader with self-referenced tables
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:
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.
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;
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.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Showtopper: OracleLoader with self-referenced tables
We have reproduced this issue as well. We will investigate it and post here about the results as soon as possible.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Showtopper: OracleLoader with self-referenced tables
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.
We have answered you via e-mail.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.
-
- Posts: 57
- Joined: Tue 11 Mar 2014 07:49
Re: Showtopper: OracleLoader with self-referenced tables
This issue seems to be fixed now.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Showtopper: OracleLoader with self-referenced tables
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
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