How to Run a Oracle Stored Procedure in Talend

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.

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

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.

Re: How to Run a Oracle Stored Procedure in Talend

Hi Shong,Thanks a lot for your helpIn 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!

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=5671105tHashInput 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

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

Re: How to Run a Oracle Stored Procedure in Talend

DECLAREid number:=10;name VARCHAR2(20);BEGINPUT_LINE(); ---this is my stored procedure containing one DBMS_OUT.put_line('xyz') onlyNAME:=GET_NEXT_LINE(); ---this is function i have used to get values from bufferDBMSOUTPUT(id,name); --again procedure call i am using it to store return value in my tableEND;

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 On command in talend before executing my block of code

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 regardsSabrina

--Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.