database - MODIFY COLUMN in oracle - How to check if a column is nullable before setting to nullable?

Question

I'm trying to fill in for a colleague in doing some Oracle work, and ran into a snag. In attempting to write a script to modify a column to nullable, I ran into the lovely ORA-01451 error:

ORA-01451: column to be modified to NULL cannot be modified to NULL

This is happening because the column is already NULL. We have several databases that need to be udpated, so in my faulty assumption I figured setting it to NULL should work across the board to make sure everybody was up to date, regardless of whether they had manually set this column to nullable or not. However, this apparently causes an error for some folks who already have the column as nullable.

How does one check if a column is already nullable so as to avoid the error? Something that would accomplish this idea:

IF( MyTable.MyColumn IS NOT NULLABLE)
   ALTER TABLE MyTable MODIFY(MyColumn  NULL);

Answer 1

You could do this in PL/SQL:

declare
  l_nullable user_tab_columns.nullable%type;
begin
  select nullable into l_nullable
  from user_tab_columns
  where table_name = 'MYTABLE'
  and   column_name = 'MYCOLUMN';

  if l_nullable = 'N' then
    execute immediate 'alter table mytable modify (mycolumn null)';
  end if;
end;