ORA-22859 for ALTER TABLE on CLOB-columns is unnecessary in some cases

[KRU]

Hi Devart :)

We are using EF Core Code-First migrations using Devart.Data.Oracle as our provider.

We ran into an issue where a "string" column had been initially created as a CLOB-column in the database, which was expected, as we did not supply a max-length.

Afterwards, we realized that an initially placed "[Required]" DataAnnotation was no longer relevant, and we now wish for the column to be nullable.
This operation should always be safe and I denote that I have executed the following statement successfully.

Code: Select all

ALTER TABLE SCHEMA.A_TEST_TABLE MODIFY (DESCRIPTION  NULL);

Please see the documentation you provide here:
https://blog.devart.com/entity-framewor ... html#note2

Modifying Columns
Oracle has a set of limitations on column modification operations. It is not allowed to modify data type for CLOB/NCLOB/BLOB columns even for an empty table. It is also not allowed to specify the NOT NULL constraint for a column that already has one, and to specify that the column is nullable if it is already nullable. We have made a workaround for the the latter limitation by generating more complex DDL, however there is no workaround for the LOB column modification limitation because it is impossible to determine whether the user wants to change data type. That’s why any ALTER’s for LOB columns are not allowed by default.

The part where you write "because it is impossible to determine whether the user wants to change data type" sounds a bit over the top, doesn't it?

Looking at the MigrationBuilder method-call, it looks like this:

Code: Select all

migrationBuilder.AlterColumn<string>(
    name: "DESCRIPTION",
    table: "A_TEST_TABLE",
    nullable: false,
    oldClrType: typeof(string),
    oldNullable: true);

Is it not possible to simply check for the setting of "oldNullable" to determine whether that is the intent of the change, along with checking the non-existence of other similar parameters, e.g. something like "newType"?

Thank you.

[KRU]

Hehe, it appears that reading 2 lines further got me a workaround via configuration :)

AlterColumnConfiguration class has the following public properties:

• LobModificationAllowed – determines whether to allow LOB column modification. Data type is not modified, only other column parameters are modified. False by default.

I do wonder why this would not be defaulted to true however - who would not want to set it to true?

[Shalex]

Hehe, it appears that reading 2 lines further got me a workaround via configuration :)

AlterColumnConfiguration class has the following public properties:

• LobModificationAllowed – determines whether to allow LOB column modification. Data type is not modified, only other column parameters are modified. False by default.

I do wonder why this would not be defaulted to true however - who would not want to set it to true?

We will investigate the question and notify you about the result.

[Shalex]

I do wonder why this would not be defaulted to true however - who would not want to set it to true?

LobModificationAllowed is false by default because EF Migrations in EF4/EF5/EF6 do not contain full information about the changes made, this may cause the failure of the whole migration. Oracle doesn't support transactional DDL, so rolling back the database state is difficult.

The metadata in EF Core is extended, and we can identify changes in the column.
Support for changing NULL/NOT NULL constraint for LOB columns in the AlterColumn operation of EF Core Code-First Migrations is added in v9.9.887.