Hi,
I have delimited file, mapped via tMap to 4 different Oracle output tables. If there is an error record in any of the records, I need the entire transaction to be rolled back. And I also want all the records that were rejected to be in the Oracle File.
Here's what I have so far:
tOracleConnection-->onSubjobOK-->tFileInputDelimited-->tMap-->OracleOutput1-->??????
If I have 5 records, and 2 were rejected, while 3 were good, how do I rollback the entire thing?
Records can be rejected for variety of reasons: formatting issues, or b/c they already exist in the database...
I put tRollback after tOracleOutput, but not sure how to specify a condition.
Thanks,
Boris
Hi,
Here's a suggestion of what you could do:
1. Wire the Rejects link from each of the tOracleOutput to the OracleFile.
2. Have a PostJob flow as the following:
tPostJob ---onComponentOk--> tJava ----- if (number of lines in OracleFile > 0) ----> tOracleRollBack
----- if (number of lines in OracleFile <= 0) -----> tCommit
Note: You can get the number of lines in OracleFile through the following: ((Integer)globalMap.get("tFileOutputDelimited_1_NB_LINE")).intValue()
Hope this helps!
Regards,
Serpico
I tried without tJava by doing the following: tOracleOutput-->Rejects-->tFileOutputDelimited-->runIf-->tOracleRollback. in runIf I specified a condition: ((Integer)globalMap.get("tFileOutputDelimited_1_NB_LINE")) > 0 When I run it, it checks runif, marks it as True, but does not rollback the other records, that were inserted.
Serpico,
That was it!!! use existing connection box had to be checked.
Thanks for figuring this out.
I will go and try to put all of this in PostJob and Prejob.
Thanks again, I really appreciate this.
