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,
TM
