Oracle 19c中PL/SQL嵌套Execute Immediate调用存储过程报错排查

问题描述 #

使用Oracle 19c，在PL/SQL的EXECUTE IMMEDIATE子句中嵌套执行dbms_pdb.exec_as_oracle_script存储过程时触发语法错误，报错信息如下：

第5行错误:
ORA-06550: 第5行，第22列:
PLS-00103: 遇到符号 "EXEC"，需要下列之一:

• & = - + ; < / > at in is mod remainder not rem return
  returning <指数()> <> or != or ~= >= <= <> and or
  like like2 like4 likec between into using || multiset bulk
  member submultiset
  ORA-06550: 第5行，第62列:
  PLS-00103: 遇到符号 "ALTER"，需要下列之一:
  ) , * & = - + < / > at in is mod remainder not rem =>
  <指数()> <> or != or ~= >= <= <> and or like like2
  like4 likec between || multiset char member byte submultiset

原PL/SQL代码：

DECLARE
 err_code EXCEPTION;
 PRAGMA EXCEPTION_INIT(err_code, -4043);
BEGIN
 execute immediate ''exec dbms_pdb.exec_as_oracle_script(''''alter type WRI$_REPT_ASH_OMX compile'''')'';
 execute immediate ''exec dbms_pdb.exec_as_oracle_script(''''alter type WRI$_REPT_AUTO_INDEX compile'''')'';
 execute immediate ''exec dbms_pdb.exec_as_oracle_script(''''alter type WRI$_REPT_ASH_OMX compile'''')'';
 execute immediate ''exec dbms_pdb.exec_as_oracle_script(''''alter type WRI$_REPT_AUTO_INDEX compile'''')'';
 execute immediate ''exec dbms_pdb.exec_as_oracle_script(''''alter package PRVT_EMX compile body'''')'';
 EXCEPTION
   WHEN err_code THEN NULL;
END;
/

核心问题 #

1. EXEC是SQL*Plus专属命令，不支持在EXECUTE IMMEDIATE中使用：EXEC是SQL*Plus工具提供的快捷执行存储过程的命令，不属于标准PL/SQL语法，EXECUTE IMMEDIATE只能解析标准的PL/SQL或SQL语句，因此遇到EXEC会触发语法错误。
2. 单引号嵌套虽然格式上做了转义，但结合EXEC的错误，导致后续语句解析失败。

修正方案 #

方案一：使用标准PL/SQL块作为动态执行内容 #

将EXEC替换为标准的BEGIN...END块调用存储过程，同时修正单引号转义：

DECLARE
 err_code EXCEPTION;
 PRAGMA EXCEPTION_INIT(err_code, -4043);
BEGIN
 execute immediate 'BEGIN dbms_pdb.exec_as_oracle_script(''alter type WRI$_REPT_ASH_OMX compile''); END;';
 execute immediate 'BEGIN dbms_pdb.exec_as_oracle_script(''alter type WRI$_REPT_AUTO_INDEX compile''); END;';
 execute immediate 'BEGIN dbms_pdb.exec_as_oracle_script(''alter type WRI$_REPT_ASH_OMX compile''); END;';
 execute immediate 'BEGIN dbms_pdb.exec_as_oracle_script(''alter type WRI$_REPT_AUTO_INDEX compile''); END;';
 execute immediate 'BEGIN dbms_pdb.exec_as_oracle_script(''alter package PRVT_EMX compile body''); END;';
 EXCEPTION
   WHEN err_code THEN NULL;
END;
/

方案二：直接调用存储过程（无需动态SQL） #

如果执行的语句是固定的，不需要动态生成，可直接在PL/SQL块中调用存储过程，省去EXECUTE IMMEDIATE：

DECLARE
 err_code EXCEPTION;
 PRAGMA EXCEPTION_INIT(err_code, -4043);
BEGIN
 dbms_pdb.exec_as_oracle_script('alter type WRI$_REPT_ASH_OMX compile');
 dbms_pdb.exec_as_oracle_script('alter type WRI$_REPT_AUTO_INDEX compile');
 dbms_pdb.exec_as_oracle_script('alter type WRI$_REPT_ASH_OMX compile');
 dbms_pdb.exec_as_oracle_script('alter type WRI$_REPT_AUTO_INDEX compile');
 dbms_pdb.exec_as_oracle_script('alter package PRVT_EMX compile body');
 EXCEPTION
   WHEN err_code THEN NULL;
END;
/

关键说明 #

• EXECUTE IMMEDIATE适用于需要动态生成SQL/PL/SQL语句的场景，固定语句直接调用存储过程更高效简洁。
• PL/SQL中字符串内的单引号需要用连续两个单引号（''）进行转义，确保语句被正确解析。

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