OSB - Rollback transactions in ForEach in case of error

Problem: In a ForEach, there is a service callout to a stored procedure which inserts/updates a row. If during that ForEach, there is an error when inserting/updating one of the rows, the whole transaction has to be rolled back.

Solution: In OSB 12c, go to the SB Console and go to the Pipeline configuration which has the ForEach activity. In Message Handling, under Transaction, select Transaction Required. This will cause for all of the inserts/updates to be rolled back if there is an error during one of them. Also, in our stored procedure, the exception is not handled and is instead re-thrown so that way OSB will catch it.

As a request, I send in 5 IDs. The Stored Procedure is programmed so that on the 5th ID, it should throw an error.

Nothing is inserted in the table after the error.

In our case, the Service Callout is in the response pipeline. Anything that is inserted in the request pipeline will be commited, and so the response pipeline has a different transaction and won't affect the transaction in the request pipeline.