One Star

How to Run a Oracle Stored Procedure in Talend

Hi ,
I have created a stored procedure in oracle which has two parameters.Normally when we run in oracle it will ask the parameter values and when we enter the values it will generate the data in corresponding table with respective to the parameter values.
Same way i want to run the Oracle Stored procedure in Talend by passing two parameters.
Please Suggest me which transformations should i use.Some one explain me step by step as am very new to Talend.
Please help me out as soon as possible.

Thanks,
Praveen Kumar
16 REPLIES
Community Manager

Re: How to Run a Oracle Stored Procedure in Talend

Hi
Using a input component to pass the parameters to SP if it requires. For example:
tFixedFlowInput--main--tOracleSP
on tFixedFlowInput, define the schema and set the value.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: How to Run a Oracle Stored Procedure in Talend

Hi SHong,
Can u Please explain this with screenshots...am trying this for so long time.
Please help me out..

Thanks in Advance.
Regards,
Praveen
One Star

Re: How to Run a Oracle Stored Procedure in Talend

I am waiting for Reply..SHong can u help me out??
Pleaseeeeeeeeee
Six Stars

Re: How to Run a Oracle Stored Procedure in Talend

Shong's description schould be enough:
tOracleConnection - will create connection
--onSubjobOK--
tFixedFlowInput - will initialize variable for stored procedure
--main--
tOracleSP - execute oracle stored procedure
Using these 3 components you are able to call oracle stored procedure.
If you are not able to get it working, you schould first go trough initial tutorials and help...
One Star

Re: How to Run a Oracle Stored Procedure in Talend

Hi,
I was able to execute an stored procedure, but when I try to execute two or more I had problems.
I want to fill an tXMLMAP, the data is from database (I had to call some store procedures to fill this component).
any idea?
thanks a lot.
Community Manager

Re: How to Run a Oracle Stored Procedure in Talend

Hi javix
You can use several tOracleSP components to call each stored procedure, and tXMLMap component allows multiple input component. For example:
tOracleSP--main--tXMLMap--main---tLogRow
|
lookup
|
tOracleSP
I don't understand your problem well, please explain it a bit more.Smiley Wink
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: How to Run a Oracle Stored Procedure in Talend

Hi Shong,
Thanks a lot for your help
In my Job, I had a tXMLMap as source, this component has the parameters to execute two stored procedures, I was trying to take the stored procedures results to fill the Final XMLMap.
My Job is like the following.
tXMLMap ----> send Parameters-----> tMSSqlSP-- > tParseRecordSet --> tLogRow --- > Final tXMLMap
| |
send Parameters--> tMSSqlSP --> tParseRecordSet --> tLogRow------------------------------- >

I was reading about tHashInput, but is it a component of the Enterprise version or may I use it in the open source talend?
thanks!
Community Manager

Re: How to Run a Oracle Stored Procedure in Talend

Hi
You are not allowed to create a cycle flow in a job, you can find more explanation and solutions in this KB article:
https://help.talend.com/pages/viewpage.action?pageId=5671105
tHashInput component is available both in Talend community version and enterprise subscription version, if you don't see it in the palette, open the Project Settings windows, then Designer, Palette settings and active it.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: How to Run a Oracle Stored Procedure in Talend

Thanks a lot, I was able to execute two or more stored procedures with tHashOutput.
But I have a little problem with that.
I create a web service, that web service was assigned to this job, so it has tEsbProviderrequest and tESBProviderResponse. When I tried to connect the tESBProviderRequest to tHashInput1 as onSubjobOk it doesn't work.
Should I to try another way to connect tEsbProviderRequest with that subJob? or how can I call that another proccess in tHashInput_1 and tHashInput_2. (both are linking with tHashoutput_1 and tHashoutput_2 in properties)
I attached image.
https://docs.google.com/file/d/0B8l9JreYNCjAQ1p4M245SmFkdms/edit?usp=sharing
One Star

Re: How to Run a Oracle Stored Procedure in Talend

Maybe I have to move the last subject about tEsbProvider and subjobs to another post.
Thanks a lot for your help!
Community Manager

Re: How to Run a Oracle Stored Procedure in Talend

Hi javix
Using oncomponentOK instead of OnsubjobOK, link it from tHashOutput_2 to tHashInput_1. Please see my screenshot.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: How to Run a Oracle Stored Procedure in Talend

Hey SHNG,
I had a looked at your website and saw that you do a great contribution to open source and Talend.
One Star

Re: How to Run a Oracle Stored Procedure in Talend

I have a question on stored proc: How you guys are creating the stored proc in Talend Studio? Appreciate your help in advance.
Thanks,
Ayan
Moderator

Re: How to Run a Oracle Stored Procedure in Talend

Hi Ayan,
For your requirment, could you please refer to KB article 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.
Six Stars

Re: How to Run a Oracle Stored Procedure in Talend

DECLARE
id number:=10;
name VARCHAR2(20);
BEGIN
PUT_LINE(); ---this is my stored procedure containing one DBMS_OUT.put_line('xyz') only
NAME:=GET_NEXT_LINE(); ---this is function i have used to get values from buffer
DBMSOUTPUT(id,name); --again procedure call i am using it to store return value in my table
END;

so this is my complete block of code..
please tell me which component i can use to write it in talend..
also please tell where can i give (SET SERVEROUTPUT OnSmiley Wink command in talend before executing my block of code
Moderator

Re: How to Run a Oracle Stored Procedure in Talend

Hi yogeshmalekar,
What's the DB you are using?
 Have you already checked talend tXXXSP component which is used to offers a convenient way to centralize multiple or complex queries in a database and call them easily to see if it works?
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.