Devart Forums

Posted: **Mon 29 Jul 2019 09:15**

Hello dear DevArt team,

In our project we facing the follwoing problem when using devart [dotConnect for Oracle Professional]
We use a model first approache and have to create migrations for Oracle and MSSQL, databases.

When creating migrations for both database types we get no warnings or error messages, for our migration.
The current migration works for MSSQL database.

Oracle fails when executing the migration, with the attached error message 1 output.

Changing the mentioned datatype from DATETIME to INT (for testing purpose) results in attached error message 2

It's very unfortunatly that we don't get a warning/error during creation of the migration.
From looking at the created tables it seems only this one tabe fails, but a second execution (of update-database option in ef core ) is not possible, cause of error messages from already existing objects /tables.

Can we get this tabel with 2 identity columns to work on oracle ?

We appriciate the support for this issue.

Best Regards
Peter Handaric

##############################################
Dev Art Version: 9.7.805 (problem existing before with previous versions)

Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0

.Net Core version 2.2.104
ef core version 2.2.2-servicing-10034
##################################

##########################
Attachment 1
##########################

PM> update-database
2019-07-29 09:48:27.1628|Validation| WARN|Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data, this mode should only be enabled during development.
Failed executing DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='0']
CREATE TABLE "Stands" (
"Id" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
"Guid" NCLOB NULL,
"Name" NCLOB NULL,
"Selectable" NUMBER(1) NOT NULL,
"LastUse" TIMESTAMP(7) DEFAULT CONVERT(datetime, '01/01/1900') NOT NULL,
"ToolDefinitionId" NUMBER(10) NOT NULL,
"StandTypeId" NUMBER(10) NOT NULL,
"ReductionPositionsPreferred" NUMBER(1) NOT NULL,
PRIMARY KEY ("Id"),
CONSTRAINT "FK_Stands_StandTypes_StandTypeId" FOREIGN KEY ("StandTypeId") REFERENCES "StandTypes" ("Id")
ON DELETE CASCADE,
CONSTRAINT "FK_Stands_StandDefinitions_ToolDefinitionId" FOREIGN KEY ("ToolDefinitionId") REFERENCES "StandDefinitions" ("Id")
ON DELETE CASCADE
)
2019-07-29 09:48:28.9959|Command|ERROR|Failed executing DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='0']
CREATE TABLE "Stands" (
"Id" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
"Guid" NCLOB NULL,
"Name" NCLOB NULL,
"Selectable" NUMBER(1) NOT NULL,
"LastUse" TIMESTAMP(7) DEFAULT CONVERT(datetime, '01/01/1900') NOT NULL,
"ToolDefinitionId" NUMBER(10) NOT NULL,
"StandTypeId" NUMBER(10) NOT NULL,
"ReductionPositionsPreferred" NUMBER(1) NOT NULL,
PRIMARY KEY ("Id"),
CONSTRAINT "FK_Stands_StandTypes_StandTypeId" FOREIGN KEY ("StandTypeId") REFERENCES "StandTypes" ("Id")
ON DELETE CASCADE,
CONSTRAINT "FK_Stands_StandDefinitions_ToolDefinitionId" FOREIGN KEY ("ToolDefinitionId") REFERENCES "StandDefinitions" ("Id")
ON DELETE CASCADE
)|ORA-00904: "DATETIME": ungültige ID
(RelationalCommand.ExecuteNonQuery => RelationalCommand.Execute => RelationalLoggerExtensions.CommandError)
Devart.Data.Oracle.OracleException (0x80004005): ORA-00904: "DATETIME": ungültige ID
at .(Int32 )
at . (Int32 , )
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
at .ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Devart.Data.Oracle.OracleException (0x80004005): ORA-00904: "DATETIME": ungültige ID
at .(Int32 )
at . (Int32 , )
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
at .ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ORA-00904: "DATETIME": ungültige ID

######################################################################

#####################
Attachment 2
#####################
PM> update-database
2019-07-29 10:00:46.5059|Validation| WARN|Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data, this mode should only be enabled during development.
System.InvalidOperationException: The table "Stands" can have only one identity column. The list of identity columns: "Id", "LastUse".
at . (CreateTableOperation )
at . (CreateTableOperation )
at . (MigrationOperation )
at .(IEnumerable`1 )
at .(IEnumerable`1 , )
at Devart.Data.Oracle.Entity.Migrations.OracleEntityMigrationSqlGenerator.Generate(IReadOnlyList`1 migrationOperations, IModel model)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateUpSql(Migration migration)
at .GenerateUpSql(Migration )
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
The table "Stands" can have only one identity column. The list of identity columns: "Id", "LastUse".

#####################################################################

Posted: **Tue 30 Jul 2019 17:15**

ORA-00904: "DATETIME": ungültige ID

You have set a default value of the "LastUse" property to CONVERT(datetime, '01/01/1900'). But the CONVERT function can be used with SQL Server only.

System.InvalidOperationException: The table "Stands" can have only one identity column. The list of identity columns: "Id", "LastUse".

1. That is the correct warning because running the following script will fail with "ORA-30669: table can have only one identity column" on Oracle 12c and higher:

Code: Select all

CREATE TABLE "Stands" ( 
"Id" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
"LastUse" NUMBER GENERATED ALWAYS AS IDENTITY,
[...]
)

2. We will add the same warning in design-time validation of Entity Developer.

3. You can employ a workaround with a sequence as a default value in EF Core Migrations:

Code: Select all

    migrationBuilder.CreateSequence("STANDS_LASTUSE_SEQ");
    migrationBuilder.CreateTable(
        name: "Stands",
        columns: table => new
        {
            Id = table.Column<int>(nullable: false),
            LastUse = table.Column<int>(type: "NUMBER", nullable: false, defaultValueSql: "STANDS_LASTUSE_SEQ.NEXTVAL")
            //, [...]
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Stands", x => x.Id);
        });

Posted: **Thu 08 Aug 2019 06:45**

Thanks, for your quick answer.
I wasn't aware, that by using the "convert()" function I'm using a ms sql specific feature.

After I read your answer we could easily determine the problem.

We replaced that part in our dbcontext for the modelbuilder "Stands".
Now we are again compatible with Ms sql and Orcale 12c and higher.

Thanks for your support.

Devart Forums

Only 1 identy column allowed (error), but devart allows creation of migration

Only 1 identy column allowed (error), but devart allows creation of migration

Re: Only 1 identy column allowed (error), but devart allows creation of migration

Re: Only 1 identy column allowed (error), but devart allows creation of migration