none
Sending multiple rows of data INTO oracle pocedure RRS feed

  • Question

  • I am looking for a method to insert multiple rows of data ,in one shot, in to an Oracle table from BizTalk ? 

    The obvious choice is to pass a REF cursor in to an Oracle Procedure from BizTalk.  The WCF-based Oracle DB adapter is supposed to be supporting Oracle IN REF cursors and I have found the documentation saying "In a scenario where the adapter provides an input REF CURSOR to a stored procedure, the adapter client must provide a command that, when executed, obtains the REF CURSOR" 

    I still cannot get the complte picture, how to obtain the
    REF CURSOR using adapter client and then fill the data and execute the proc.

    Any step by step intructions or information regarding the exact adapter settings are greatly appreciated.

    I have looked at the "
    insertBatchSize " option, but it is hard to get direct table access to use an insert statement. A stored procedure/function is the only option.

    Thanks in advance
    • Edited by s_r_e_e Thursday, June 18, 2009 3:00 PM
    Tuesday, June 16, 2009 7:00 PM

Answers

  • Hi Sreejith,

    As I described, Oracle Ref Cursors are by design, pointers to oracle result sets. You can bing for Oracle Ref Cursors, and you should get programming examples on the scenarios.

    Coming to your specific case, I see that you want to send multiple rows of data to a stored proc. Let me articulate the possible solutions for you:

    1. Use Oracle Adapter in BizTalk Adapter Pack 2.0: Oracle DB adapter in BAP 2.0 supports the use of PL/SQL tables of records, PL/SQL tables of simple types (index by tables) as well as VARRAYS. This would be the cleanest solution

    2. Insert data into a temp table (you can insert multiple rows in one go), then trigger the SP - the SP would read from the temp table. (works with BAP 1.0 too).

    3. Create an SP that takes 100 parameters, use a map in Biztalk to send 100 parameters. Kind of creating a batch.

    I'd recommend option 1. In case you cannot upgrade, then option 2 should work fine.

    Thanks,
    Manas
    • Marked as answer by s_r_e_e Thursday, June 18, 2009 7:03 PM
    Thursday, June 18, 2009 5:20 PM

All replies

  • Hey,

    You can refer to the samples at http://msdn.microsoft.com/en-us/dd796258.aspx

    Look at the sample "Invoke Functions with REF CURSORs".

    Pls let us know if you've questions,

    Thanks,
    -- Murali
    This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
    Tuesday, June 16, 2009 8:22 PM
  • Thank you for the information. We have tried this sample and found how to "obtain the REF CURSOR using adapter" and the sample shows how to execute the procedure with the cursor.

    Could you please advice, How to fill the data in to INPUT REF Cursor before executing the proc ?

    In the sample, the input message seem to be an sql statement/sql function to obtain the refcursor.

    <INRECS>BEGIN OPEN ? FOR SELECT * FROM ACCOUNTACTIVITY WHERE ACCOUNT=100001; END;</INRECS>
    In the real life, the input is the actual data from another source. We have been struggling to find a way to map the actual source data (from a database) to the input ref cursor.

    Thanks again
    Sreejith
    Wednesday, June 17, 2009 3:57 PM
  • If you mean the 'other source' is another Oracle server (lets call it server2) and you want to get a ref cursor in server1, then you could try the following (I havent tried this myself):
    1. define a database link to server2 on server1
    2. create a synonym on server1 for the table on server2 using database link
    3. using that synonym link to fetch the ref cursor as in the documentation

    If the 'other source' is non-Oracle, then you cannot use Ref Cursors, by definition, Ref Cursors are pointers to data on an Oracle system, and cannot be data itself.

    I hope this helps.

    Thanks,
    Manas
    • Edited by Manas G Wednesday, June 17, 2009 7:42 PM typo
    Wednesday, June 17, 2009 7:42 PM
  • Thanks for the reply.

    The source of data is not another Oracle server, it is an XML document. The DB link method wouldn't work

    I wonder, what would be the use of the adapter supporting IN REF CURSOR if it do not let data to be sent in to Oracle?

    I am looking for a way to send a set of rows in to Oracle from BizTalk , instead of calling a fucntion/proc with single parameter 100 times. What method
    BizTalk support to send multiple rows of data in to an Oracle procedure ?.    (If no native integration support, I think the option would be to send an XML / custom string and then parse it within the oracle procedure)

    Thanks
    Sreejith
    • Edited by s_r_e_e Thursday, June 18, 2009 2:58 PM
    Thursday, June 18, 2009 2:20 PM
  • Hi Sreejith,

    As I described, Oracle Ref Cursors are by design, pointers to oracle result sets. You can bing for Oracle Ref Cursors, and you should get programming examples on the scenarios.

    Coming to your specific case, I see that you want to send multiple rows of data to a stored proc. Let me articulate the possible solutions for you:

    1. Use Oracle Adapter in BizTalk Adapter Pack 2.0: Oracle DB adapter in BAP 2.0 supports the use of PL/SQL tables of records, PL/SQL tables of simple types (index by tables) as well as VARRAYS. This would be the cleanest solution

    2. Insert data into a temp table (you can insert multiple rows in one go), then trigger the SP - the SP would read from the temp table. (works with BAP 1.0 too).

    3. Create an SP that takes 100 parameters, use a map in Biztalk to send 100 parameters. Kind of creating a batch.

    I'd recommend option 1. In case you cannot upgrade, then option 2 should work fine.

    Thanks,
    Manas
    • Marked as answer by s_r_e_e Thursday, June 18, 2009 7:03 PM
    Thursday, June 18, 2009 5:20 PM
  • Hello Manas  , Thank you for the information..  I got carried away by assuming the refcursor will be the solution. The intend is to send multiple rows in to oracle.

    I prefer option one.. But we use BizTalk Adapter Pack 1.0 today.  We have plans to upgrade to 2.0 in the near future and will wait for it to implement this.

    Would you have any sample code of using  PL/SQL tables of records for input ?  

    Thanks
    Sreejith



    Thursday, June 18, 2009 7:01 PM
  • Hi Manas. 

    I don't understand very well what you mean about solution 1.

    Do you have any sample for help me?

    Tks.

    Regards.


    Espero ter ajudado
    Ruth Resende
    MVP, MCTS - Biztalk Server
    www.biztalkbrasil.com.br
    Twitter: @ruthresende

    Wednesday, June 6, 2012 6:29 PM
  • I've made this article in portuguese that I wrote about insert multiples records in one shot.

    http://www.biztalkbrasil.com.br/2012/06/inserindo-varios-registros-com.html

    I hope it helps.

    Regards


    Espero ter ajudado
    Ruth Resende
    MVP, MCTS - Biztalk Server
    www.biztalkbrasil.com.br
    Twitter: @ruthresende

    Wednesday, July 4, 2012 7:01 PM
  • S_R_E_E

    Instead of using Stored Procedure you can insert directly into an Oracle table using Insertgram.  I have used it in many occasions and it was successful. SP's are used only if i had to poll and update the recordset.

    Thanks


    Thanks & Regards,
    Roopesh Varma

    ________________________________________________________________

    Please remember to click “Mark as Answer” or "Vote As Helpful" on the post that helps you. This can be beneficial to other community members reading the thread.

    Thursday, July 5, 2012 7:19 PM