« Oracle and BizTalk Server, Part 2 | Main | Using PowerShell to Upgrade BizTalk Projects »
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.

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):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>