none
Inserting into Oracle: To use or not to use Adapters?

    Question

  • Hi,

    I'm in a forked path on whether to use WCF Oracle adapters or not and need advice.

    Here's my scenario. 

    I need to insert data into three tables in Oracle. These three tables are associated to each other via keys.

    TABLE1
                - TABLE2 (TABLE1 key)
                               - TABLE3 (TABLE 2 key)

    The kicker here is that TABLE1 can have mutliple records in TABLE2 and TABLE2 can have multiple instances in TABLE3.  Before inserting into TABLE2, I need to get the parent key from TABLE1.  Before inserting into TABLE3, I need to get the parent key from TABLE2.

    I've three options here:

    1) Create customer type in Oracle to mimic the relationship between the three tables adn generate corresponding schema using the WCF adapter wizard
    2) Generate separate schemas for each table and perform looping in orchestration (2 called orchestrations for inserting into TABLE2 and TABLE3)
    3) Generate separate schemas for each table, create custom schemas using the disparate schemas as imports, create deserialized class for the custom schema,  and create .NET code to insert into the three tables.

    Option #1: Doesn't seem to be possible.  My trials are documented here: http://social.msdn.microsoft.com/Forums/en-US/biztalkr2adapters/thread/644c121d-e944-492f-a19a-7d72254f9fdd

    Now i'm choosing between options #2 and #3.  #2 follows the BizTalk way but since there's no concept of transactional insert and the fact that there can be 20+ TABLE2 records under TABLE1 and possibly 5 TABLE3 records for each TABLE2 record, going through the whole process of msgbox-pipeline-adapter will take up unnecessary resources.

    TIA

    Monday, February 13, 2012 3:19 AM

Answers

  • Hi

    I would suggest you to use Option 3 in this case, This will give you more control on databse logic and handling different exceptions. This will give you more control while debugging your code also.

    I always write .net classes to handle any complex databse insert and manululation of data. This will also give me a clean tier to apply any changes in the logic.

    Let me know your views.

    HTH

    Naushad Alam

    Monday, February 13, 2012 2:21 PM

All replies

  • Hi,

    I would suggest to implement your logic in a single stored procedure i.e. Inserting the data for all the tables in one procedure. Send all the data for 3 tables to this single procedure.

    Use the WCF-Oracle adapter for your schema generation.

    Following benifits will be achieved using this option,

    1) You will have to generate one single schema

    2) You will have to procedure only once. Hence the round trips or looping is avoided. This is very good from performance perspective

    3) It will be very easy to handle the transaction in single procedure. This is required in your case as there is a dependancy between table insertion.

    I hope this will be helpful to you. Let us know if your requirements are not fulfilled with this option.


    Thanks With Regards,
    Shailesh Kawade
    MCTS BizTalk Server
    Please Mark This As Answer If This Helps You.
    http://shaileshbiztalk.blogspot.com/


    • Edited by Shailesh Kawade Monday, February 13, 2012 5:50 AM
    • Proposed as answer by Paul Endras Tuesday, February 14, 2012 10:08 AM
    Monday, February 13, 2012 5:48 AM
  • Hello Shailesh.

    That's my first choice but I can't imagine that being possible with dynamic insert content unless I generate against a custom Oracle data type that mirrors the relationship of the three tables.  Sadly, I'm unsuccessful at getting the WCF adapter generator to accept >2 levels of  data type nesting.

    Imagine this relationship.  TABLE1 can have one or more TABLE2 records, and TABLE2 can have one or more TABLE3 records.

    TABLE1 Data
        - TABLE2 Data
            - TABLE3 Data
            - TABLE3 Data
            - TABLE3 Data
        - TABLE2 Data
            - TABLE3 Data
        - TABLE2 Data
            - TABLE3 Data
            - TABLE3 Data

    Monday, February 13, 2012 6:54 AM
  • Hi,

    Please refer to similar thread below,

    http://social.msdn.microsoft.com/Forums/en/biztalkr2adapters/thread/60102590-bac2-4d2d-b3a1-c72420cd0d92

    http://blogs.msdn.com/b/biztalkcpr/archive/2009/10/05/inserting-parent-child-records-with-identity-column-using-wcf-sql-adapter-in-one-transaction.aspx

    I hope this helps you.


    Thanks With Regards,
    Shailesh Kawade
    MCTS BizTalk Server
    Please Mark This As Answer If This Helps You.
    http://shaileshbiztalk.blogspot.com/

    Monday, February 13, 2012 7:28 AM
  • Sadly, I'm working on Oracle and I've been unsuccessful using similar steps documented in those links in Oracle
    Monday, February 13, 2012 7:52 AM
  • Hi,

    Please look at link below for WCF-oracle as well,

    http://social.msdn.microsoft.com/Forums/pl/biztalkr2adapters/thread/58bae46a-20e6-4027-8947-d924bc61db16

    http://msdn.microsoft.com/en-us/library/dd788415(BTS.10).aspx


    Thanks With Regards,
    Shailesh Kawade
    MCTS BizTalk Server
    Please Mark This As Answer If This Helps You.
    http://shaileshbiztalk.blogspot.com/

    Monday, February 13, 2012 8:39 AM
  • Hi

    I would suggest you to use Option 3 in this case, This will give you more control on databse logic and handling different exceptions. This will give you more control while debugging your code also.

    I always write .net classes to handle any complex databse insert and manululation of data. This will also give me a clean tier to apply any changes in the logic.

    Let me know your views.

    HTH

    Naushad Alam

    Monday, February 13, 2012 2:21 PM
  • Thanks Naushad.  I'm thinking the same.  Would be easier to controll the rollback in that manner.

    I'll look into composite operations as well just so I have an alternative.  The trouble with creating .NET code is generating the insert statement where there are some 180 columns which I have to deal with. Gross!

    I have posted an update to my question regarding nested data types in Oracle, sadly the Oracle WCF adapter only supports 2 nested layers max. (http://social.msdn.microsoft.com/Forums/en-US/biztalkr2adapters/thread/644c121d-e944-492f-a19a-7d72254f9fdd)

    Monday, February 13, 2012 5:10 PM
  • Ok, I had the same situation where i had to deal with 89 columns for insert . Good luck friend.

    HTH

    Naushad

    Monday, February 13, 2012 5:13 PM