none
BizTalk Adapter Pack 2.0 - Support for Oracle type TABLE RRS feed

  • Question

  • Hi.

    I'm looking into building a stored procedure in Oracle that will enable me to insert data into three tables in one call.  The relationship of the three table is such that TABLE1 can have one or more associated records in TABLE2, and TABLE2 can have one or more associated records in TABLE3.

     

    I did the code below in Oracle but I am unable to generate WCF bindings using Adapter Pack 2.0 and get the error:

    "Microsoft.ServiceModel.Channels.Common.MetadataException: Retrieval of Operation Metadata has failed while building WSDL at 'http://Microsoft.LobServices.OracleDB/2007/03/SYSTEM/Package/AA_TEST/INSERT_TEST' ---> Microsoft.ServiceModel.Channels.Common.MetadataException: Invalid Metadata. Check if the database user has permissions to UDT 'SYSTEM.AA_TEST.LV3_LIST'"

     

    Oracle Code (imagine we have tables TEST_LV1, TEST_LV2, and TEST_LV3)

    CREATE or REPLACE PACKAGE AA_TEST AS
       
      TYPE lv3_list IS TABLE OF TEST_LV3%ROWTYPE;
     
        TYPE lv2_list IS TABLE OF TEST_LV2%ROWTYPE;
       
      TYPE lv2_type IS RECORD (lv2 TEST_LV2%ROWTYPE, lv3  lv3_list);
     
        TYPE test_type IS RECORD (lv1 TEST_LV1%ROWTYPE, lv2 lv2_type);
       
        PROCEDURE insert_test(testdata IN test_type);
    END AA_TEST;

     

    Thanks in advance.

    Sunday, February 5, 2012 5:16 PM

Answers

  • Can you try this:

    Create you TABLE or VARRAY outside of a package (as a standalone USER DEFINED OBJECT) instead of declaring it inside the package. I still think that this should work out..

    Thanks,
    Manas


    -- Please mark as answered if this answers your question.

    • Marked as answer by Miggleness Monday, February 13, 2012 4:41 PM
    Thursday, February 9, 2012 11:38 AM
  • Ohh... bummer, didn't work still.

    I got this error while testing: "User-Defined Types can be nested only up to two levels. The last element node seen was "Array"."

    • Marked as answer by Miggleness Monday, February 13, 2012 5:07 PM
    Monday, February 13, 2012 5:06 PM

All replies

  • I'd also like to add that the issue isn't due to a permissions issue.  Generating the artifacts runs fine if I do not use table types.
    Monday, February 6, 2012 1:36 AM
  • you could do a couple of things here:

    1. Use a 'index by' table instead of a generic table i.e. "TYPE lv3_list is TABLE OF TEST_LV3%ROWTYPE INDEX BY INT;"
    2. Use a full blown User Defined Type (VARRAY of OBJECT)

    Oracle Adapter does not work with non indexed table types. This is to avoid a certain ambiuguity in type metadata fetched from the db server.

    The 1st one will be the easiest to try. Can you try this out and let me know?

    Thanks,
    Manas

     

     


    -- Please mark as answered if this answers your question.
    Monday, February 6, 2012 7:46 AM
  • Thank you for responding Manas.

    I just tried both varray and index tables but neither work.  The adapter's generator refuses to create the schemas and bindings when there's an array of a complex type (record or ROWTYPE).  I guess there's no hope with this one. I've tried multiple permutations already. :(

    I'm setting up BTS2010 and see if the latest version of the adapter supports that.

    Monday, February 6, 2012 3:36 PM
  • What error do you get when you use index by (PL/SQL) table? Does it contain one of the restricted data types mentioned on this page?

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

    Thanks,

    Manas


    -- Please mark as answered if this answers your question.

    Tuesday, February 7, 2012 8:27 AM
  • The error message is the same. 

    I might be meeting this limitation "The Oracle E-Business adapter supports UDT nesting only up to two levels."

    Oh, if only it supports nesting up to 3.

    Tuesday, February 7, 2012 5:16 PM
  • Can you try this:

    Create you TABLE or VARRAY outside of a package (as a standalone USER DEFINED OBJECT) instead of declaring it inside the package. I still think that this should work out..

    Thanks,
    Manas


    -- Please mark as answered if this answers your question.

    • Marked as answer by Miggleness Monday, February 13, 2012 4:41 PM
    Thursday, February 9, 2012 11:38 AM
  • I'll give that a try tomorrow.  Sadly, our design phase is done so we'll have to take the pain and create component to insert into the tables one at a time.  I'm still undecided if this should be done using adapters or just .NET.
    Saturday, February 11, 2012 5:32 PM
  • Wow, creating objects outside of packages did work.  Thanks a bunch.  I'll see if I can still incorporate this, otherwise I'd be doing a sin.

    Appreciate the help Manas.

    Monday, February 13, 2012 4:42 PM
  • Ohh... bummer, didn't work still.

    I got this error while testing: "User-Defined Types can be nested only up to two levels. The last element node seen was "Array"."

    • Marked as answer by Miggleness Monday, February 13, 2012 5:07 PM
    Monday, February 13, 2012 5:06 PM