« TSQL for insert sproc parameters | Main | Oracle and BizTalk Server, Part 1 »

Oracle and BizTalk Server, Part 2

This is the second post in a series on the BizTalk Oracle Adapter. The goal is just to demonstrate the capabilities and document any traps that I experienced.

Part 1 was about setting up Oracle and performing a basic insert from BizTalk Server. Part is going to take it a step further and call an Oracle Package. For the unfamiliar a package is (according to Oracle):

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts, a specification (spec) and a body; sometimes the body is unnecessary. The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body defines the queries for the cursors and the code for the subprograms. 

There’s a great deal of information regarding packages floating around and I’m in no way an expert so won’t get too deep here. This scenario extends the example from part 1 and adds an Orders table with the many to one relationship with Customers. I wanted to show that you can send complex types to Oracle which I think is a pretty handy feature.  


The goal is to update or create a customer and insert 1 or more orders in one call to the Oracle database. To be honest it took me a while to figure out how I could develop a package that would work with BizTalk. The challenge is defining the orders collection in such a way that the Oracle Adapter would accept. I wanted to generate a request message that looked something like this:


<Order 1/>

<Order 2/>

In Oracle you can define collections a few different ways and in different places; in packages, as UDT (in a schema), records, varray’s, tables, objects etc. Problem is that the BizTalk Oracle Adapter and ODP.NET doesn’t support a lot of them. Microsoft has documented this but it made my brain hurt trying to understand it.

I finally got it to work by defining my collection as a UDT (User Defined Type). I created one called SINGLEORDER which is an object:

create or replace
And then another Type called ORDERSTYPE which is a table or varray of SINGLEORDER.
create or replace
I could have defined SINGLEORDER as a ROWTYPE based on the CUSTOMER_ORDERS table but that seemed to be unsupported by the Adapter due to the levels of nesting:

The Oracle Database adapter supports UDT nesting only up to two levels.

Oracle packages have a specification and a body. The package specification in the end looked like this:
create or replace
PROCEDURE CreateCustomerOrders (CustomerName VARCHAR2, CustomerID NUMBER, Orders ORDERSTYPE);
Note that the Orders variable is referencing the Type ORDERSTYPE defined above.
You’d call this package in PL/SQL like this:

BTS.CUSTOMERORDERS.CreateCustomerOrders('Mike',null, BTS.ORDERSTYPE(

SINGLEORDER(null,null,'EEE',200) ));

The body is where all the magic happens.
create or replace
NewCustomerID NUMBER;
PROCEDURE CreateCustomerOrders (CustomerName VARCHAR2,CustomerID NUMBER, Orders ORDERSTYPE ) AS

NewCustomerID := bts.customerid.NEXTVAL;
INSERT INTO BTS.Customer ( id, name) VALUES (NewCustomerID, CustomerName);

FORALL i IN orders.FIRST..orders.LAST
INSERT INTO BTS.CUSTOMER_ORDERS (customerid,id, qty, sku)
VALUES( NewCustomerID, BTS.ORDERID.NEXTVAL, orders(i).Quanity, orders(i).SKU );

UPDATE BTS.Customer SET Name = customername WHERE ID = CustomerID;

FORALL i IN orders.FIRST..orders.LAST
INSERT INTO BTS.CUSTOMER_ORDERS (customerid,id, qty, sku)
VALUES( CustomerID, BTS.ORDERID.NEXTVAL, orders(i).Quanity, orders(i).SKU );


END CreateCustomerOrders;

This should be pretty easy to follow, we check whether CustomerID is NULL and either create a new customer or update the existing. You’ll notice I’m doing a FORALL on the Orders collection, this is another area that stumped me. From the examples on Oracle’s site I thought I could treat the Orders variable just like a table and select directly from it but it seems the complier never got that memo. When I did that it would throw exceptions about not being able to find the table or view. Gave up trying to get that working and went with the FORALL statement. Perhaps someone more familiar with Oracle can help with that.  
Now that we have our package complied and tested we’re ready to generate some schemas for BizTalk.


To generate the schemas we jump back into the Add Generated Items/Consume Adapter Service form. You’ll configure the URI as before with one notable difference. When the Adapter generates the schemas it will want to generate an assembly that help it with the UDT’s we created in Oracle. There are 2 properties on the Bindings tab: GeneratedUserTypesAssemblyFilePath and GeneratedUserTypesAssemblyKeyPath. If your package is using UDT’s you’ll have to provide a location for the generated dll’s and a strong name key. If you don’t you won’t get an exception until runtime when it tries to find the DLL, but more on that later.
This will generate 2 schema files (if you have UDT’s) and one orchestration with our message and port types defined for us.
The first Schema OracleDBBinding_CUSTOMERORDERS looks like this:

And the second OracleDBBinding_UserType looks like this:


Obviously CUSTOMERORDERS is referencing UserType, nothing shocking here apart from that I spelt Quantity wrong. Too many screenshots deep to care about that now you’ll just have to live with it.

Now we can setup a Map to transform the data from our Customer format to the Oracle Package Message

<ns0:Customer Name="Stackoverflow" xmlns:ns0="http://OraclePOC.Schemas.Customer">
<ns0:Orders SKU="SKU_0" Qty="100" />
<ns0:Orders SKU="SKU_1" Qty="100" />
Nothing fancy here other then remember to xsi:nill fields you want to be NULL otherwise you’ll get exceptions.


The first time I tested it I wasn’t aware of the UDT assembly mentioned above and received this error.

The adapter failed to transmit message going to send port "SendPort2" with URL "oracledb://localhost:1521/ORCL/Dedicated". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.MetadataException: The assembly required for type mapping not found.

Server stack trace:
   at System.ServiceModel.AsyncResult.End[TAsyncResult](IAsyncResult result)
   at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
   at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
   at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

Exception rethrown at [0]:
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
   at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)".

The assembly required for type mapping not found. Went back over the documentation and found this nugget. Basically we have to provide the location and key when we generate the schemas and then GAC the DLL or copy it to the BizTalk program files directory. The generated User Defined Assembly defines our Types and implements an Interface called IOracleCustomType. It basically helps the adapter moved back and forth from a .NET object into an Oracle Type. A little unexpected but it makes sense.

public class ORDERSTYPE : INullable, IOracleCustomType
// Fields
public SINGLEORDER[] Array;
private bool mIsNull;
private OracleUdtStatus[] mStatusArray;

// Methods
void IOracleCustomType.FromCustomObject(OracleConnection connection1, IntPtr ptr1)
OracleUdt.SetValue(connection1, ptr1, 0, this.Array, this.mStatusArray);

void IOracleCustomType.ToCustomObject(OracleConnection connection1, IntPtr ptr1)
object statusArray = null;
this.Array = (SINGLEORDER[])OracleUdt.GetValue(connection1, ptr1, 0, out statusArray);
this.mStatusArray = (OracleUdtStatus[])statusArray;

// Properties
public virtual bool IsNull
return this.mIsNull;

public static object Null
ORDERSTYPE orderstype = new ORDERSTYPE();
orderstype.mIsNull = true;
return orderstype;

public virtual OracleUdtStatus[] StatusArray
return this.mStatusArray;
this.mStatusArray = value;

The next exception I got was due to the send port running in a host that wasn’t 32-bit only. The Error you’ll receive is a little misleading:

The adapter failed to transmit message going to send port "SendPort2" with URL "oracledb://localhost:1521/ORCL/Dedicated". It will be retransmitted after the retry interval specified for this Send Port. Details:"System.Reflection.ReflectionTypeLoadException: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.

Server stack trace:
   at System.Reflection.Module._GetTypesInternal(StackCrawlMark& stackMark)
   at System.Reflection.Assembly.GetTypes()
   at Microsoft.Adapters.OracleDB.OracleDBConnectionFactory.LoadUdtAssemblies(String UserAssembliesLoadPath)
   at Microsoft.Adapters.OracleDB.OracleDBConnection.Microsoft.ServiceModel.Channels.Common.IConnection.BuildHandler[TConnectionHandler](MetadataLookup metadataDictionary)
   at Microsoft.ServiceModel.Channels.Common.Design.ConnectionPool.GetConnectionHandler[TConnectionHandler](Guid clientId, TimeSpan timeout, MetadataLookup metadataLookup, String& connectionId)
   at Microsoft.ServiceModel.Channels.Common.Channels.AdapterRequestChannel.OnOpen(TimeSpan timeout)
   at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannel.OnOpen(TimeSpan timeout)
   at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)

Exception rethrown at [0]:
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at System.ServiceModel.ICommunicationObject.Open()
   at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.GetChannel[TChannel](IBaseMessage bizTalkMessage, ChannelFactory`1& cachedFactory)
   at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.SendMessage(IBaseMessage bizTalkMessage)".

The insert example from Part 1 didn’t mind running in a 64-bit host but I guess it’s the UDT’s or something about packages. Easily fixed.

After that it worked a treat. The main challenge I see are the restrictions on defining collections in the package that will work with the Adatper or ODP.NET. Someone that has invested a lot into packages that want’s to use BizTalk is going to be disappointed or at least spend a lot of time trying to work around it. Still I like being able to send complex types to the database. MSSQL doesn’t really support that although you can send XML and kind of achieve the same thing but it’s not as elegant. On the flip side the Oracle adapter does not support XML data types which seems odd at best.

Source Code.

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>