Oracle SP with OUT parameter

Eight Stars

Oracle SP with OUT parameter

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?

Moderator

Re: Oracle SP with OUT parameter

Hi,
Have you already checked document about:TalendHelpCenter:Calling a stored procedure or function?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Eight Stars

Re: Oracle SP with OUT parameter

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?
Moderator

Re: Oracle SP with OUT parameter

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Eight Stars

Re: Oracle SP with OUT parameter

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

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog