idempotent script can't be executed once a table has been dropped

[knalinne]

Versions of tools

dotnet: 5.0.302
ef tools: 5.0.10
Oracle: 12C

Issue

We use the idempotent script to update our production database.

Since we dropped an old table, the script always raise errors because some "INSERT" won't compile as the table no longer exists.

In the generated idempotent script, we have a block like this:

DECLARE
    v_Count INTEGER;
BEGIN
SELECT COUNT(*) INTO v_Count FROM "__EF_MIGRATION" WHERE "MIGRATION_ID" = N'20210902141511_Types';
IF v_Count = 0 THEN
    BEGIN
    INSERT INTO "TYPE" ("ID", "CODE")
    VALUES (1, N'INTERNAL');
    END;
 END IF;
END;

it doesn't compile because the "TYPE" table no longer exists.

According to this issue: dotnet/efcore#12911, the idempotent script should generate "execute immediate ..." query in the idempotent script.

in the example below, it should be something like:

DECLARE
    v_Count INTEGER;
BEGIN
SELECT COUNT(*) INTO v_Count FROM "__EF_MIGRATION" WHERE "MIGRATION_ID" = N'20210902141511_Types';
IF v_Count = 0 THEN
    BEGIN
    EXECUTE IMMEDIATE 'INSERT INTO "TYPE" ("ID", "CODE")
    VALUES (1, N''INTERNAL'')';
    END;
 END IF;
END;

[alexkeh]

I'm not able to reproduce this problem. I'm using the Oracle EF Core 5.21.3 provider with EF Core Tools 5.0.10. I see that the EXECUTE IMMEDIATE is being generated with the INSERT statement.

DECLARE
    v_Count INTEGER;
BEGIN
SELECT COUNT(*) INTO v_Count FROM "__EFMigrationsHistory" WHERE "MigrationId" = N'20210915195234_first';
IF v_Count = 0 THEN

    EXECUTE IMMEDIATE '
    INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
    VALUES (N''20210915195234_first'', N''5.0.10'')
    ';
 END IF;
END;

/

[knalinne]

Hello,

Thanks for your answer, It will be easier to explain the problem with an example: https://github.com/knalinne/OracleEfCoreTest

If you look at the Migrations, in the Domain folder you find 4 migrations:

• first one, that create a User and Type tables, with a one to many from User to Type
• second one, that add data in the 2 tables
• third one, removing the Type table
• a last one, creating a new user.

You'll also find in the root of the repository the generated idempotent script (idempotent-script.sql)

As you can see, an EXECUTE IMMEDIATE is being generated for INSERT statements, but only when inserting in the migration table.

For the data generated from the builder.HasData(...) method in the context configuration, the insert statements do not use an EXECUTE IMMEDIATE.

Example:

DECLARE
    v_Count INTEGER;
BEGIN
SELECT COUNT(*) INTO v_Count FROM "__EF_MIGRATION" WHERE "MigrationId" = N'20210915210308_AddUserAndType';
IF v_Count = 0 THEN

    BEGIN
    INSERT INTO "Types" ("Id", "Code")
    VALUES (1, N'INTERNAL');
    END;
 END IF;
END;

If you try to execute the idempotent script several times, it will only works the first time, then you'll get the error:

[65000][6550] ORA-06550: line 8, column 18: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 8, column 5: PL/SQL: SQL Statement ignored

[alexkeh]

Thanks! I've been able to reproduce the problem. I've filed bug 33360423 to have one of our EF Core engineers review it.

[knalinne]

Glad I could help.

[alexkeh]

This bug has been fixed, which should make it into the next patch release, 21.4.

[knalinne]

Hello @alexkeh ,

After migrating to version 5.21.4, it seems the generated idempotent script is no longer valid.

the script to create the Users table was :

execute immediate 'CREATE TABLE "Users" (
        "Id" NUMBER(10) NOT NULL,
        "Name" NVARCHAR2(2000),
        "TypeId" NUMBER(10) NOT NULL,
        CONSTRAINT "PK_Users" PRIMARY KEY ("Id"),
        CONSTRAINT "FK_Users_Types_TypeId" FOREIGN KEY ("TypeId") REFERENCES "Types" ("Id") ON DELETE CASCADE
    )';

after migrating to 5.21.4, the generated script is:

execute immediate 'CREATE TABLE "Users" (
        "Id" NUMBER(10) 1, 1 NOT NULL,
        "Name" NVARCHAR2(2000),
        "TypeId" NUMBER(10) NOT NULL,
        CONSTRAINT "PK_Users" PRIMARY KEY ("Id"),
        CONSTRAINT "FK_Users_Types_TypeId" FOREIGN KEY ("TypeId") REFERENCES "Types" ("Id") ON DELETE CASCADE
    )';

As you can see, "1, 1" has been added on the Id column, when executing the script I get the error: ORA-00907: missing right parenthesis.

Any idea ?

Thanks for your help

[alexkeh]

Sorry, the fix didn't make it into 21.4 as I expected. We ended up using an earlier label for that release. The fix will be in either 21.4.1 or 21.5. I'm verifying which release.

[alexkeh]

For Oracle EF Core 6, the fix is in 21.4.1 release. For Oracle EF Core 5, the fix is in the 21.5 release.