One Star

Get Stored Procedure return Value

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
17 REPLIES
One Star

Re: Get Stored Procedure return Value

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

Re: Get Stored Procedure return Value

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
Community Manager

Re: Get Stored Procedure return Value

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Get Stored Procedure return Value

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)
One Star

Re: Get Stored Procedure return Value

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
Community Manager

Re: Get Stored Procedure return Value

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Get Stored Procedure return Value

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

Re: Get Stored Procedure return Value

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Get Stored Procedure return Value

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

Re: Get Stored Procedure return Value

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Get Stored Procedure return Value

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
One Star

Re: Get Stored Procedure return Value

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
Moderator

Re: Get Stored Procedure return Value

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

Re: Get Stored Procedure return Value

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

Re: Get Stored Procedure return Value

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Get Stored Procedure return Value

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

Re: Get Stored Procedure return Value

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