title: BizTalk - generating schema from Oracle stored proc with table variable argument
body: <p>I'm trying to set up a simple example project in BizTalk that gets changes made to a table in a SQL Server db and updates a copy of that table in an Oracle db.</p>
  
  <p>On the SQL Server side, I have a stored proc named GetItemChanges() that returns a variable number of records.</p>
  
  <p>On the Oracle side, I have a stored proc named Update_Item_Region_Table() designed to take a table of records as a parameter so that it can process all the records returned from GetItemChanges() in one call. It is defined like this:</p>
  
  <pre><code>create or replace type itemrec is OBJECT (
          UPC                VARCHAR2(15),
          REGION             VARCHAR2(5),
          LONG_DESCRIPTION   VARCHAR2(50),
          POS_DESCRIPTION    VARCHAR2(30),
          POS_DEPT           VARCHAR2(5),
          ITEM_SIZE          VARCHAR2(10),
          ITEM_UOM           VARCHAR2(5),
          BRAND              VARCHAR2(10),
          ITEM_STATUS        VARCHAR2(5),
          TIME_STAMP         VARCHAR2(20),
          COSTEDBYWEIGHT     INTEGER
  );
  
  create or replace type tbl_of_rec is table of itemrec;
  
  create or replace PROCEDURE Update_Item_Region_table  ( Item_Data  tbl_of_rec ) 
  IS
   errcode integer; 
   errmsg varchar2(4000); 
  BEGIN 
     for recIndex in 1 .. Item_Data.COUNT
     loop
  
     update FL_ITEM_REGION_TEST set
        Region            = Item_Data(recIndex).Region,
        Long_description  = Item_Data(recIndex).Long_description,
        Pos_Description   = Item_Data(recIndex).Pos_description,
        Pos_Dept          = Item_Data(recIndex).Pos_dept,
        Item_Size         = Item_Data(recIndex).Item_Size,
        Item_Uom          = Item_Data(recIndex).Item_Uom,
        Brand             = Item_Data(recIndex).Brand,
        Item_Status       = Item_Data(recIndex).Item_Status,
        Timestamp         = to_date(Item_Data(recIndex).Time_stamp, 'yyyy-mm-dd HH24:mi:ss'),
        CostedByWeight    = Item_Data(recIndex).CostedByWeight
     where
        UPC = Item_Data(recIndex).UPC;
  
     log_message(Item_Data(recIndex).Region, '', 'Updated item ' || Item_Data(recIndex).UPC || '.');
  
     end loop;
  
   EXCEPTION 
   WHEN OTHERS THEN 
   errcode := SQLCODE(); 
   errmsg := SQLERRM(); 
   log_message('CE', '', 'Error in Update_Item_Region_table(): Code [' || errcode || '], Msg [' || errmsg || ']  ...'); 
  END;
  </code></pre>
  
  <p>In my BizTalk project I generate the schemas and binding information for both stored procedures. For the Oracle procedure, I specified a path for the <strong>GeneratedUserTypesAssemblyFilePath</strong> parameter to generate a DLL to contain the definition of the data types. In the Send Port on the server, I put the path to that Types DLL in the <strong>UserAssembliesLoadPath</strong> parameter.</p>
  
  <p>I created a map to translate the GetItemChanges() schema to the Update_Item_Region_Table() schema.</p>
  
  <p>When I run it the data is extracted and transformed fine but causes an exception trying to pass the data to the Oracle proc:</p>
  
  <p>*The adapter failed to transmit message going to send port "WcfSendPort_OracleDBBinding_HOST_DATA_Procedure_Custom" with URL "oracledb://dvotst/". It will be retransmitted after the retry interval specified for this Send Port. Details:"System.InvalidOperationException: Custom type mapping for 'HOST_DATA.TBL_OF_REC' is not specified or is invalid.*</p>
  
  <p>So it is apparently not getting the information about the custom data type TBL_OF_REC into the Types DLL.</p>
  
  <p>Any tips on how to make this work? </p>