topic Re: tDBSP_1(Oracle) : Unable to retrieve data from table using Oracle Procedure in Talend Studio

tDBSP_1(Oracle) : Unable to retrieve data from table using Oracle Procedure

PranayVatsal — Fri, 15 Nov 2024 23:36:24 GMT

Hello All,

I'm trying to retrieve data from Oracle through a SP, used tOracleSP component (tDBSP).

I was able to fetch the data from the procedure "GET_ITEM_DATA" but had no luck with "GET_ITEM_DATA2"

tried many ways but no use.

below is the screenshot.

I've changed the output type to "RECORDSET" along with tParsedRecordSet, still no use.

All I get is this error:

Exception in component tDBSP_1 (API_GET_POST_ITEM_DETAILS_GET_ITEM_DATA2)

java.sql.SQLException: ORA-06550: line 1, column 7:

PLS-00306: wrong number or types of arguments in call to 'GET_ITEM_DATA2'

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

any suggestions?

Re: tDBSP_1(Oracle) : Unable to retrieve data from table using Oracle Procedure

Anonymous — Fri, 22 Oct 2021 08:49:22 GMT

Hello,

Have you tried to test it directly on PL/SQL Developer to see if it works fine?

Are the input parameters Integer or String?

If you want return results from the Oracle procedure, you should use OUTPUT parameter or you can define PL/SQL function where you can define return value of the function.

https://www.techonthenet.com/oracle/functions.php

https://www.techonthenet.com/oracle/procedures.php

Best regards

Sabrina

Re: tDBSP_1(Oracle) : Unable to retrieve data from table using Oracle Procedure

PranayVatsal — Fri, 22 Oct 2021 11:39:59 GMT

Thanks for the reply, Sabrina.

1) Yes, I've tested it in SQL Developer and it returns the output.

2) Input parameter is a String. (attached the package as well with all details).

3) Defined the output parameter and it's a table-type.

thanks,

Pranay Vatsal

Re: tDBSP_1(Oracle) : Unable to retrieve data from table using Oracle Procedure

PranayVatsal — Wed, 27 Oct 2021 11:48:27 GMT

Hi @Xiaodi Shi , Any ideas?

thanks,

Pranay Vatsal

Re: tDBSP_1(Oracle) : Unable to retrieve data from table using Oracle Procedure

Anonymous — Thu, 28 Oct 2021 03:41:46 GMT

Hello,

For retrieving result set from a stored procedure, please try to link tOracleSP to a tParseResultSet component, define a column with object type on schema of tOracleSP.

Best regards

Sabrina

Re: tDBSP_1(Oracle) : Unable to retrieve data from table using Oracle Procedure

PranayVatsal — Wed, 24 Nov 2021 19:31:54 GMT

@Xiaodi Shi : Thank you, Tried that with no luck.

It's throwing the same error.

thanks,

Pranay Vatsal

Re: tDBSP_1(Oracle) : Unable to retrieve data from table using Oracle Procedure

Anonymous — Thu, 25 Nov 2021 07:19:41 GMT

Hello,

We are supposing that it is possible that the mistake is in this parameter. Please check your parameter spelling, it has to match the Store Procedure variable name, specially, if you have an output variable.

Best regards

Sabrina