none
Oracle EBS Connections Causing Dehydration RRS feed

  • Question

  • I am having an interesting problem with my Oracle EBS adapter in that all of my orchestrations are dehydrating immediately.  I found and applied the following hotfix, which seemed to be related to my problem...

     

    http://support.microsoft.com/kb/934040

     

    And, things were, temporarily, working...so, I thought we had fixed it.  But, now I am back to the same state where orchestrations are dehydrating and I suspect it is due, potentially, to some settings on the adapter, but there is little detail on these settings...

     

    1)  Max Concurrent Calls - This is set to 5, and, mysteriously, I have 5 inactive sessions in Oracle.  I suspect, perhaps, that my transactions in each connection may be contending with one another, but I'm not sure...(and breaking those connections isn't an easy task here since we have to wait for an SR to be worked by Oracle!)

     

    2)  Use Transaction.  I am updating (inserting to) 3 separate tables in Oracle.  I have this all wrapped in a Biztalk transaction, but I'm wondering if Oracle's concept of a transaction is being used here.  Part of my work is doing a nextval on a sequence and using that number on the insert to the table (standard sequence use).  I understand that in a transaction in Oracle, doing a nextval won't necessarily NOT get applied...in other words, if a transaction that has the nextval in it has not completed and another transaction starts, it will be able to get the value AFTER the first nextval...But, I'm wondering if the "Use transaction" option in Biztalk handles it the same, especially when the sequence has a cache value.

     

    3) Refresh Agent...seems like I would always want it set to yes to re-establish connections, but will it really be able to handle partially-finished "transactions" then?

     

    ...I'm at my wit's end on this and would love to converse with someone familiar with the Adapter and the pros/cons of each setting and whether my hunches are right are not, while I wait for my Oracle connections to be broken....

     

    Thanks!

    Wednesday, February 13, 2008 8:34 PM

Answers

  • If Use Transaction is set to true then adapter issues a commit or rollback request for all active operations on any statement associated with an affected connection of its associated send or recieve port only.

    As such in this scenario if insert to the lines table failed (after the insert to the header table was successful), it will not rollback the insert operation of the header table as they are associated with different conections of different ports.

     

    Thanks,

    Sanjay

    Wednesday, February 27, 2008 1:58 PM

All replies

  • In Use Transaction section, you say that you are updating(inserting to) 3 seperate tables in Oracle and wrapped all in a Biztalk transaction. Could you please describe the orchestration you are using and the way you are binding the physical ports to the logical ports in your BizTalk application. Also could you please elaborate how you are wrapping all the insert operation of 3 seperate tables in one Biztalk transaction. If possible could you please share the input xml you are using.

     

    Regarding Refresh Agent, it cannot handle partially-finished transactions.

     

    Thanks,

    Sanjay

    Thursday, February 21, 2008 1:52 PM
  • Thank you for your reply!  As you can tell, I'm new to Biztalk and I'm maintaining a number of orchestrations that were built by someone that is no longer here and cannot give me the background as to why they were built this way.  Let me see if I can describe the orchestration...

     

    I have a Scope shape (assuming this is the Biztalk "transaction", as it has exception logic in it) that consists of 3 groups, and, of course, an exception block around the entire thing.  Within each group, I have a send and receive shape that is connected to a port (3 logical send/receive ports), the first, which is the native sql call for the sequence/next val, the second, which is the native sql call to insert to the first table (InvoiceHeader) and the third is the native sql call to insert to (InvoiceLines)...

     

    At the risk of over-simplifying, the xml for the last 2 calls is basically what's needed for an insert for each table (the lowest-level nodes for each column on the table).  The xml for the nextval, basically has a node that will be returning the number that is returned from the nextval.  I can supply this if it would help to understand, but they are rather lengthy, since the number of columns in each table are quite large (Oracle's define-able attribute columns).

     

    The request xmls is the data needed for the tables and the response would be the sqlcode returned from the call.

     

    I believe each logical port, though is mapped to one physical send port, which represents a native connection to the Oracle database (the adapter, which is configured with the connection string, etc).

     

    I don't see how this design will take care of a "rollback" if, for example, the insert to the lines table failed (after the insert to the header table was successful), as the connection to the oracle database for the call for the header table is probably complete, and, potentially closed (depending on how it connection pools)....

     

    I have done much database development, so I do understand how I would create a transaction, for example in a PL/SQL block, thus allowing a rollback if any part of the set of database calls error'd...but, I don't see how this design (within Biztalk) is conducive to that type of scenario.

     

    But, the adapter has the "Use transaction" setting....not sure what that really implies when I have it set to Yes or No.

     

    Thanks, in advance, for your insight.

    Friday, February 22, 2008 6:25 PM
  • If Use Transaction is set to true then adapter issues a commit or rollback request for all active operations on any statement associated with an affected connection of its associated send or recieve port only.

    As such in this scenario if insert to the lines table failed (after the insert to the header table was successful), it will not rollback the insert operation of the header table as they are associated with different conections of different ports.

     

    Thanks,

    Sanjay

    Wednesday, February 27, 2008 1:58 PM
  • Yes.  Thank you.  that is what I was thinking too.  If I put the insert to the header and detail table in a stored proc and set 'use transaction' to yes, that would probably get the results I wanted.  Additionally, if I put all of the insert statements on one connection/port within the Biztalk orchestration, with 'use transaction', it should also give me the functionality I want...I'm assuming I could do that, but haven't analyzed the orchestration enough to see how I could do that.  It seems that the ports were created as a one-for-one (one table, one port) for table updates and I would have to change the port entirely to be able to have multiple inserts for different tables.  Let me know if you know of good documentation to start with to make that kind of change.  Thanks!

     

    Wednesday, February 27, 2008 4:36 PM
  • Hi Sherry

     

    I would be very interested in understanding your Oracle e-business scenarios.

     

    If you are doing simple table access, you could try the new WCF-based Oracle DB adapter which is part of the BizTalk Adapter Pack. It has specific features to support transations from BizTalk.

     

     

     

     

     

     

    Thursday, February 28, 2008 12:07 AM
  • Ok.  Thank you.  I probably won't look into this at this time, as we are replacing our Oracle system with a non-Oracle solution.  But, this information was interesting, and I may look at it in the future.  Thanks.

     

    Thursday, March 6, 2008 3:14 PM