Вопрос по database, schema, plsql, oracle – MODIFY COLUMN в oracle - Как проверить, можно ли обнулять столбец, прежде чем устанавливать на nullable?

34

Я пытаюсь заменить коллегу в работе с Oracle и натолкнулся на загадку. Пытаясь написать скрипт для изменения столбца в nullable, я столкнулся с прекрасной ошибкой ORA-01451:

<code>ORA-01451: column to be modified to NULL cannot be modified to NULL
</code>

Это происходит потому, что столбец уже равен NULL. У нас есть несколько баз данных, которые необходимо обновить, поэтому в своем ошибочном предположении я решил, что установка его в NULL должна работать по всем направлениям, чтобы убедиться, что все были в курсе, независимо от того, установили ли они этот столбец вручную в NULL или нет. Тем не менее, это, очевидно, вызывает ошибку у некоторых людей, которые уже имеют столбец как обнуляемый.

Как можно проверить, является ли столбец уже обнуляемым, чтобы избежать ошибки? Что-то, что могло бы реализовать эту идею:

<code>IF( MyTable.MyColumn IS NOT NULLABLE)
   ALTER TABLE MyTable MODIFY(MyColumn  NULL);
</code>

Ваш Ответ

2   ответа
44

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;
это спасатель, спасибо
Джеффри, вы, вероятно, правы, но это было для сценария изменения схемы, так что это случайная ситуация для каждой базы данных. Как только изменение было внесено, оно больше не запускается. Jay S
Я полагаю, что запрашивать словарь данных каждый раз, когда вы запускаете эту ALTER TABLE, было бы совершенно неэффективно, по сравнению с простой обработкой исключения, если оно происходит.
Спасибо, Тони! Я получил эту работу (с небольшим исправлением, чтобы закрыть цитату по выполнению немедленно), и мы сейчас в деле! Jay S
Использование этого подхода не всегда работает. Я сталкивался со схемой, в которой столбец считается обнуляемым, но установка NOT NULL все равно вызывает ORA-01451.
21

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

если вы не хотите использовать 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

или просто измените таблицу и проигнорируйте ошибку.

добавлятьhost rm -f to_null.sql (* nix) илиhost del /f to_null.sql чтобы не засорять каталог временными файлами.
Сразу хочу добавить, что этот подход очень широко используется в PLSQL
+1 для & quot; поймать исключение, если оно не удалось & quot; подход, который более эффективен, чем запрос словаря данных каждый раз

Похожие вопросы