Home > BizTalk Server > BizTalk Adapter 3.0 and Oracle Stored Procedures

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.

Categories: BizTalk Server
  1. Shona
    September 10, 2010 at 6:02 pm

    Hi, I’m currently working on a project where we execute a function in the poll statement to do what you want to do and it works.

    • September 13, 2010 at 8:45 am

      Can you verify what version you are using Shona? Based on Charles’ reply perhaps I’m just mixed up on versions.

  2. September 13, 2010 at 2:03 am

    Not sure I understand this. The link you provide refers to the limitations of the Oracle adapter in the first release of the BizTalk Adapter Pack (for some reason, the adapter was designated ‘version 3.0’ in that release). This affected BTS 2006 R2 development. Since then, Microsoft has released Biztalk Adapter Pack 2.0 (the current release) with a far more functional and complete version of the adapter, including support for calling SPs during polling. The link to the example is for this current version.

    Which version of the adapter pack are you using? I wonder if perhaps you’ve fallen victim to Microsoft’s very confusing versioning strategy. I guess the current Oracle adapter, in BizTalk Adapter pack 2.0, is version 4, but it seems to have lost its version specifier.

    • September 13, 2010 at 8:44 am

      You might be right Charles. I might just be confused by the versioning. I guess I’ll have to take a closer look. Thanks.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: