Script-Migration -i returns invalid SQL Script

CarstenL91

Hi,

I have used Script-Migration to generate the SQL script to update databases, no matter what migration level they are.

For this I used the switch -i (--idempotent).

Extract of the generated script:

DECLARE    v_Count INTEGER;BEGINSELECT COUNT(*) INTO v_Count FROM "__EFMigrationsHistory" WHERE "MigrationId" = N'20190523102943_Initial'IF v_Count = 0 THEN    BEGIN     EXECUTE IMMEDIATE 'CREATE TABLE     "AP_ApplicationGroups" (        "ApplicationGroupId" RAW(16) NOT NULL,        "DSCreated" TIMESTAMP(7),        "DSCreatedBy" NVARCHAR2(20),        "DSModified" TIMESTAMP(7),        "DSModifiedBy" NVARCHAR2(20),        "Name" NVARCHAR2(64) NOT NULL,        "Position" NUMBER(10) NOT NULL,        CONSTRAINT "PK_AP_ApplicationGroups" PRIMARY KEY ("ApplicationGroupId")    )';    END; END IFEND/

The generated script is wrong.

A semicolon is missing in line 4, 21 and 22.

DECLARE    v_Count INTEGER;BEGINSELECT COUNT(*) INTO v_Count FROM "__EFMigrationsHistory" WHERE "MigrationId" = N'20190523102943_Initial'IF v_Count = 0 THEN    CREATE UNIQUE INDEX "IX_AP_ApplicationGroups_Name" ON "AP_ApplicationGroups" ("Name") END IFEND/

A semicolon is missing in line 4, 9 and 10.

Line 8 (Create Unique Index") has to been executed with "execute immediate".

Tested with .net Core 3.1 and Oracle.EntityFrameworkCore 3.19-beta2

Alex Keh-Oracle

Thanks for reporting. I was able to reproduce the problem in a couple of EF Core samples. I filed bug 31726832 to track the issue.

In my testing, I found that leaving out the Idempotent parameter generated a valid script that can be applied in the ODT Query Window or SQLPlus. For now, please use that as a workaround while my team investigates.

Alex Keh-Oracle

Thanks for reporting. I was able to reproduce the problem in a couple of EF Core samples. I filed bug 31726832 to track the issue.

In my testing, I found that leaving out the Idempotent parameter generated a valid script that can be applied in the ODT Query Window or SQLPlus. For now, please use that as a workaround while my team investigates.

CarstenL91

Hi Alex,

for completeness:

after updating my application to the production release of Oracle EF Core 3.1 (3.19.80), the output of Script-Migration is valid.

Thanks for the Bugfix.