MODIFY COLUMN in oracle - How to check if a column is nullable before setting to nullable?
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;
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 errorEND;/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.sqlor just do the alter table and ignore the error.