Get Stored Procedure return Value

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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)

[_AnonymousUser]

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

[Anonymous]

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

[_AnonymousUser]

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...

[Anonymous]

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

[_AnonymousUser]

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