@michaelLipscombe 

Entries in Oracle (2)

Sunday
May132012

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:

<Customer>

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

And the second OracleDBBinding_UserType looks like this:

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
 
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.

Thursday
May102012

Oracle and BizTalk Server, Part 1

Had someone ask about integrating BizTalk Server with Oracle. I once helped a firm that was using it but I didn’t get time to delve in to it. Someone generated the schemas and that was the closest I got. Figured it was time to learn and as I don’t know a lot about Oracle I decided to download it and give it a go.

The purpose of this post is to provide a summary of adapter and how it’s used within BizTalk. It will also provide a basic introduction to Oracle albeit limited to the simple scenarios. I’ll break it out into a few different posts:

  1. Installing Oracle and a simple insert scenario
  2. Executing a Package
  3. Executing a select and return the recordset to BizTalk
  4. Polling Oracle from BizTalk

Note: This is the first time I’ve ever used Oracle so go easy.

Installing

You can download Oracle 11g here. Apart from the annoying oracle.com account business it’s pretty easy to get this thing downloaded and installed.

Didn’t hit any snags until I tried to launch the SQL Developer application. It asked for the location of the java executable. I pointed it to the java directories installed with the database but that just had it more angry. I browsed to the directory SQL Developer lived in and deleted the location I gave it from the sqldeveloper.conf file. Installed Java and the J2ESDK and pointed it to those instead. It then complained that it couldn’t find msvcr100.dll. Found a forum post that said to copy the dll from the Java directories to the same directory SQL Developer runs from and away we went.

Service Name & SID

During the installation the only thing I changed was the install location. It wanted to use an SID of ORCL which seemed reasonable to me so I left it. From what I can tell SID is the unique name of the instance and service name is the alias. You’ll need to know the service name to connect from visual studio when generating schemas from BizTalk. If you don’t know locate a file called tnsnames.ora and it will tell you. My SID and Service Name were the same (ORCL) but that is probably a default.

image_thumb[2]

Connecting

Once SQL Developer got over it’s Java issues it was time to connect. During the installation I set the administrator password so I logged on as SYS using that password.

image_thumb[5]

Creating a User

I wanted to create a user and schema that I could use with BizTalk and that wasn’t an admin.

CREATE USER BTS DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
GRANT UNLIMITED TABLESPACE TO BTS;
GRANT CREATE SESSION TO BTS;

As BizTalk will be using this user to login it needs permission to create a session. The Tablespace business apparently allows the user/schema to create tables or more appropriately permissions to use the USERS tablespace.

You should now be able to logon as this user.

image_thumb[8]

Creating Tables

Now that we have our user I will create two tables Customer and Orders.

CREATE TABLE CUSTOMER
(
ID NUMBER NOT NULL,
NAME VARCHAR2(20 BYTE)
, CONSTRAINT CUSTOMER_PK PRIMARY KEY
(
ID
)
ENABLE
)
TABLESPACE "USERS"
LOGGING
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
;
CREATE TABLE ORDERS
(
ID NUMBER NOT NULL,
CUSTOMERID NUMBER NOT NULL,
SKU VARCHAR2(20 BYTE) NOT NULL,
QTY NUMBER NOT NULL
, CONSTRAINT ORDERS_PK PRIMARY KEY
(
ID
)
ENABLE
)
TABLESPACE "USERS"
LOGGING
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
;

ALTER TABLE ORDERS
ADD CONSTRAINT ORDERS_CUSTOMER_FK1 FOREIGN KEY
(
CUSTOMERID
)
REFERENCES CUSTOMER
(
ID
) ENABLE
;
In Oracle Identity columns don’t exist it uses sequences instead. You have to define the sequence and then create a trigger on the table. Don’t know how common it is with Oracle developers to do this but as I wanted to test BizTalk inserting directly into the table I figured this approach would be the easiest (see section on InlineValue below).
First create a sequence and then a trigger that executes on a new row.
CREATE SEQUENCE  "BTS"."CUSTOMERID"  MINVALUE 1 MAXVALUE 9999 
INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE ;
CREATE OR REPLACE
TRIGGER BTS_CUSTOMERID_SEQ
BEFORE INSERT ON BTS.CUSTOMER
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
SELECT customerid.NEXTVAL
INTO :new.id
FROM dual;
END;

BizTalk

Now that we have our database and tables we’ll setup BizTalk to perform a basic insert into the customer table. Before you can do any of this you’ll have to install the LOB Adapters as explained here.

Insert

The next step is to generate the schemas, this is done via the Add Generated Items wizard in Visual Studio.
image_thumb[11]

And then selecting Add Adapter/Add Adapter Metadata
image_thumb[13]

Which opens the Add Adapter Wizard.

image_thumb[15]

Which takes us to the familiar Consume Adapter Service form.

image_thumb[17]

Next we have to connect to the Oracle database.

image_thumb[19]

image_thumb[22]

Click the Connect button back on the Consume Adapter Service form and it should connect.

image_thumb[26]

As I’m just testing a basic insert I expand the Schema BTS and click on the table Customer. This will show all the available operations for that table.

image_thumb[28]

I’ll only add the Insert operation and click OK.

image_thumb[30]

This will add a new schema to our project plus an orchestration that will have all the multiple part messages defined for us.

image_thumb[31]

The XSD will have a type called CUSTOMERRECORDINSERT which represents the structure of the table and some other standard nodes that the adapter expects for the Insert.

The Record called RECORDSET is repeatable so you can batch multiple Customer inserts.

image_thumb[33]

I’ll move the orchestration to the Orchestrations project. I’ll create a simple Customer schema which I’ll use to initiate the process and map it to the Oracle Insert message. We’ll just use the FILE adapter to read this guy in and pass it to the orchestration.

image_thumb[34]

image_thumb[51]

Something to pay attention to in the map is the attribute InlineValue. Normally you just map the column element, in this case NAME. The documentation’s explanation for InlineValue is

You can use the InlineValue attribute in the Insert operation to insert computed values into tables or views in the Oracle database. This is an optional attribute and is available for all simple data records in a multiple record Insert operation. If you specify a value for this attribute, it overrides the specified value of a record. For more information about the InlineValue attribute.

Que?

This is a pretty handy feature. I had setup a trigger to get the next number from the sequence when a new record is inserted. The InlineValue attribute lets you make calls to sequences and probably functions during the insert. I could remove the trigger from the table and map the string “customerid.NEXTVAL” to the ID columns InlineValue, customerid being the name of the sequence and NEXTVAL the method that returns the umm next value.

image_thumb[55]

image_thumb[39]

When you setup the WCF-Custom (WCF-OracleDB) Send Port there are a couple of things keep in mind.

1. The Address or URI will be something like: oracledb://localhost:1521/ORCL/Dedicated. If you’re not sure go back to the visual studio add generated items wizard, connect to the database again and you can copy it from there.

image_thumb[42]

2. As with most WCF send ports you need to provide the SOAP Action. For this first example as we’re only support one action so I’ll use: http://Microsoft.LobServices.OracleDB/2007/03/BTS/Table/CUSTOMER/Insert. Again if you’re not sure you can get this from the generated schema.

image_thumb[44]

Don’t forget to add the credentials.

image_thumb[46]

Start it all up and drop in a Customer message.

<ns0:Customer Name="Twitter" xmlns:ns0="http://OraclePOC.Schemas.Customer">
<ns0:Orders SKU="SKU_0" Qty="Qty_1" />
</ns0:Customer>

and….

image_thumb[48]

Pretty straight forward and very easy to setup, granted this is a very simple scenario.

Source Code if anyone is interested.