Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Script-Migration -i returns invalid SQL Script

CarstenL91Aug 6 2020 — edited Sep 10 2020

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;

BEGIN

SELECT 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 IF

END

/

The generated script is wrong.

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

DECLARE

    v_Count INTEGER;

BEGIN

SELECT 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 IF

END

/

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

This post has been answered by Alex Keh-Oracle on Aug 7 2020
Jump to Answer

Comments

Post Details

Added on Aug 6 2020
2 comments
382 views