文章內(nèi)容

Oracle ORA-01451: 要修改為 NULL 的列無(wú)法修改為 NULL

發(fā)布時(shí)間: 2012/9/14 16:57:31

調(diào)試一段程序，遇到如題錯(cuò)誤，查資料才發(fā)現(xiàn)Oracle中不允許將NULL字段修改為NULL字段。只好在修改之前做判斷了。
打開(kāi)PL/SQL，寫(xiě)如下代碼
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;

運(yùn)行，又出現(xiàn)錯(cuò)誤提示如下
---------------------------------------------------------------------------
ORA-06550: 第 8 行, 第 7 列:
PLS-00103: 出現(xiàn)符號(hào) "ALTER"在需要下列之一時(shí)：
( begin case declare exit
for goto if loop mod null pragma raise return select update
while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
---------------------------------------------------------------------------------
仔細(xì)一看，原來(lái)alter不允許在PL/SQL下直接運(yùn)行，只好更改如下
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;
運(yùn)行通過(guò)