Showstopper: OracleLoader and identity columns

[Dennis Wanke]

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]

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

[Shalex]

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]

This issue seems to be fixed now.