Showstopper: OracleLoader and identity columns

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

Showstopper: OracleLoader and identity columns

Post by Dennis Wanke » Mon 05 May 2014 11:16

We've just ran into a problem using OracleLoader with Oracle 12c database generated with EF's ObjectContext.CreateDatabase/Script(). Our scenario is to copy a table containing an identity column from one database (arbitrary DMS, e.g. Oracle, SQL Server, etc.) to the other (Oracle). The problem is that the source table has already all its values assigned, including those for the identity column. This works perfectly on Oracle 11g but fails on Oracle 12c with the error "ORA-32795: cannot insert into a generated always identity column", because the identity column is defined as GENERATED ALWAYS. Ignoring identity columns in application logic is not an option since it would require too much efforts, especially for complicated foreign key fix-up.

Note that SqlClient.SqlBulkCopy class does already support this scenario out-of-the-box by providing the "KeepIdentity" option.

We believe the problem could be easily solved by defining identity columns as GENERATED BY DEFAULT in ObjectContext.CreateDatabase/Script() or at least make such an option available via provider settings.

Shalex
Devart Team
Posts: 8082
Joined: Thu 14 Aug 2008 12:44

Re: Showstopper: OracleLoader and identity columns

Post by Shalex » Thu 08 May 2014 15:19

Thank you for your report. We will investigate the question and notify you about the result.

Shalex
Devart Team
Posts: 8082
Joined: Thu 14 Aug 2008 12:44

Re: Showstopper: OracleLoader and identity columns

Post by Shalex » Fri 16 May 2014 10:48

The behaviour is changed in the latest (8.3.161) build of dotConnect for Oracle: now the Code-First CreateDatabase()/CreateDatabaseScript()/Code-First Migrations functionality for Oracle 12c generates 'GENERATED BY DEFAULT ON NULL AS IDENTITY' instead of 'GENERATED ALWAYS AS IDENTITY' for autoincrement identity columns when config.DatabaseScript.Column.NumericIdentityBehavior = Default.

It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=29592.

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

Re: Showstopper: OracleLoader and identity columns

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

This issue seems to be fixed now.

Post Reply