none
ORA-02074: cannot ROLLBACK in a distributed transaction RRS feed

  • Question

  • I am calling a Oracle SP using WCF-Custom adapter and receiving following error:
    ORA-02074: cannot ROLLBACK in a distributed transaction

    Let me know if there is any solution to this. i can't modify code on Oracle side as it is 3rd party SP.
    I just call it supplying 1 parameter. SP logic is invisible to me.

    Monday, September 15, 2008 6:34 PM

Answers

  • I am assuming that in this case your message gets suspended and you instead want to continue processing, right?

    If so, you can probably look at using the "Enable Routing for Failed Messages" feature in Biztalk.

     

    In short, this feature allows you to handle messaging failures (in your case, the adapter throwing an exception) in an automated fashion instead of having the messages suspended. You can subscribe to the 'Failed Messages' from an orchestration (or send port) based on a set of context properties, just like you'd do for any other message and do the neccesary error-handling, if any.

     

    Please look at the following links for details:-

     

    http://technet.microsoft.com/en-us/library/aa578516.aspx

    http://msdn.microsoft.com/en-us/library/aa578574.aspx

    Tuesday, November 11, 2008 8:21 AM

All replies

  •  ankur_rathi wrote:

    Oracle version = 10g

    biztalk version = 2006 R2

    Monday, September 15, 2008 6:39 PM
  • Is the value of the EnableBizTalkCompatibilityMode = true? What is your scenario, are you just invoking a single SP from BizTalk?

     

    Thursday, September 18, 2008 12:39 PM
  • Sorry for late reply, Yes. The EnableBizTalkCompatibilityMode = True.
    I am calling Oracle SP from WCF-Custom adapter generated schema and passing I/P parameters to it and expecting O/P parameters.
    There's an issue there:
    I always get only the first record (from a set of records) sent to the Oracle SP and thus it won't loop through the execution of SP for each set of record supplied to it, I beleive it's problem with Oracle adpaer only, because the same scenario works perfectly with SQL Adapter.
    Let me know if somebody knows resolution for this. Or if I am missing something? 
    Tuesday, October 14, 2008 8:53 PM
  • This is different bacuse this relates also to the error which I get by calling the SP as mentioned in Post. The other thing specified is seperate issue, therefore i started a fresh thread for it.
    Wednesday, October 15, 2008 2:51 PM
  • Does your SP have an explicit ROLLBACK statement in it?

     

    From my experience, using ODP.NET, whenever an SP in oracle that has an explicit COMMIT or ROLLBACK is invoked, the error "cannot ROLLBACK/COMMIT in a distributed transaction" occurs. You can try this out with a simple SP that just has a single statement 'commit;' and try calling it via ODP.NET.

     

    Needless to say, if it cannot be done using ODP.NET, it cannot be done via the adapter.

     

    Hope this helps,

    Manas

    Friday, October 17, 2008 4:25 PM
  • The SP code is black box for me. I just need to paas the parameters to it!!
    Yes, it must have Rollback/Commit in it and I tested that it fails in the scenario as mentioned.
    But is there a solution to it, except for calling it from .NET wrapper?
    Friday, October 17, 2008 5:28 PM
  • I have not been able to use ODP.NET to invoke such an SP, and haven't found any solution till date (and have stopped looking for one now).

    Saturday, October 18, 2008 6:11 AM
  • Then I guess the only way left is to call in in wrapper .NET class?
    Tuesday, October 21, 2008 8:30 PM
  • By using wrapper .NET class for the above, Is there any way I can capture the exceptions and errors as returned by Oracle to Biztalk and resume the orchestration? I have code done if I was just using the normal procedure call using port. How to implement/reuse the same code?
    Monday, November 3, 2008 4:22 PM
  • I am assuming that in this case your message gets suspended and you instead want to continue processing, right?

    If so, you can probably look at using the "Enable Routing for Failed Messages" feature in Biztalk.

     

    In short, this feature allows you to handle messaging failures (in your case, the adapter throwing an exception) in an automated fashion instead of having the messages suspended. You can subscribe to the 'Failed Messages' from an orchestration (or send port) based on a set of context properties, just like you'd do for any other message and do the neccesary error-handling, if any.

     

    Please look at the following links for details:-

     

    http://technet.microsoft.com/en-us/library/aa578516.aspx

    http://msdn.microsoft.com/en-us/library/aa578574.aspx

    Tuesday, November 11, 2008 8:21 AM