BizTalk - generating schema from Oracle stored proc with table variable argument
- by Ron Savage
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.
On the SQL Server side, I have a stored proc named GetItemChanges() that returns a variable number of records.
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:
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;
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 GeneratedUserTypesAssemblyFilePath 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 UserAssembliesLoadPath parameter.
I created a map to translate the GetItemChanges() schema to the Update_Item_Region_Table() schema.
When I run it the data is extracted and transformed fine but causes an exception trying to pass the data to the Oracle proc:
*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.*
So it is apparently not getting the information about the custom data type TBL_OF_REC into the Types DLL.
Any tips on how to make this work?
