运行Oracle修改列类型脚本报PLS-00103错误如何解决

问题原因 #

• Oracle数据库客户端默认以分号;作为普通SQL语句的结束标记，但PL/SQL匿名块内部本身就包含分号，因此块结束后需要额外添加单独一行的斜杠/，标记整个PL/SQL块执行完毕。
• 原脚本没有添加该分隔符，客户端会把第一个PL/SQL块后的ALTER语句误判为块内内容，而PL/SQL语法不允许直接写裸的DDL语句，因此抛出PLS-00103编译错误。

修复方案 #

方案1：添加PL/SQL块结束分隔符（改动最小） #

只需要在两个PL/SQL匿名块的END;后面各加一行单独的/即可，修复后完整脚本如下：

BEGIN
FOR i in (SELECT CONSTRAINT_NAME, TABLE_NAME FROM user_constraints WHERE TABLE_NAME = 'DATA_MAPP') LOOP 
EXECUTE IMMEDIATE 'alter table '||i.table_name||' disable constraint '||i.constraint_name||'';
END LOOP;
END;
/

ALTER TABLE MAPP_SOA.DATA_MAPP ADD (DATA_MAPP_ID_RESERVA NUMBER(10)); 
UPDATE MAPP_SOA.DATA_MAPP SET DATA_MAPP_ID_RESERVA = DATA_MAPP_ID; 
UPDATE MAPP_SOA.DATA_MAPP SET DATA_MAPP_ID = NULL;
ALTER TABLE MAPP_SOA.DATA_MAPP MODIFY DATA_MAPP_ID VARCHAR(30 CHAR);
UPDATE MAPP_SOA.DATA_MAPP SET DATA_MAPP_ID = DATA_MAPP_ID_RESERVA;
ALTER TABLE MAPP_SOA.DATA_MAPP DROP COLUMN DATA_MAPP_ID_RESERVA; 

BEGIN
FOR i in (SELECT CONSTRAINT_NAME, TABLE_NAME FROM user_constraints WHERE TABLE_NAME = 'DATA_MAPP') LOOP 
EXECUTE IMMEDIATE 'alter table '||i.table_name||' enable constraint '||i.constraint_name||'';
END LOOP;
END;
/

方案2：所有逻辑合并到单个PL/SQL块 #

也可以把中间的DDL、DML语句都通过EXECUTE IMMEDIATE封装到同一个PL/SQL块里，不需要额外拆分分隔符：

BEGIN
  -- 禁用所有约束
  FOR i in (SELECT CONSTRAINT_NAME, TABLE_NAME FROM user_constraints WHERE TABLE_NAME = 'DATA_MAPP') LOOP 
    EXECUTE IMMEDIATE 'alter table '||i.table_name||' disable constraint '||i.constraint_name;
  END LOOP;

  -- 字段类型修改逻辑
  EXECUTE IMMEDIATE 'ALTER TABLE MAPP_SOA.DATA_MAPP ADD (DATA_MAPP_ID_RESERVA NUMBER(10))';
  EXECUTE IMMEDIATE 'UPDATE MAPP_SOA.DATA_MAPP SET DATA_MAPP_ID_RESERVA = DATA_MAPP_ID';
  EXECUTE IMMEDIATE 'UPDATE MAPP_SOA.DATA_MAPP SET DATA_MAPP_ID = NULL';
  EXECUTE IMMEDIATE 'ALTER TABLE MAPP_SOA.DATA_MAPP MODIFY DATA_MAPP_ID VARCHAR(30 CHAR)';
  EXECUTE IMMEDIATE 'UPDATE MAPP_SOA.DATA_MAPP SET DATA_MAPP_ID = DATA_MAPP_ID_RESERVA';
  EXECUTE IMMEDIATE 'ALTER TABLE MAPP_SOA.DATA_MAPP DROP COLUMN DATA_MAPP_ID_RESERVA';

  -- 重新启用所有约束
  FOR i in (SELECT CONSTRAINT_NAME, TABLE_NAME FROM user_constraints WHERE TABLE_NAME = 'DATA_MAPP') LOOP 
    EXECUTE IMMEDIATE 'alter table '||i.table_name||' enable constraint '||i.constraint_name;
  END LOOP;
END;
/

注意事项 #

执行脚本前建议先备份DATA_MAPP表全量数据，避免意外操作导致数据丢失。

内容的提问来源于stack exchange，提问作者nauanalima