Quoting error in sql script generated by oracle ef core

[ckraus1990]

Hi everyone

I am using ef core 2.2 with devart oracle 9.6.696 model-first approach.
Now I want to build up the release pipeline and therefore I use the following command to generate a sql migration script:

Code: Select all

ef migrations script -o scriptfile.sql -i

As a following step I'd like to use SQL*Plus to execute the generated script in my database instance.
Unfortunately I get an error at the following statement:

Code: Select all

DECLARE
  pCount NUMBER;
BEGIN
  pCount := 0;
  SELECT count(*) INTO pCount FROM EFMigrationsHistory__ WHERE MigrationId = '20190220090546_MigrationName';
  IF (pCount <> 1) THEN
    EXECUTE IMMEDIATE '
    BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE MyTable MODIFY SomeColumn NOT NULL';
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE <> -1451 AND SQLCODE <> -1442 THEN
          RAISE;
        END IF;
    END;
';
  END IF;
END;
/

This is generated based on the following C# code in the corresponding migration:

Code: Select all

migrationBuilder.AlterColumn<string>(
                name: "SomeColumn",
                table: "MyTable",
                maxLength: 10,
                nullable: false,
                oldClrType: typeof(string),
                oldMaxLength: 10,
                oldNullable: true);
                

If I run this script in SQL*Plus I get this error:

Code: Select all

      EXECUTE IMMEDIATE 'ALTER TABLE MyTable MODIFY SomeColumn NOT NULL';
                         *
ERROR at line 9:
ORA-06550: line 9, column 26:
PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like like2 like4 likec between into using || multiset bulk
member submultiset

Is the an option to escape the qoutes of the inner execute immediate statement?

[Shalex]

Thank you for your report. We will notify you when the bug is fixed.

[ckraus1990]

Thank you very much!

Meanwhile I habe found another example:

Migrationstatement:

Code: Select all

            
 migrationBuilder.AddColumn<DateTime>(
       name: "ResultDate",
       table: "Orders",
       nullable: false,
       defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));

The resulting SQL will be the following:

Code: Select all

DECLARE
  pCount NUMBER;
BEGIN
  pCount := 0;
  SELECT count(*) INTO pCount FROM EFMigrationsHistory__ WHERE MigrationId = '20190213103229_AddOrderColumns';
  IF (pCount <> 1) THEN
    EXECUTE IMMEDIATE '
    ALTER TABLE Orders
    ADD ResultDate TIMESTAMP(7) DEFAULT TO_DATE('0001-01-01', 'yyyy-mm-dd') NOT NULL
';
  END IF;
END;
/

DEFAULT TO_DATE('0001-01-01', 'yyyy-mm-dd') should be
DEFAULT TO_DATE(''0001-01-01'', ''yyyy-mm-dd'') with escaped single quotes

[Shalex]

Thank you for your report. We will notify you when the issue is fixed.

[Shalex]

The bug with applying an escaped quotation to the idempotent script in EF Core 2 Code-First Migrations is fixed: viewtopic.php?f=1&t=38884.