Devart Forums

Posted: **Mon 27 Jan 2020 07:58**

Hey,
I'm in the process of evaluating whether to integrate DevArt Oracle driver into an application written in .NET Core 3.1. I'm using code-first migrations and the Oracle Server is on version "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0".

In a migration like this:

Code: Select all

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable(
        name: "TableName",
        columns: table => new
        {
            TenantName = table.Column<string>(nullable: true),
            ...

DevArt driver is generating column definitions for `TenantName` as `"TenantName" NVARCHAR2 NULL` (notice the missing max length for type definition).
When I try to appy the migrations, Oracle Server returns an error:

Code: Select all

Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='0']
CREATE TABLE "AbpAuditLogs" ( 
  "Id" RAW(16) NOT NULL,
  ...
  "TenantName" NVARCHAR2 NULL,
  ...
)
Failed executing DbCommand (14ms) [Parameters=[], CommandType='Text', CommandTimeout='0']
CREATE TABLE "AbpAuditLogs" ( 
  "Id" RAW(16) NOT NULL,
  ...
  "TenantName" NVARCHAR2 NULL,
  ...
)
Disposing transaction.
Closing connection to database '' on server 'SECRET_ORACLE_DSN'.
Closed connection to database '' on server 'SECRET_ORACLE_DSN'.
'MyAppMigrationsDbContext' disposed.
Devart.Data.Oracle.OracleException (0x80004005): ORA-00906: missing left parenthesis
   at ═  .╚(Int32 ╗)
   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 Devart.Common.Entity.cu.ExecuteNonQuery()
   at Devart.Data.Oracle.Entity.aj.h()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.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.UpdateDatabaseImpl(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ORA-00906: missing left parenthesis

The issue seems to be related to missing max-length in column `NVARCHAR2` definitions If I manually run the generated table definition I get the same error (ORA-00906: missing left parenthesis), but after adding a max length to some value, say 256, the errors disappears.

This issue seems to be related to viewtopic.php?t=32811 and a fix was released subsequently at the time.

Has there been a regressions, any guesses as to why SQL generation fails?

Posted: **Tue 28 Jan 2020 15:15**

We cannot reproduce the issue.

Case 1

Code: Select all

    class MyDbContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) =>
    optionsBuilder.UseOracle(@"User Id=scott;Password=tiger;Server=oracle12c.datasoft.local/ORCLPDB.datasoft.local;Direct=True;license key=...;");

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
        }
        DbSet<MyClass> MyClasses { get; set; }
    }

    public class MyClass
    {

        public int Id { get; set; }

        // [StringLength(100)]
        public string Data { get; set; }
    }

->

PM> add-migration 1
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "MyClasses",
                columns: table => new
                {
                    Id = table.Column<int>(nullable: false),
                    Data = table.Column<string>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_MyClasses", x => x.Id);
                });
        }

->

PM> script-migration
CREATE TABLE "MyClasses" ( 
  "Id" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
  "Data" NCLOB NULL,
  PRIMARY KEY ("Id")
)

Case 2

Code: Select all

    class MyDbContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) =>
    optionsBuilder.UseOracle(@"User Id=scott;Password=tiger;Server=oracle12c.datasoft.local/ORCLPDB.datasoft.local;Direct=True;license key=...;");

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
        }
        DbSet<MyClass> MyClasses { get; set; }
    }

    public class MyClass
    {

        public int Id { get; set; }

        [StringLength(100)]
        public string Data { get; set; }
    }

->

PM> add-migration 1
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "MyClasses",
                columns: table => new
                {
                    Id = table.Column<int>(nullable: false),
                    Data = table.Column<string>(maxLength: 100, nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_MyClasses", x => x.Id);
                });
        }

->

PM> script-migration
CREATE TABLE "MyClasses" ( 
  "Id" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
  "Data" NVARCHAR2(100) NULL,
  PRIMARY KEY ("Id")
)

Please tell us how we should modify any of these cases to reproduce the issue you have encountered.

Devart Forums

Devart.Data.Oracle.EFCore 9.10.909 generating invalid SQL, a potential regression?

Devart.Data.Oracle.EFCore 9.10.909 generating invalid SQL, a potential regression?

Re: Devart.Data.Oracle.EFCore 9.10.909 generating invalid SQL, a potential regression?