39

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);
Jay S
  • 7,904
  • 2
  • 39
  • 52

2 Answers2

49

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;
Iván
  • 129
  • 1
  • 7
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • Thank you Tony! I got this working (with a minor fix to close the quote on the execute immediate) and we're now in business! – Jay S Jun 22 '09 at 19:51
  • I'd suggest that querying the data dictionary every time you run this ALTER TABLE would be quite inefficient, compared to just handling the exception if it occurs. – Jeffrey Kemp Jun 23 '09 at 12:29
  • Jeffrey, you probably are correct, but this was for a schema change script so it's a run-once situation per database. Once the change has been made, it won't be run again. – Jay S Jun 23 '09 at 13:20
  • Using this approach appears not to always work. I have come across a schema where a column is reported as being nullable, but setting NOT NULL still throws ORA-01451. – waxwing Jun 17 '11 at 08:38
  • ORA-01403: no data found – zloctb Aug 21 '15 at 22:34
  • @zloctb, yes of course if your schema doesn't have a table MYTABLE with a column MYCOLUMN then that query will raise NO_DATA_FOUND :-) – Tony Andrews Aug 22 '15 at 06:54
28

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.

Rob van Laarhoven
  • 8,737
  • 2
  • 31
  • 49