topic Re: Get Stored Procedure return Value in Talend Studio

Get Stored Procedure return Value

Anonymous — Sat, 16 Nov 2024 14:12:33 GMT

Hi,
I would like to get back the value of a Oracle Stored Procedure but I don't know how to do this.
I succeed in executing the stored procedure.
How can I do it ?
Thanks,
François

Re: Get Stored Procedure return Value

Anonymous — Sat, 04 Oct 2008 09:07:01 GMT

Hello Francois,
in tOracleSP component properties, there is an option called "isFunction" below the SP Name field. to get the value back from Oracle, you need to check "isFunction" box & select the variable in which you wanna store the return value.

Re: Get Stored Procedure return Value

Anonymous — Fri, 26 Jun 2009 13:46:21 GMT

Hi.
Other poster, related question: Once the tOracleSP component stores the return value, how can i access it later?
I need to access it from several components in my job.
Thanks

Re: Get Stored Procedure return Value

Anonymous — Mon, 29 Jun 2009 08:20:00 GMT

Hello All
Here I show a simpe example: call a function of Oracle on tOracleSP.
my sql:
SQL> create or replace function f1(in_id number) return varchar2 is
2 value person.name%type;
3 begin
4 select name into value from person where id=in_id and rownum=1;
5 return value;
6 end;
7 /
Function created.

Best regards

shong

Re: Get Stored Procedure return Value

Anonymous — Mon, 29 Jun 2009 08:34:33 GMT

Thanks a lot Shong, this helped me in finding my solution: Instead of a tLogRow I used a tSetGlobalVar (I needed the return value of the function in several components, and a direct link was impractical)

Re: Get Stored Procedure return Value

_AnonymousUser — Fri, 07 Dec 2012 07:33:03 GMT

Hi Shong,
I have to do the same in Oracle.
Is is mandatory to create function with the same name as procedure name.
Oracle does not allow two objects to have same name.
Is there any workaround for this.

Regards
Samya

Re: Get Stored Procedure return Value

Anonymous — Fri, 07 Dec 2012 13:22:12 GMT

Hi Shong,
I have to do the same in Oracle.
Is is mandatory to create function with the same name as procedure name.
Oracle does not allow two objects to have same name.
Is there any workaround for this.

Regards
Samya

No, the function name can be any string as long as it fit Oracle specification, you just need to type in the function name in the SP Name field.
Shong

Re: Get Stored Procedure return Value

_AnonymousUser — Tue, 22 Jan 2013 11:13:23 GMT

Hi,
I am just three days old to TALEND.....
I've created a procedure in db and wanna call it from talendDI.. In my procedure I m calculting the values and inserting the values in a new table....(BULK Upload)
The procedure is being executed but the values aren't coming to the table...
The process I've used are:
1. OrclConnction---On subjobOk----torclsp----tParseRecordset-----torcloutput
2. orclconnection----tfixedflowinput----torclsp----torcloutputbulk
But both the process are not workin can any1 help me out...
One more thing there are seven parameters m passing in the procedure while executing....
Thnx in advance...

Re: Get Stored Procedure return Value

Anonymous — Wed, 23 Jan 2013 02:40:28 GMT

Hi,

The procedure is being executed but the values aren't coming to the table...
The process I've used are:
1. OrclConnction---On subjobOk----torclsp----tParseRecordset-----torcloutput

You have used a tOracleConnection to create a db connection, this component is always used together with tOracleCommit, so you are required to use a tOracleCommit at the end of the job to commit the change and close the db connection.
OrclConnction
|
On subjobOk
|
torclsp----tParseRecordset-----torcloutput
|
onsubjobok
|
tOracleCommit
Shong

Re: Get Stored Procedure return Value

_AnonymousUser — Wed, 23 Jan 2013 05:12:50 GMT

Still not working....it's only throwing one row from torclsp to tparsercrdset and 0 rows to torcloutput

Re: Get Stored Procedure return Value

Anonymous — Wed, 23 Jan 2013 06:20:35 GMT

Still not working....it's only throwing one row from torclsp to tparsercrdset and 0 rows to torcloutput

hi
The problem is no rows are transformed to tOracleOutput, rather than the changes are not commit. It seems you don't use correctly the tParseRecordSet component, there is a scenario about this component in Talend Help Center, I suggest you to refer to this scenario.
https://help.talend.com/pages/viewpage.action?pageId=8122872#Raa48944
Shong

Re: Get Stored Procedure return Value

Anonymous — Mon, 13 Jan 2014 09:35:25 GMT

Hey All!
I'm new to talend and I want to run a SP that runs a process in the DB (oracle), in the end of the process a table with 20,00 rows is created.
How do I retrieve the data (table with 20,000 rows) after executing my SP? Whice component I need to use?
Tnx
lirazru

Re: Get Stored Procedure return Value

Anonymous — Tue, 11 Mar 2014 14:54:47 GMT

How can i call a procedure for mysql.
tmysqlsp1->tlogrow. (i am using only this two component). it is giving me null values.
But when i am calling this procedure from database it is giving me results. where i am wrong??
Thanks in Advance

Re: Get Stored Procedure return Value

Anonymous — Wed, 12 Mar 2014 02:58:33 GMT

Hi deba,
What your Procedure looks like? Is there any set of records from output? If so, you have to use TalendHelpCenter:tParseRecordSet to parse.
Best regards
Sabrina

Re: Get Stored Procedure return Value

Anonymous — Wed, 25 Jun 2014 15:48:25 GMT

Hello,
I have the following error message from the t_jdbcsp component :
Exception in component t_JDBCSP_1
Java.sql.SQLException : Parameter 1 cannot be registered to be of type OTHER because it maps to type DECIMAL and they are incompatible.
In the Parameter tab of the component the checkbox function is checked and the result has to be returned in the lastexcrat column of the output schema.
In the output schema the column lastexcrat has the type BigDecimal (The f_excrat function which is called in the component returns a BigDecimal value ; it works fine when called from the Netbeans IDE).
Excerpt of the code generated :

							// Start of branch "lastexcrat"
							if (lastexcrat != null) {
								/**
								 *  start
								 */
								currentComponent = "tJDBCSP_1";
								// lastexcrat
								// lastexcrat
								if (execStat) {
									runStat.updateStatOnConnection("lastexcrat"
											+ iterateId, 1, 1);
								}
								statement_tJDBCSP_1.registerOutParameter(1,
										java.sql.Types.OTHER);
								if (lastexcrat.finacc_credat == null) {
									statement_tJDBCSP_1.setNull(2,
											java.sql.Types.DATE);
								} else {

Any idea on what is wrong ?
TOS Version: 5.4.1 Build id: r111943-20131212-1133
Bet regards,
Marc.

Re: Get Stored Procedure return Value

Anonymous — Thu, 26 Jun 2014 02:53:41 GMT

Hi labm59

Java.sql.SQLException : Parameter 1 cannot be registered to be of type OTHER because it maps to type DECIMAL and they are incompatible.

It seems there is a wrong setting on t_JDBCSP_1, can you please upload some screenshots of the t_JDBCSP_1 component?
Shong

Re: Get Stored Procedure return Value

Anonymous — Thu, 26 Jun 2014 11:40:43 GMT

Hi Shong,
Don"t see how to upload images.
I have sent you 5 screenshots to your email @talend.
Let me know if you need additional info.
Marc.

Re: Get Stored Procedure return Value

Anonymous — Thu, 03 Jul 2014 12:42:21 GMT

Hi Shong,
Did you find what is wrong or missing ?
Best regards,
Marc.

Re: Get Stored Procedure return Value

Anonymous — Tue, 28 Apr 2020 15:40:06 GMT

@geetha wrote:
Hello Francois,
in tOracleSP component properties, there is an option called "isFunction" below the SP Name field. to get the value back from Oracle, you need to check "isFunction" box & select the variable in which you wanna store the return value.

Even when I have a procedure and not a function to run?