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.
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.
I want service response to be in "standard normalized manner". Standard response schema to notify sucess/failure is below
So I developed schema and maps for Oracle Insert response form oracleDB to normalized schema. 2 map are below.
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)
Then I created maps to map Original Data Schemas to oracle request schemas.
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
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
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.
2) Second Policy To Set the OutBound Transport End Points
3) Thrid Policy to set the Map to normalize response to Standard Reponse.
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.
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.