So the documentation that comes with the BizTalk Adapter Pack makes scant reference to flowing transactions to the adapters. That is, if I want to call the “Insert” operation on an “Orders” table, but only commit that if the “Insert” operation on the “Order Items” table succeeds, how do I wrap those operations in a single transaction?
WCF has great transaction support, and the BizTalk Adapter Pack is built on WCF, but the product documentation for the Oracle adapter states:
The Oracle Database adapter does not support performing transactions on the Oracle database using System.Transaction. The adapter supports transactions using OracleTransaction.
Hmmm. That’s pretty much the only time transactions are mentioned at all. That makes it sound like I cannot wrap my service calls in a System.Transaction and have to use the OracleTransaction object from the ODP.NET bits. What better way to confirm this than by actually testing it?
I’m using the example from my TopXML.com articles. So in that article, I mention inserting into two tables sequentially via proxy classes. So, what happens if I take that same block of “insert” code and purposely create an error in the second set of data (e.g. use a non-existent “OrderID”)? An exception occurred during the second operation, but the first insert command succeeded …
Notice that my “Orders” table has a record in it, but the “OrderItems” table has no corresponding items for OrderID #34. So, I’m stuck in an inconsistent state. Not good.
On a whim, I decided to wrap the entire block of “insert” code inside a System.Transaction.TransactionScope block to see what would happen. On the first execution, I got an error saying “Unable to Load OraMTS“. Interesting. Looked like the System.Transaction in my code is converted to an Oracle transaction by the adapter and the OraMTS object (from the Oracle client) wasn’t found. So, I went back to my Oracle client installation and made sure to install the Oracle Services for Microsoft Transaction Server.
Now, if I executed my code again, with the same error in the 2nd set of insert commands, the database remained in a consistent state, and the first insert did not commit. So you CAN wrap these service invocations inside a System.Transacton object (at least for the Oracle adapter) to daisy-chain atomic operations.
Overall, the documentation for the BizTalk Adapter Pack is top notch, but the complete absence of transaction instructions seems curious.
Richard Seroter's Architecture Musings 
Thanks Richard, this makes a lot of sense.
I noticed the adapter supports nested record types passed as parameters to Oracle functions and procedure, could that be used as a different method?
I installed the Oracle Services for Microsoft Transaction Server and the same error “Unable to Load OraMTS“.
Is there any thing a must do to solve this error