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

柔情只为你懂 2023-02-26 02:14 29阅读 0赞

# 报错原因 #

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