BizTalk Adapter 3.0 and Oracle Stored Procedures
13-Sep-2010 Update: After reading this post read Charles’ comment – perhaps there’s just a mix-up on versions (although I haven’t verified this yet).
I was helping advise another coworker on a project where we wanted to use a WCF Oracle receive location to pull data from a database. The poll query looked something like select * from tablename where flag = ‘R’ for update and the post-poll statement did something like update tablename set flag = ‘P’. In order to avoid the possibility that additional ‘R’ records would be written after the select statement but before the update statement (which would cause the also to be updated to ‘P’ without being queried) I had chosen to use a serializable isolation level. However, there was a concern that locking the whole table, even if for a short period, might cause a problem. Instead we decided to use a stored procedure that would return the records for us and ensure that only the records returned would be updated. Sounds simple enough, right?
Well, this is where I was thoroughly disappointed with the development team that worked on the BizTalk Adapter Pack 3.0. I had found Microsoft articles, Microsoft code samples, etc. explaining how to call a stored procedure in a WCF Oracle receive location, but for some reason my coworker couldn’t get any of them to work (here’s an example of one such Microsoft page). I decided to slow down and take a look myself. I was initially confused too, until I came across this page and read “the Oracle Database adapter does not support stored procedures in polling. To address this issue, the Oracle Database adapter enables clients to specify a polling query and a post poll query.” Of course I had to laugh at this point because our reason for wanting to use a stored procedure was because the polling query and post poll statement was too limiting.
Microsoft has done something that they rarely do – they removed functionality in a newer version of the their product. Whereas previously you could call a stored procedure in a WCF Oracle receive location, you no longer can. I couldn’t believe it. Undoubtedly it has something to do with a disagreement between Microsoft and Oracle on something or rather, but it’s still very disappointing.