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.  

Database

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:

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

TYPE SINGLEORDER

AS

OBJECT

( ID NUMBER, CUSTOMERID NUMBER, SKU VARCHAR2(20), QUANITY NUMBER

);

And then another Type called ORDERSTYPE which is a table or varray of SINGLEORDER.

create

or

replace

TYPE ORDERSTYPE

AS

TABLE

OF

SINGLEORDER;

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

PACKAGE CUSTOMERORDERS

AS

PROCEDURE

CreateCustomerOrders (CustomerName VARCHAR2, CustomerID NUMBER, Orders ORDERSTYPE);

END

CUSTOMERORDERS;

Note that the Orders variable is referencing the Type ORDERSTYPE defined above.

You’d call this package in PL/SQL like this:

begin

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

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

end;

The body is where all the magic happens.

create

or

replace

PACKAGE BODY CUSTOMERORDERS

AS

NewCustomerID NUMBER;

PROCEDURE

CreateCustomerOrders (CustomerName VARCHAR2,CustomerID NUMBER, Orders ORDERSTYPE )

AS

BEGIN

IF

CustomerID

IS

NULL

THEN

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 );

ELSE

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

IF

;

END

CreateCustomerOrders;

END

CUSTOMERORDERS;

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.

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.

image
image

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:

image
image

And the second OracleDBBinding_UserType looks like this:

image
image

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"

/>

ns0:Customer

>

image
image

Nothing fancy here other then remember to xsi:nill fields you want to be NULL otherwise you’ll get exceptions.

LET’S DO THIS!

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

[OracleArrayMapping]

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

{

get

{

return

this

.mIsNull;

}

}

public

static

object

Null

{

get

{

ORDERSTYPE orderstype =

new

ORDERSTYPE();

orderstype.mIsNull =

true

;

return

orderstype;

}

}

public

virtual

OracleUdtStatus[] StatusArray

{

get

{

return

this

.mStatusArray;

}

set

{

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.