Wrong SQL statement causes ORA-22859 error when trying to make nullable a CLOB column

[cesarhein]

Bug Report

Q	A
BC Break	no
Version	2.10.1

Summary

In a Oracle DB I'm trying to set as nullable a CLOB column in a table. But the SQL statement generated is wrong.

Current behaviour

The SQL statement generated currently is:

ALTER TABLE table_name MODIFY (column_name CLOB DEFAULT NULL NULL)

How to reproduce

1.- Create a table with a text (CLOB) column, not null, and without default value.
2.- Try to change column making it nullable and with default value = null.
3.- Get Error: ORA-22859

Expected behaviour

The correct SQL statement should be:

ALTER TABLE table_name MODIFY (column_name DEFAULT NULL NULL)

Same SQL statement but without data type.

Explanation

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.
For these reason, when we put the CLOB data type in the SQL statement, Oracle throw the ORA-22859 error assuming that we are trying to modify the data type.
To avoid this behaviour, we must simply omit the data type. Data type is not necessary for change nullable attribute or default value.

[morozov]

The reproducer:

$desiredTable = new Table('table_name');
$desiredTable->addColumn('column_name', 'text', ['notnull' => true]);

$sm = $connection->createSchemaManager();
$sm->createTable($desiredTable);

$actualTable = $sm->listTableDetails('table_name');
$desiredTable->getColumn('column_name')
    ->setNotnull(false)
    ->setDefault(null);
$comparator = $sm->createComparator();
$diff = $comparator->diffTable($actualTable, $desiredTable);
$sm->alterTable($diff);