topic Re: How to Run a Oracle Stored Procedure in Talend in Talend Studio

How to Run a Oracle Stored Procedure in Talend

_AnonymousUser — Sat, 16 Nov 2024 12:40:02 GMT

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

Re: How to Run a Oracle Stored Procedure in Talend

Anonymous — Fri, 23 Sep 2011 06:33:44 GMT

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

Re: How to Run a Oracle Stored Procedure in Talend

_AnonymousUser — Mon, 03 Oct 2011 13:46:51 GMT

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

Re: How to Run a Oracle Stored Procedure in Talend

_AnonymousUser — Thu, 20 Oct 2011 09:42:46 GMT

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

Re: How to Run a Oracle Stored Procedure in Talend

Anonymous — Sun, 04 Dec 2011 20:56:20 GMT

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

Re: How to Run a Oracle Stored Procedure in Talend

Anonymous — Thu, 20 Jun 2013 15:13:19 GMT

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

Anonymous — Thu, 20 Jun 2013 15:17:06 GMT

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

Re: How to Run a Oracle Stored Procedure in Talend

Anonymous — Thu, 20 Jun 2013 16:31:41 GMT

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!

Re: How to Run a Oracle Stored Procedure in Talend

Anonymous — Fri, 21 Jun 2013 04:53:47 GMT

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

Re: How to Run a Oracle Stored Procedure in Talend

Anonymous — Fri, 21 Jun 2013 15:00:39 GMT

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

Anonymous — Mon, 24 Jun 2013 13:52:41 GMT

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

Re: How to Run a Oracle Stored Procedure in Talend

Anonymous — Fri, 28 Jun 2013 10:01:05 GMT

Hi javix
Using oncomponentOK instead of OnsubjobOK, link it from tHashOutput_2 to tHashInput_1. Please see my screenshot.
Shong

Re: How to Run a Oracle Stored Procedure in Talend

_AnonymousUser — Fri, 19 Jul 2013 14:58:00 GMT

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

Re: How to Run a Oracle Stored Procedure in Talend

_AnonymousUser — Tue, 16 Jun 2015 14:40:21 GMT

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

Re: How to Run a Oracle Stored Procedure in Talend

Anonymous — Wed, 17 Jun 2015 05:10:13 GMT

Hi Ayan,
For your requirment, could you please refer to KB article about: TalendHelpCenter:Calling a stored procedure or function.
Best regards
Sabrina

Re: How to Run a Oracle Stored Procedure in Talend

YPMAL — Wed, 16 Nov 2016 13:05:00 GMT

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

Re: How to Run a Oracle Stored Procedure in Talend

Anonymous — Thu, 17 Nov 2016 07:19:27 GMT

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