Five Stars

Talend Open Studio : Stored Procedure Returning null

Hi All,

I am trying to implement a solution as below:

tJava --> tMSSQLRow (Which calls "exec <Stored Procedure> <input parameter>") --> tParseRecordSet --> tJavaFlex

The same SP works fine on Management Studio, as well as, Talend Sandbox; and returns the data.

But, somehow, it's not working in Talend Open Studio. And it's returning null.

Could someone provide some solution to that?

Thanks,

Mitul Vyas

  • Big Data
1 ACCEPTED SOLUTION

Accepted Solutions
Eleven Stars TRF
Eleven Stars

Re: Talend Open Studio : Stored Procedure Returning null

Hi,

Why do you use tMSSQLRow instead of tMSSqlSP to call the procedure?

Based on your job, the design should be:

tJava --> tMSSqlSP --> tParseRecordSet --> tJavaFlex

tMSSqlSP schema must have a column of datatype "Object".

Why does it works using Talend Big Data Sandbox? That is the question.


TRF
3 REPLIES
Eleven Stars TRF
Eleven Stars

Re: Talend Open Studio : Stored Procedure Returning null

Hi,

Why do you use tMSSQLRow instead of tMSSqlSP to call the procedure?

Based on your job, the design should be:

tJava --> tMSSqlSP --> tParseRecordSet --> tJavaFlex

tMSSqlSP schema must have a column of datatype "Object".

Why does it works using Talend Big Data Sandbox? That is the question.


TRF
Five Stars

Re: Talend Open Studio : Stored Procedure Returning null

Hi.

 

Sorry for responding very late.

 

Thanks TRF for response.

 

That worked in both the ways (using MSSQLRow as well as MSSQLSP components).

 

Regards,

Mitul Vyas

Two Stars

Re: Talend Open Studio : Stored Procedure Returning null

Hi,

 

I have similar scenario. I have a function which returns multiple records.How do we need to read these records from function output in Talend?

Which components we have to use? and what is the code required?


create type prcs_vars_obj is object (var varchar2(10), value varchar2(10));
create type prcs_vars_tab is table of prcs_vars_obj;

create or replace FUNCTION startJob_getPrcsVars_obj (
  ,p_RunDate          NUMBER)
RETURN prcs_vars_tab
IS  
    v_prcs_vars prcs_vars_tab := prcs_vars_tab();
    i integer := 0;
BEGIN  
for r in (
SELECT
   VAR
  ,to_char((((TO_NUMBER(TO_CHAR(VALUE, 'YYYY')) * 100) + (TO_NUMBER(TO_CHAR(VALUE, 'MM')))) * 100) + TO_NUMBER(TO_CHAR(VALUE, 'DD'))) AS VALUE
 FROM (
  SELECT
    CONCAT('DT-',  VAL)  AS VAR,
    to_date(to_char(p_RunDate), 'YYYYMMDD')+ (VAL*-1) AS VALUE
   FROM (
    SELECT 0 VAL FROM dual UNION ALL
    SELECT 1 VAL FROM dual UNION ALL
    SELECT 2 VAL FROM dual UNION ALL
    SELECT 3 VAL FROM dual UNION ALL
    SELECT 4 VAL FROM dual UNION ALL
    SELECT 5 VAL FROM dual UNION ALL
    SELECT 6 VAL FROM dual UNION ALL
    SELECT 7 VAL FROM dual
  ) A
) A
)
loop
    v_prcs_vars.extend;
    i := i+1;
    v_prcs_vars(i) := prcs_vars_obj(r.var, r.value);
end loop;
return v_prcs_vars;
END;