修改表字段类型报错：ORA-01439: 要更改数据类型, 则要修改的列必须为空 && 输出可重复执行脚本

报错原因

在oracle修改user表字段deptid类型时遇到报错：“ORA-01439:要更改数据类型,则要修改的列必须为空”，是因为要修改字段的新类型和原来的类型不兼容。

如果要修改的字段数据为空时，则不会报这种类型的错误。

解决方案一：分步解决

要修改字段的新类型和原来的类型不兼容时，可以通过如下方式解决该问题：

  	-- 1、修改原字段名 deptid 为 deptid_tmp 
    alter table user rename column deptid to deptid_tmp;
    -- 2、增加一个和原字段名同名的字段 deptid 
    alter table user add (deptid varchar2(50));
    -- 3、将原字段 deptid_tmp 数据更新到增加的字段 deptid 
   	update user set deptid=trim(deptid_tmp);
    -- 4、更新完，删除原字段 deptid_tmp 
	alter table user drop column deptid_tmp;

解决方案一：完美解决

判断字段类型是否改过，支持重复执行

DECLARE
  num     NUMBER;
  sqltext VARCHAR2(3000);
BEGIN
  SELECT COUNT(1)
    INTO num
    FROM user_tab_columns
   WHERE table_name = UPPER('user')
     AND column_name = UPPER('deptid') AND data_type=UPPER('NUMBER');

  IF num = 1 THEN
    -- 修改原字段名 deptid 为 deptid_tmp 
    sqltext := 'alter table user rename column deptid to deptid_tmp';
    EXECUTE IMMEDIATE sqltext;
    -- 增加一个和原字段名同名的字段 deptid 
    sqltext := 'alter table user add (deptid varchar2(50))';
    EXECUTE IMMEDIATE sqltext;
    -- 将原字段 deptid_tmp 数据更新到增加的字段 deptid 
    sqltext := 'update user set deptid=trim(deptid_tmp)';
    EXECUTE IMMEDIATE sqltext;    
    -- 更新完，删除原字段 deptid_tmp 
    sqltext := 'alter table user drop column deptid_tmp';
    EXECUTE IMMEDIATE sqltext;    
  END IF;
end;
/