Modify a column to NULL - Oracle

Question

I have a table named CUSTOMER, with few columns. One of them is Customer_ID.

Initially Customer_ID column WILL NOT accept NULL values.

I've made some changes from code level, so that Customer_ID column will accept NULL values by default.

Now my requirement is that, I need to again make this column to accept NULL values.

For this I've added executing the below query:

ALTER TABLE Customer MODIFY Customer_ID nvarchar2(20) NULL

I'm getting the following error:

ORA-01451 error, the column already allows null entries so
therefore cannot be modified

This is because already I've made the Customer_ID column to accept NULL values.

Is there a way to check if the column will accept NULL values before executing the above query...??

Answer 1

Or you can just ignore the error:

declare
    already_null exception;
    pragma exception_init (already_null , -01451);
begin
    execute immediate 'alter table <TABLE> modify(<COLUMN> null)';
    exception when already_null then null;
end;
/