Script-Migration -i returns invalid SQL Script — oracle-tech

    Forum Stats

  • 3,715,756 Users
  • 2,242,858 Discussions
  • 7,845,559 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Script-Migration -i returns invalid SQL Script

CarstenL91
CarstenL91 Member Posts: 26 Red Ribbon
edited September 2020 in ODP.NET

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

Best Answer

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,752 Employee
    edited August 2020 Accepted Answer

    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.

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,752 Employee
    edited August 2020 Accepted Answer

    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
    CarstenL91 Member Posts: 26 Red Ribbon
    edited September 2020

    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.

Sign In or Register to comment.