Showstopper: OracleLoader and identity columns

Showstopper: OracleLoader and identity columns

Postby 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.
Dennis Wanke
 
Posts: 57
Joined: Tue 11 Mar 2014 07:49

Re: Showstopper: OracleLoader and identity columns

Postby 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: 7460
Joined: Thu 14 Aug 2008 12:44

Re: Showstopper: OracleLoader and identity columns

Postby 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.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: Showstopper: OracleLoader and identity columns

Postby Dennis Wanke » Fri 30 May 2014 07:59

This issue seems to be fixed now.
Dennis Wanke
 
Posts: 57
Joined: Tue 11 Mar 2014 07:49


Return to dotConnect for Oracle