Change constraint of clob column

Question

I want to change a NOT NULL constraint of a clob column to a NULL constraint. However, when trying

ALTER TABLE myTable ALTER COLUMN myClobCol clob NULL;

or

ALTER TABLE myTable modify myClobCol clob NULL;

I get the following errors:

ORA-01735: invalid ALTER TABLE option

or

ORA-22859: invalid modification of columns

What am I doing wrong? Do I have to use a temp column in this case too? I know the scenario of changing the data type from clob to varchar2 by using a temp column but here I just want to change the constraint. Why this is not possible?

Thanks in advance!

Answer 1

The ORA-22859 error is raised by Oracle on any attempt to modify a CLOB, NCLOB, or BLOB column data type. This comes from a column limitation for the so called LOB data types.

When specifying the new type in the ALTER TABLE command, as in your case:

ALTER TABLE table MODIFY column CLOB NULL;

The validation is triggered and the query is aborted as Oracle considers changing from CLOB to CLOB to be equally invalid.

The good thing is the ALTER TABLE command supports omitting pieces of the column definition you want to left intact. So, by changing it to:

ALTER TABLE table MODIFY column NULL;

You'll be able to drop the nullity constraint while keeping the same data type.