Datastage中Oracle连接器与存储过程阶段无输出记录问题排查

问题场景 #

某PL/SQL块在TOAD中可正常运行并输出结果，但在Datastage中通过Oracle连接器阶段或存储过程阶段执行时，输出文件仅显示空值。该场景调用Pkg_x包内的sp_calc存储过程，包含1个输入参数和2个输出参数，预期输出示例为：Dummy=12345、Dummy1=ab、Dummy2=ae123。

测试用PL/SQL块代码 #

Declare
    Dummy varchar2(20);
    Dummy1 varchar2(100);
    Dummy2 varchar2(200);

Begin
    Dbms_output.enable();
    Pkg_x.sp_calc(dummy,dummy1,dummy2);

    Dbms_output.putline(dummy);
    Dbms_output.putline(dummy1);
    Dbms_output.putline(dummy2);
End;

已尝试的无效操作 #

• 通过额外连接器查询传入Dummy参数，无效果
• 在Datastage存储过程阶段执行以下代码（已尝试call、exec等方式），仍输出空值：

  Begin Pkg_x.sp_calc(:1,:2,:3); End;
  

核心原因 #

1. Dbms_output不兼容：Datastage的Oracle连接器/存储过程阶段不会捕获Dbms_output的输出内容，这是TOAD能显示结果但Datastage无法获取的关键原因。
2. 参数模式未明确：存储过程参数若未指定IN/OUT/IN OUT模式，Datastage无法正确识别输出参数，导致无法获取返回值。
3. 阶段配置不匹配：存储过程阶段未正确映射输出参数到Datastage列，或参数数据类型、长度与Oracle定义不一致。

解决方案 #

方案1：明确参数模式并正确映射 #

1. 确认存储过程sp_calc的参数定义，示例如下：

   PROCEDURE sp_calc(
       p_dummy IN VARCHAR2,
       p_dummy1 OUT VARCHAR2,
       p_dummy2 OUT VARCHAR2
   );
   

2. 在Datastage存储过程阶段使用带参数别名的调用语句：

   BEGIN
       Pkg_x.sp_calc(:IN_DUMMY, :OUT_DUMMY1, :OUT_DUMMY2);
   END;
   

3. 在Datastage阶段配置中：
   • 将输入参数IN_DUMMY映射至对应输入列
   • 创建OUT_DUMMY1、OUT_DUMMY2两个输出列，分别映射到存储过程的两个输出参数
   • 确保参数数据类型、长度与Oracle存储过程定义完全一致

方案2：使用REF CURSOR返回结果（推荐多输出场景） #

若可修改存储过程，改为通过REF CURSOR返回结果集，更适配Datastage的结果捕获逻辑：

1. 修改存储过程，添加REF CURSOR输出参数：

   CREATE OR REPLACE PACKAGE Pkg_x AS
       TYPE output_cursor IS REF CURSOR;
       PROCEDURE sp_calc(
           p_dummy IN VARCHAR2,
           p_result OUT output_cursor
       );
   END Pkg_x;
   
   CREATE OR REPLACE PACKAGE BODY Pkg_x AS
       PROCEDURE sp_calc(
           p_dummy IN VARCHAR2,
           p_result OUT output_cursor
       ) IS
           v_dummy1 VARCHAR2(100);
           v_dummy2 VARCHAR2(200);
       BEGIN
           -- 原业务逻辑计算v_dummy1、v_dummy2
           OPEN p_result FOR
               SELECT v_dummy1 AS dummy1, v_dummy2 AS dummy2 FROM DUAL;
       END sp_calc;
   END Pkg_x;
   

2. 在Datastage的Oracle连接器阶段，执行如下查询调用存储过程：

   SELECT dummy1, dummy2 FROM TABLE(Pkg_x.sp_calc(:IN_DUMMY));
   

   并在连接器中配置对应结果列。

方案3：临时表中转结果（无法修改存储过程时使用） #

1. 创建全局临时表：

   CREATE GLOBAL TEMPORARY TABLE temp_sp_result(
       dummy1 VARCHAR2(100),
       dummy2 VARCHAR2(200)
   ) ON COMMIT PRESERVE ROWS;
   

2. 编写封装PL/SQL块，调用存储过程并将结果插入临时表：

   DECLARE
       v_dummy VARCHAR2(20) := :IN_DUMMY;
       v_dummy1 VARCHAR2(100);
       v_dummy2 VARCHAR2(200);
   BEGIN
       Pkg_x.sp_calc(v_dummy, v_dummy1, v_dummy2);
       INSERT INTO temp_sp_result(dummy1, dummy2) VALUES(v_dummy1, v_dummy2);
   END;
   

3. 在Datastage中先执行上述PL/SQL块，再通过另一个Oracle连接器查询临时表获取结果：

   SELECT dummy1, dummy2 FROM temp_sp_result;
   

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