oracle设置允许为空,Oracle ORA-01451: 要修改为 NULL 的列无法修改为 NULL

调试一段程序，遇到如题错误，查资料才发现Oracle中不允许将NULL字段修改为NULL字段。只好在修改之前做判断了。

打开PL/SQL，写如下代码

declare

visnull varchar2(4);

begin

select nullable into visnull from user_tab_columns

where table_name = upper('tblStockInspect')

-

and column_name = upper('FDepartID');

if visnull = 'N' then

alter table tblStockInspect modify FDepartID int null;

end if;

end;

运行，又出现错误提示如下

---------------------------------------------------------------------------

ORA-06550: 第 8 行, 第 7 列:

PLS-00103: 出现符号 "ALTER"在需要下列之一时：

( begin case declare exit

for goto if loop mod null pragma raise return select update

while with

<<

continue close current delete fetch lock insert open rollback

savepoint set sql execute commit forall merge pipe purge

---------------------------------------------------------------------------------

仔细一看，原来alter不允许在PL/SQL下直接运行，只好更改如下

declare

visnull varchar2(4);

begin

select nullable into visnull from user_tab_columns

where table_name = upper('tblStockInspect')

and column_name = upper('FDepartID');

if visnull = 'N' then

execute immediate 'alter table tblStockInspect modify FDepartID int null‘;

end if;

end;

运行通过

本文出自：亿恩科技【www.enkj.com】