解决datastage调用oracle存储过程时存储过程抛出异常而作业不中止的问题

创建测试表及插入测试数据

create table TEST_20190523(
COL1 varchar(20) not null,
primary key(COL1)
);
insert into TEST_20190523 values('A')
insert into TEST_20190523 values('B')

创建测试存储过程

create or replace PROCEDURE TEST_2019 
(
  PARAM1 IN VARCHAR2 
) AS 
   no_result exception;
   str varchar(1000);
   result_num integer;
BEGIN
  str := 'select count(1) from TEST_20190523 where COL1=:c';
  execute immediate str into result_num using PARAM1;
  dbms_output.put_line(result_num);
  if result_num=1 then
    str := 'delete from TEST_20190523 where COL1=:c';
    execute immediate str using PARAM1;
  else
    raise no_result;
  end if;
  EXCEPTION
  WHEN no_result then
    DBMS_OUTPUT.PUT_LINE('数据删除失败条件不满足!');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('发生异常!');
END TEST_2019;

创建datastage作业

在before sql中调用存储过程

运行作业

存储过程调用语句是 call TEST_2019('D'),往测试表插数据时，只插入了A，B两条数据，此时希望数据删除失败，datasatge作业直接报错。

修改存储过程代码

create or replace PROCEDURE TEST_2019 
(
  PARAM1 IN VARCHAR2 
) AS 
   no_result exception;
   str varchar(1000);
   result_num integer;
BEGIN
  str := 'select count(1) from TEST_20190523 where COL1=:c';
  execute immediate str into result_num using PARAM1;
  dbms_output.put_line(result_num);
  if result_num=1 then
    str := 'delete from TEST_20190523 where COL1=:c';
    execute immediate str using PARAM1;
  else
    raise no_result;
  end if;
  EXCEPTION
  WHEN no_result then
    --使用RAISE_APPLICATION_ERROR将应用程序专有的错误从服务器端转达到客户端应用程序
    RAISE_APPLICATION_ERROR(-20001,'数据删除失败条件不满足!');
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20999,'发生异常!');
END TEST_2019;

重新运行ds作业，作业报错，日志如下

Oracle_Connector_7: The OCI function executeDirect returned status -1. Error code: 20001, Error message: ORA-20001: 数据删除失败条件不满足!
ORA-06512: at "SCOTT.TEST_2019", line 20. (CC_OraUtils::handleBeforeAfterSQL, file CC_OraUtils.cpp, line 5,322)

如有疑问，可加微信一起交流