When using Oracle in BizTalk Server, it is very common to come across time consuming tasks. This post was created to minimize time and maximize quality assurance.

Installing/Configuring Oracle

Download Oracle from (http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html). Start the setup and go through the Oracle database installation process. It is important to know the SID and the service name. The SID is the unique name of the scenario and the service name is the alias. This is crucial to connecting from Visual Studio when generating schemas from BizTalk. If the SID and service name is unknown locate the file tnsnames.ora. .. In default installation, this file is located at <Oracle Installation Folder>apporacleproduct<version>servernetworkADMIN.

Default SID and Service Name entry is shown as:

XE =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = XE)
 )
 )

Working with Oracle Database

Oracle SQL Developer” can be used to manage Oracle database objects. Oracle SQL Developer can be downloaded from (http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html) for free.

Open Oracle SQL Developer and create a new connection to logon as “SYS” in SYSDBA role.

Oracle-BizTalk (1)

Create a user and schema that can be used for BizTalk connectivity.

-- USER SQL
CREATE USER btuser IDENTIFIED BY test123 
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";

-- QUOTAS
ALTER USER btuser QUOTA UNLIMITED ON "USERS";

-- ROLES
GRANT "DBA" TO btuser ;

-- SYSTEM PRIVILEGES
GRANT CREATE SESSION TO btuser ;

Logon as btuser, create a table and name it EMPLOYEE, and then insert some records into it.

Oracle-BizTalk (2)

- - Create Table 

CREATE TABLE EMPLOYEES 
(
 ID INT NOT NULL 
, EMPLOYEENAME VARCHAR2(50) NOT NULL 
, DEPARTMENT VARCHAR2(20) 
, CONSTRAINT EMPLOYEES_PK PRIMARY KEY 
 (
 ID 
 )
 ENABLE 
);

- - Insert Data

INSERT INTO "BTUSER"."EMPLOYEES" (ID, EMPLOYEENAME, DEPARTMENT) VALUES ('1', 'John', 'Admin')
INSERT INTO "BTUSER"."EMPLOYEES" (ID, EMPLOYEENAME, DEPARTMENT) VALUES ('2', 'Kerry', 'Support')
INSERT INTO "BTUSER"."EMPLOYEES" (ID, EMPLOYEENAME, DEPARTMENT) VALUES ('3', 'Jack M. Hyatt', 'Admin')
INSERT INTO "BTUSER"."EMPLOYEES" (ID, EMPLOYEENAME, DEPARTMENT) VALUES ('4', 'Randy N. Thomas', 'I.T.')
INSERT INTO "BTUSER"."EMPLOYEES" (ID, EMPLOYEENAME, DEPARTMENT) VALUES ('5', 'Kevin K. Lewis', 'I.T.')

Configure Oracle Adapter in BizTalk Server

Create the Oracle adapter using BizTalk Server Administration using the following steps:

  • Expand Platform Settings and select Adapters.
  • Right-click the “Adapters node, point the cursor to New, and then click Adapter.
  • In the Adapter list, click WCF-OracleDB.
  • In the Name box, type WCF-OracleDB.
  • Click OK.
  • Restart Host instances to enable changes.

Oracle-BizTalk (3)

Let’s Start Developing the BizTalk Application

Open Visual Studio and create a new Empty BizTalk Project. Right-click the project, point the cursor to Add, and then click Add Generated Items.

Oracle-BizTalk (4)

Select Add Adapter Metadata, then click Add.

Oracle-BizTalk (5)

On the Add Adapter Wizard screen, select from the following list of registered adapters, click WCF-OracleDB and then click Next.

Oracle-BizTalk-A

The Consume Adapter Service screen will be displayed.

Oracle-BizTalk (6)

Click Configure, setup a Username and Password, the URI properties, and click OK.

Oracle-BizTalk (7)

Oracle-BizTalk (8)

Once done; click Connect to get definitions of all available operations.

Oracle-BizTalk (9)

Now, click Select in the Available categories and operations list, and then click Add to make it part of generated schema. Click OK to generate the schema file OracleDBBinding_EMPLOYEES.xsd.

Add a new project, Employees.Orchestrations, in the solution and add a new orchestration into it that will get connected with Oracle server and pull Employees data from it.

Add references of the Employees.Schemas project into the orchestrations project. Open EmployeeData orchestration in designer. In Orchestration view, create two new messages.

  1. RcvMessage – Bind this message and type the name Select that is a member of Employees.Shcemas.OralceDBBinding.EMPLOYEES. This message will be used by BizTalk for receiving request messages to be sent to Oracle server to get Employees data.
  1. Bind this message and type the name SelectResponse that is also a member of Shcemas.OralceDBBinding.EMPLOYEES. This message will get Employee data as a response from Oracle server.

The image below is the flow of orchestration that is developed to send a request to the Oracle server and get Employee data in response.

Oracle-BizTalk (10)

This Orchestration performs the following steps:

  1. ReceiveMessage shape, receives Select xml message from a one-way receive only port and passes it to the SendMessage
  2. The SendMessage shape sends a request to a two-way port that connects with Oracle to get employee data.
  3. Response message from SendEmployeeList port is passed to ReceiveResponse
  4. ReceivedMessage is sent to a send port using SendQueryResults shape to save the response at a file location.

Deploy the solution to your BizTalk Server and create the following ports:

  1. Receive Port (“Select” type xml message to trigger orchestration)
  2. Oracle Send Port (import from binding file generated along-with schema)
  3. Send Port (to save Oracle response as an xml file)

To trigger orchestration, drop the XML file to receive location containing request in following format:

<ns0:Select xmlns:ns0="http://Microsoft.LobServices.OracleDB/2007/03/BTUSER/Table/EMPLOYEES">
 <ns0:COLUMN_NAMES>*</ns0:COLUMN_NAMES>
 <ns0:FILTER></ns0:FILTER>
</ns0:Select>

You will get response data in the following format stored in an XML file:

<ns0:SelectResponse xmlns:ns0="http://Microsoft.LobServices.OracleDB/2007/03/BTUSER/Table/EMPLOYEES">
 <ns0:SelectResult>
 <ns0:EMPLOYEESRECORDSELECT>
 <ns0:ID>1</ns0:ID>
 <ns0:EMPLOYEENAME>John</ns0:EMPLOYEENAME>
 <ns0:DEPARTMENT>Admin</ns0:DEPARTMENT>
 </ns0:EMPLOYEESRECORDSELECT>
 <ns0:EMPLOYEESRECORDSELECT>
 <ns0:ID>2</ns0:ID>
 <ns0:EMPLOYEENAME>Kerry</ns0:EMPLOYEENAME>
 <ns0:DEPARTMENT>Support</ns0:DEPARTMENT>
 </ns0:EMPLOYEESRECORDSELECT>
 <ns0:EMPLOYEESRECORDSELECT>
 <ns0:ID>3</ns0:ID>
 <ns0:EMPLOYEENAME>Jack M. Hyatt</ns0:EMPLOYEENAME>
 <ns0:DEPARTMENT>Admin</ns0:DEPARTMENT>
 </ns0:EMPLOYEESRECORDSELECT>
 <ns0:EMPLOYEESRECORDSELECT>
 <ns0:ID>4</ns0:ID>
 <ns0:EMPLOYEENAME>Randy N. Thomas</ns0:EMPLOYEENAME>
 <ns0:DEPARTMENT>I.T.</ns0:DEPARTMENT>
 </ns0:EMPLOYEESRECORDSELECT>
 <ns0:EMPLOYEESRECORDSELECT>
 <ns0:ID>5</ns0:ID>
 <ns0:EMPLOYEENAME>Kevin K. Lewis</ns0:EMPLOYEENAME>
 <ns0:DEPARTMENT>I.T.</ns0:DEPARTMENT>
 </ns0:EMPLOYEESRECORDSELECT>
 </ns0:SelectResult>
</ns0:SelectResponse>

The goal is to easily connect and use the Oracle database with the BizTalk Server 2013 by following the steps given in this blog.