topic Re: Talend Open Studio : Stored Procedure Returning null in Talend Studio

Talend Open Studio : Stored Procedure Returning null

mitulvyas — Sat, 16 Nov 2024 09:45:36 GMT

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

Re: Talend Open Studio : Stored Procedure Returning null

TRF — Sun, 21 May 2017 16:30:23 GMT

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.

Re: Talend Open Studio : Stored Procedure Returning null

mitulvyas — Wed, 21 Jun 2017 06:42:27 GMT

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

Re: Talend Open Studio : Stored Procedure Returning null

Anonymous — Wed, 12 Jul 2017 10:28:06 GMT

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;

tOracle_SP_scenario.jpg

Re: Talend Open Studio : Stored Procedure Returning null

archienesss — Thu, 07 May 2020 18:42:34 GMT

@TRF wrote:

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.

Hello TRF,

would it be possible for you to send a sample job? I've tried your solution from How to call a procedure from MS sql server? but to no avail. I'm particularly interested what to do with tJava and tJavaFlex components from your solution. Thanks in advance