通过SQL实现数据校验触发导入作业失败的问题咨询

核心原因 #

你遇到的TOAD和PUTTY批量执行效果差异，是PUTTY环境中常用的Oracle脚本执行工具SQL*Plus的默认错误处理规则导致的：PL/SQL块内部抛出的未捕获异常，默认不会让SQL*Plus返回非零的退出码，也不会终止脚本执行，所以你的作业调度工具会判定任务执行成功，返回COMPLETE状态。

修复步骤 #

• 第一步：在脚本开头添加SQL*Plus错误处理配置，开启报错即退出的逻辑

  -- 放在脚本最开头，PL/SQL块之前
  WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;
  WHENEVER OSERROR EXIT FAILURE ROLLBACK;
  SET SERVEROUTPUT ON;
  

• 第二步：保留Oracle原生兼容的异常抛出语句，删除不兼容的语法
  你注释的语句中raiseerror是SQL Server专属语法、THROW仅Oracle 12c以上版本支持，最稳妥的写法是用raise_application_error，取消该语句的注释即可。
• 第三步：优化冗余查询逻辑，你原有子查询中select distinct loc和group by loc功能重复，group by返回的loc本身就是唯一值，不需要额外加distinct。
• 第四步：确保EXIT语句放在PL/SQL块外部，不要写到块内部。

修改后完整可运行脚本 #

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
SET SERVEROUTPUT ON;

Declare 
  valid_loc NUMBER;
  Inv_check NUMBER;
BEGIN
  select param_value into Inv_check 
  from scpomgr.udt_systemparam 
  where param_name = 'INV_CHECK';

  select count (*) into valid_loc 
  from (
    select loc
    from scpomgr.inventory
    where loc in ('GB01', 'FR01', 'DE01', 'IT01', 'ES01', 'IE01', 'CN01', 'JP01', 'AU01', 'US01')
    group by loc
    having count (*) > Inv_check
  );

  if valid_loc < 10 THEN 
    raise_application_error(-20001,'Likely Missing Inv Records');
  END IF;
END;
/
EXIT

额外校验点 #

如果修改后还是不触发失败，可以在PUTTY中手动执行脚本后，执行echo $?查看返回码：如果返回值不是0说明脚本本身已经可以抛出错误，问题出在你的作业调度工具的退出码识别规则上，需要配置调度工具识别非零返回码为任务失败即可。

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