在oracle中修改列 - 如何在设置为可空之前检查列是否为空？ [英] MODIFY COLUMN in oracle - How to check if a column is nullable before setting to nullable?

问题描述

我正在尝试填写一个同事在做一些Oracle的工作，并遇到一个障碍。在尝试写一个脚本来修改一个列为可空的时候，我遇到了可爱的ORA-01451错误：

  ORA-01451 ：要修改为NULL的列不能修改为NULL 
  

这是因为列已经为NULL 。我们有几个数据库需要被udpated，所以在我错误的假设我想把它设置为NULL应该全面工作，以确保每个人都是最新的，无论他们是否手动设置此列为可空。但是，这显然会导致一些已经拥有该列为空的人的错误。

如何检查列是否已经可以为空，以避免错误？可以实现这个想法的东西：

  IF（MyTable.MyColumn IS NOT NULLABLE）
 ALTER TABLE MyTable MODIFY空值）;您可以在PL / SQL中执行此操作：

p>



  declare 
 l_nullable varchar2（1）; 
 begin 
 select null null into l_nullable 
 from user_tab_columns 
其中table_name ='MYTABLE'
和column_name ='MYCOLUMN'; 
 
 if l_nullable ='N'then 
执行立即'alter table mytable modify（mycolumn null）'; 
 end if; 
 end; 
  




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

解决方案

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;

这篇关于在oracle中修改列 - 如何在设置为可空之前检查列是否为空？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！