Datastage Oracle connector and stored procedure stage not outputting records

Question

I have the below query running fine in TOAD and gives me an output. However when I run it in Datastage using Oracle connector stage pl sql block or a stored procedure stage, it outputs nulls in output file.

I have 1 input and 2 output parameters. This one is running a procedure from a package. My output values should be like Dummy =12345, Dummy1= ab, Dummy2= ae123.

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;

I tried inputting dummy from another connector via query. It does jot work. I tried putting below query in a stored procedure stage in datastage and it gives out nulls. Used call, exec etc as well.

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

Answer 1

You might want to look into oracle "Table Functions" or even "Pipelined Table Functions" which can be selected like normal data tables and thus return rows of columns which the oracle connector can map to an output link.