Talend Connect
Virtual Summit
JOIN US!
And visit the Customer
& Community Lounge.
May 27-28, wherever you are.

Oracle SP with OUT parameter

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

Highlighted
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.
Highlighted
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?
Highlighted
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.
Highlighted
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 GARTNER 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

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog