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.

Monthly Archives: December 2010

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