Tenalinaga's Blog

I am frog in a well. Every time I jump out of the well; I relialize I am in bigger well and eventually get bored which motivates me to get to next level.

Category Archives: Adapters

Multi-record Insertion into Oracle DB using ESB Messaging Scenario

Multi-Record Insertation into any table within Oracle DB using ESB Messaging Scenario based on Message Type

Idea of this sample seems to pretty be intersting when I first thought about it. we are originally thinking more in lines of having seprate WCF service to inert into each table and Main Orchestration having to call sepecific WCF service dynamically. These services need individual orchestration to published as WCF service.

But here Idea is to develop one single service which can be consumed by generic orchestration which accepts XMLDocument type. Input XMLDocuments would be one among set of defiend schemas that hold data that should be inserted to corresponding tables. We achive this WCF service with out orchestration (just messaging senario) using Itineraries.

Step1: Creation of 2 test Oracle Tables

For Demonstration I created 2 tables in HR database.  I want to insert into any of the 2 tables based on the incoming schema.

Oracle Test Tables


 Step2: Creation of Schemas and Maps

using Consume Adapter Service -> OracleDBBinding I have generated  Request schemas for table insert operation. Generated schema structure for “TABTESTPRODCUTS” and “TABTESTSTORES” table is shown side by side.

Oracle Insert Schemas

I want service response to be in "standard normalized manner". Standard response schema to notify sucess/failure is below

Normalized Response

So I developed schema and maps for Oracle Insert response form oracleDB to normalized schema. 2 map are below.

Oracle Insert Schemas

I also created 2 schemas (Original Data Schemas) which has data that should be inserted into oracleDB. (In a genralized sense these can be defined set of defined schemas that hold data for corresponding tables in OracleDB)

Native Data Schemas

Then I created maps to map Original Data Schemas to oracle request schemas.

Maps To Request Schema

Step3: Creation of WCF Service Receive Port and Dynamic soilict-response Port

We proceed to cretate a WCF service using WCF schema publishing Wizard and create a receive location. Input Schema is of type “Microsoft.XLANGs.BaseTypes.Any” and Output response schema is standard normalized response. Configure Receive Location is shown below

WCF Service Receive Port

ItinerarySelectReceiveXML has itinerary selector component that can select the itinerary. we use Resolver.Itinerary" to fetch itinerary from Database. Itinerary we are about to develop in next steps is named "GenricOracleInsert". We also create a Dynamic Solicit Response port and provide configuation as shown below

Dynamic Solicit Respone Port

Step4: Creating BRE Polcies

We will need to create 3 BRE policies.

1) First one to Select the Map based on the incoming message type for the input schema.

 BRE Request Map

2) Second Policy To Set the OutBound Transport End Points

BRE Request Map

3) Thrid Policy to set the Map to normalize response to Standard Reponse.

 BRE Request Map

Step5: Itinerary Design

Now we have all the required artifact. we need deploy them in BT application. Then we can proceed with Itinerary Design

Itinerary is like routing slip and steps associated are shown below.

based on messagetypes BRE policy provide information to perform dynamic routing and transofrmations.


 Itinerary Design

Step6: Generic InBound Test Orchestration

Generic Inbound Test Orchestration

We now create a test orchestration to test the WCF service we create. This genric orchestration accepts valid XMLDocuments as input.

Typically incoming XMLDocuments are validated by the receive XMLdisassembler pipeline component and subscribed by the orchestration.

Then we route defined Orginal Data schemas to the WCF service (remember WCF service input schema is also generic which accepts any valid XMLDocuments).

Then baed on BRE policies it gets transformed, and Outbound transport context properties are set and subscribed by dynamic solicit response port.
On the response from solicit-response port we choose a map based on policy in BRE to get normalized response.

Generic Test Orchestration for the WCF service would look like.

Itinerary Design


BizTalk WCF-Oracle Adapter

Oracle 11g

OUTBOUND Operations

1) Using Stored Procedure with SYS_REFCURSOR

Here we try to select multiple rows from Oracle Database using WCF-OracleDB adapter from BizTalk.
For the example I have used “EMPLOYEES” and “JOB_HISTORY” tables in “HR” department. I created a package and two stored procedure within the package.
First one selects Employees based on DepartmentID (while other gets employee start and end date based on employee_id).
For this example I used first stored procedure.

Creating Package

Package Creation

Create Package Body

Package Body

Now that we have stored procedure we need to generate request and
response schemas using BizTalk Adapter Service.
Then we can configure by providing credentials and hostname and service name (this information is avilable in tnsnames.ora file).

once you succeed in connecting to database you can browse to your package then select stored procedures


This will generate the schema

Generated Schema

we develop the orchestration send the request (transform shape maps the input ID to ID in Request ) and get the response


Now we build the application and deploy it into BizTalk administrative console. We configure the adapter.
We provide the credentials and there are couple of GOTCH here. “OperationName” must be equal to logical request response operation name within orchestration.
In the Configuration Bindings tab Set “Ambient transcation to false.


Resultant Output Would look like


2) Using Stored Procedure with REF CURSOR

You can obtain a different result by slight modification of the stored procedure and following the same procedure

stroed procedure will select the records with status ‘NEW’ and update their status to ‘OLD’ once they are written into cursor.

Genrated schema would be little different. It will have same request element and response would look like


3) Using Basic SQL Select

Here we again use AddGenerateItems -> Consume Adapter Service -> OracleDB Binding and provide the credentials and HostName and Service name as above.
Once we establish connectivity we select the requried table and the operation. This would generate a schema which looks like 

Consume Adapter Service

Here we need a map to fill the column names (required column namesin table) and filter expersion


Orchestration to send req and obtain response would be very much similar to the above method and deployment and send port(req-resp) would be similar.Beaware of above mentioned GOTCH’s.

Obtained result would look like


INBOUND Operations
4) Polling and Notification Services

For Pooling service during the configuring database setting -> Binding properties -> Polling Receive -> Enter following
1) Polling data Avilable (if this data is avilable then it pools)
2) Polling Statement
3) Post Poll Statement (executes after polling statement gets executed)
4) enter the credentials
5) Click ok and test the connectivity.
6) Select “InBound” Operations and select “\” on top then Polling this will generate the required polling input schemas.

For Notification service during the configuring database setting -> Binding properties -> Enter following
1) Enter the notification statements
2) Set notifyonlistenerstart to true
3) enter the credentials
4) Click ok and test the connectivity.
5) Select “InBound” Operations and select “\” on top then Notification this will generate the required Notifcation input schemas.

Gotcha: you must change the notification for this to work
In Oracle : grant change notification to

All the above procedure is used of SELECT Now short notes for INSERT

You can write a stored procedure for insert and generate schemas. But Insert schemas generated has one record Request and response.

using BASIC CRUD operations of WCF-Oracle adapter on the tables it is possible to insert multiple rows into oracle. You should use “Insert” Node which imports array of inserts.
operation name of logical port should be Insert.

I shall try to find if there is a way to insert multiple records into oracle table using stored procedure.

BizTalk 2010 Intergration with Oracle E-Business Suite

I started Working on Oracle E-Business Suite. Will Have more stuff coming up Eventually.

Error with ODBC Driver

GetBatchOfMessagesAndSubmit Error: Receive Port query processing failed: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

The adapter “ODBC” raised an error message. Details “Receive Port query processing failed: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified ”

Resoultion: Remove user DSN user account and add it to System DSN.