topic Re: Oracle SP with OUT parameter in Talend Studio

Oracle SP with OUT parameter

Anonymous — Mon, 07 Mar 2016 15:41:21 GMT

Hi,
I have to invoke a SP written on Oracle that takes 1 IN parameter and 1 OUT parameter.
The IN parameter is of type int and simple to configure.
The OUT parameter however is a table of number. Is there a way to configure this OUT parameter using tOracleSP component?

Re: Oracle SP with OUT parameter

Anonymous — Tue, 08 Mar 2016 06:57:50 GMT

Hi,
Have you already checked document about: TalendHelpCenter:Calling a stored procedure or function?
Best regards
Sabrina

Re: Oracle SP with OUT parameter

Anonymous — Tue, 08 Mar 2016 10:25:59 GMT

Hi,
I had already checked the link you shared. I also created a job as detailed in the link as follows:

Here the tFixedFlowInput component's schema has 1 int field called as p_In which will be the IN parameter for the stored procedure.
The tOracleSP component is configured as follows:

And the schema is defined as follows:

Now when I run the job, I get the following error in the Talend console:
Exception in component tOracleSP_1
java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SP_PROCESS_SPSSTOGARNET2'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

If the Type of the p_Out parameter in the schema is set to Object or byte[] the error is given as java.sql.SQLException: Invalid column type.

Any idea how to resolve this error?

Re: Oracle SP with OUT parameter

Anonymous — Wed, 09 Mar 2016 10:04:34 GMT

Hi,
Can you successfully call your stores procedure 'SP_PROCESS_SPSSTOGARNET2' in oracle DB?
Could you please show us your stores procedure?

Best regards
Sabrina

Re: Oracle SP with OUT parameter

Anonymous — Wed, 09 Mar 2016 12:31:42 GMT

xdshi wrote:
Hi,
Can you successfully call your stores procedure 'SP_PROCESS_SPSSTOGARNET2' in oracle DB?
Could you please show us your stores procedure?

Best regards
Sabrina

Yes, I can successfully call the stored proc in Oracle client.
The stored procedure is actually very big to include here.
This is how the stored procedure is declared:

CREATE OR REPLACE PACKAGE "RDFM_PROD"."TABLETSPSSTOGARNETPACK" 
  IS
   type numTbl is table of number
      index by binary_integer;
   Procedure SP_PROCESS_SPSStoGARNET2(
    p_In        IN number,
    p_Out       OUT numTbl);
END;

And this is how I executed the stored procedure from Oracle client

DECLARE
	P_IN NUMBER := 1;
	P_OUT RDFM_PROD.TABLETSPSSTOGARNETPACK.NUMTBL;
BEGIN
	RDFM_PROD.TABLETSPSSTOGARNETPACK.SP_PROCESS_SPSSTOGARNET2(P_IN, P_OUT);
	DBMS_OUTPUT.PUT('P_IN: ');
	DBMS_OUTPUT.PUT_LINE(P_IN);
	DBMS_OUTPUT.PUT('P_OUT: ');
	DBMS_OUTPUT.PUT_LINE(P_OUT(1));
END;
GO