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

痛定思痛。 2022-01-26 23:45 207阅读 0赞

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

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作业

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70][]

在before sql中调用存储过程

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 1][]

运行作业

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 2][]

存储过程调用语句是 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作业，作业报错，日志如下

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 3][]

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)

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 4][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70]: /images/20220127/ad02d84a14c149eea6c0d71fa9cb10b3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 1]: /images/20220127/37ac8c97a49f4a6aa0f2d6bd5b33b5eb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 2]: /images/20220127/ad647db8d0854d5298bce159edd1fc05.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 3]: /images/20220127/c9a253f3b3ce4fd19d7819f2d211aa33.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 4]: /images/20220127/80a2dac724fa449f939ce8cfa91dbfbc.png