none
Insert to two Tables as 1 Transcation using WCF Oracle DB Adapter

    Question

  • I'm using the WCF Oracle DB Adapter to perform inserts to 2 Oracle tables in 1 transaction.

    Set Transcation name "inserts1"; --> using SQLEXECUTE
    Insert into Table1 --> using generate Insert schema operation
    Insert into Table2--> using generate Insert schema operation
    both inserts and set transaction messages are sent within a single atomic transaction.
    Commit;--> above inserts statements succeed, issued using SQLEXECUTE
    Rollback; --> above insert statements fail, issued using SQLEXECUTE in the Compensate and/or exception handler

    the Rollback and Commit statements are not issued to the Oracle instance correctly. 

    I'm using BizTalk 2006 R2 and related WCF Oracle DB Adapter.  Is it possible to use Composite operations in BizTalk 2006 R2 Adapters?

    am I missing something.


    Ashith Raj
    • Edited by Ashi Friday, January 8, 2010 12:06 PM provided more infor
    Friday, January 8, 2010 11:16 AM

Answers

All replies

  • I am pretty sure CompositeOperation only works with the BizTalk Adapter Pack 2.0 and BizTalk 2009. Here is a link on using Oracle DB w/ composite operations like this: http://msdn.microsoft.com/en-us/library/dd788415(BTS.10).aspx

    I can only find examples of using CompositeOperation for SQL, Oracle EBS, and Oracle DB.

    Thanks,
    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Friday, January 8, 2010 11:36 PM
    Moderator
  • As I'm using BizTalk 2006 R2 and BAP v1. Using Composite operations is NOT an Option.

    The Latest Issue, I'm facing is on how to catch TargetSystemException and unable to send "ROLLBACK" message in the exception handler block.


    Ashith Raj
    Sunday, January 10, 2010 9:30 AM
  • If you can create SPs on your Oracle server, it might be easier for you to use a custom SP that performs inserts to the two tables.

    Also, BAP v2 is supported on 2006 R2 - if you have SA, you can use BAP v2 without having to upgrade BizTalk.

    Thanks,
    Manas

    Tuesday, January 12, 2010 7:47 AM
  • I don't have permission to create SP on Oracle Server, hence this option is ruled out.

    Let me check whether it is feasible or allowed to install BAP v2 on my BizTalk machine.

    Will the above sequence of Oracle DB Messages ensure atomic transaction of inserts in to 2 tables?


    Ashith Raj
    Tuesday, January 12, 2010 9:24 AM
  • Are the two inserts being performed using a single SQL Execute operation or 2 SQL execute operations?

    My guess is 2 SQL Execute operations - in that case, the oracle side will not be rolled back for the first operation in case the second one failes (you can try it). You will have to use compensation.
    Wednesday, January 13, 2010 4:21 AM
  • As my two inserts messages are send as 2 individual inserts to Oracle, even if I initiate a transaction, send the insert message to oracle and on failure of the first insert I'm using Compensation to rollback (by sending SQLEXECUTE Message with SQL Statement ROLLBACK WORK),

    it is not working because the inserts are a different transaction and my compensation SQLEXECUTE is a different transaction.

    Can I use SAVEPOINT or rollback a Named Oracle transaction statements in my compensation block.
    Ashith Raj
    Wednesday, January 13, 2010 5:13 AM
  • Did you consider using multi table insert in your SQL execute? It will make things really simple for you. It looks like this: INSERT ALL INTO TABLE1 (COL1, COL2) VALUES (VAL1, VAL2) TABLE2 (COL3, COL4) VALUES (VAL3, VAL4) ...
    Friday, January 15, 2010 4:22 AM
  • I thought about using SQLEXECUTE to send insert statements to both tables in 1 go.

    But my requirement is to transform from SAP IDOC and then insert huge number of records in 2 Oracle Tables. SQL EXECUTE may lead to complex string concatenations and dynamic generation of inserts is preferred.

    any Ideas in SQLEXECUTE  approach is appreciated.

    Now, I'm try to use .NET Assembly to perform Oracle DB Transactions using ODP.NET. I'll pass the mapped message to the .NET assembly to perform the inserts. I'll post know the learnings here soon.
    Ashith Raj
    Monday, January 18, 2010 9:41 AM
  • This blog post sounds like what you are trying to do but on SQL: http://blogs.msdn.com/biztalkcpr/archive/2009/10/05/inserting-parent-child-records-with-identity-column-using-wcf-sql-adapter-in-one-transaction.aspx.

    Here is info on using ref%cursor with the oracleDbBinding: http://msdn.microsoft.com/en-us/library/dd788392(BTS.10).aspx

    The following link also shows how to use Oracle record types too: http://msdn.microsoft.com/en-us/library/dd787864(BTS.10).aspx

    Thanks,
    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Friday, February 19, 2010 1:53 AM
    Moderator

  • Thanks for releasing the info, If only had got it earlier!
    Wednesday, March 2, 2011 9:57 PM