Calling an Oracle stored procedure to insert multiple rows into multiple tables

Anonymous — Sat, 16 Nov 2024 06:13:18 GMT

My requirement is to design a job that would call an Oracle stored procedure (tOracleSP) to insert rows into multiple tables. Details are:

1. Business identifies some new items, so they create a csv file which has the basic information for new items. Let's say they create 3 new items on the file.

2. My Talend job is to read this csv file and then call a stored procedure "Create_New_Item" to insert new rows for many tables: UPC_ID_Xref, New_Product, UPC_ID_Desc, etc.

The link I found TalendHelpCenter:Calling a stored procedure or function
is not much helpful, because all of the given examples only have one row going to the tOracleSP component. So my questions is:

1. If I read in new items info from csv file and call the SP, I have to read the new items one by one

and call the SP for each one: tFlowToIterate --iterate--> tFixedFlowInput -- iterate--> tOracleSP, correct?

2. If I use a database staging table to store the new items, and read new items from the staging table and call the SP, should I use "Main" or "Iterate"?

3. If there is error on the csv file for the 2nd item, how the tOracleSP will behave? Will the 1st new item be created into the tables? I think knowing this will be very important for designing for debugging and rerunning.

Thanks,

Re: Calling an Oracle stored procedure to insert multiple rows into multiple tables

Anonymous — Mon, 01 Apr 2019 07:05:01 GMT

Hi,

One of the important aspect of the evolution of ETL is to move away from complex codes within scripts of stored procedures as it might be difficult to traverse in due course. So why don't you replicate the logic within Stored Proc using a series of normal tDBOutput components in a controlled fashion?

That will be the most optimal way as you can still send the data to underlying table as a block of records instead of one at a time.

If you still want to go by Stored Proc route, could you please check below link? But my personal preference is to do the same logic within ETL itself.

https://community.talend.com/t5/Design-and-Development/resolved-Using-Stored-Procedures-in-Talend/td-p/109276

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

topic Re: Calling an Oracle stored procedure to insert multiple rows into multiple tables in Talend Studio

Calling an Oracle stored procedure to insert multiple rows into multiple tables

Re: Calling an Oracle stored procedure to insert multiple rows into multiple tables