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

Question

Hello all,

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 varchar2(1);
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;

Answer 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

or just do the alter table and ignore the error.

Answer 3

he "catch exception if it fails" approach works not if you do any additional changes beyound nullability.