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]
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]
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]
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]
image_thumb[11]

And then selecting Add Adapter/Add Adapter Metadata

image_thumb[13]
image_thumb[13]

Which opens the Add Adapter Wizard.

image_thumb[15]
image_thumb[15]

Which takes us to the familiar Consume Adapter Service form.

image_thumb[17]
image_thumb[17]

Next we have to connect to the Oracle database.

image_thumb[19]
image_thumb[19]
image_thumb[22]
image_thumb[22]

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

image_thumb[26]
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]
image_thumb[28]

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

image_thumb[30]
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]
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]
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[34]
image_thumb[51]
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[55]
image_thumb[39]
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]
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]
image_thumb[44]

Don’t forget to add the credentials.

image_thumb[46]
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]
image_thumb[48]

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

Source Code if anyone is interested.