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);

Solution 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;

Solution 2

just do the alter table and catch the exception.

DECLARE
   allready_null EXCEPTION;
   PRAGMA EXCEPTION_INIT(allready_null, -1451);
BEGIN
   execute immediate 'ALTER TABLE TAB MODIFY(COL  NULL)';
EXCEPTION
   WHEN allready_null THEN
      null; -- handle the error
END;
/

if you don't want to use PL/SQL

    set feedback off
    set echo off
    set feedback off
    set pages 0
    set head off

    spool to_null.sql

    select 'alter table TAB modify (COL NULL);' 
    from user_tab_columns
    where table_name = 'TAB'
    and column_name = 'COL'
    and nullable = 'N';

    spool off
    set feedback on
    set echo on
    set termout on
    @@to_null.sql 
    host rm -f to_null.sql

or just do the alter table and ignore the error.