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

Create 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

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 

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.

Advertisement