none
BizTalk 2010 - Oracle commit issue RRS feed

  • Question

  • Hello,

    I am aware that Biztalk does not allow Oracle Store Procedure to have commit statement in the procedure. How do I "commit" the transaction? I have use Oracle sp to copy records from one table to another. Because of the absense of commit, only some records were copied and the rest does not appear in the table. No error or warning at all from the BizTalk management console.

    Is there a way to force Oracle to flush each time a record is copied?

    Thanks,

    Thursday, July 21, 2011 5:11 PM

Answers

  • you could do two things:

     

    1. Set UseAmbientTransaction to false, and then commit from within the SP

    2. Set UseAmbientTransaction to true, and ensure that WCF Transactions are turned on. In that case the transaction will be committed once the response is received. This is the recommended approach.

    Thanks,
    Manas


    -- Please mark as answered if this answers your question.
    Friday, July 22, 2011 3:54 AM

All replies

  • At first, I thought Oracle did not flush the records but after more extensive testing, it turns out that the sp may skip records. I added a bunch of records in 1 table and it appears that BizTalk picked and chose which records to copy and sometime the records were duplicate. Not sure why this is happening.

    I have 1 schema that polls 1 record at a time, processing the record then send a message to trigger the copy using a sp (using a different schema). At the end of polling (1st schema), I had a postpollstatement to delete the original record. Everything is supposed to take place sequentially. I set the polling interval to 1s for the schema. Theoritically speaking, a record should be read, processed, copied (archived) then deleted in this order. Not really sure why the records were missing and duplicated. Something must be going on that is way beyond my limited understanding of Biztalk.

    Have anyone come across this situation or have explanation to this weird case?

    Thanks!

    Thursday, July 21, 2011 8:30 PM
  • you could do two things:

     

    1. Set UseAmbientTransaction to false, and then commit from within the SP

    2. Set UseAmbientTransaction to true, and ensure that WCF Transactions are turned on. In that case the transaction will be committed once the response is received. This is the recommended approach.

    Thanks,
    Manas


    -- Please mark as answered if this answers your question.
    Friday, July 22, 2011 3:54 AM
  • Thanks Manas. I understand your explantion regarding option 1. 

    Regarding your 2nd suggestion which is the recommended one, I am a bit fuzzy on it. How do I turn on WCF Transaction? Is it specific to the data object I am manipulating or the whole project or the whole server? I do not see it on the binding window where WCF Transaction is. Do I need to set it from the orchestration?

    I apologize for the possible obvious questions as I am very new to BizTalk. Please help.

    Thanks,


    Friday, July 22, 2011 11:44 AM