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.

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

AdapterConfiguration

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

Orchestration

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.

BTAdminAdapterConfiguration

Resultant Output Would look like

OutputSysCursor

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.
RefCursor

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

outputRefCursor

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

Map

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

Map

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.

Advertisements

12 responses to “BizTalk WCF-Oracle Adapter

  1. Harold January 11, 2011 at 6:24 pm

    Interesting – I thought that I read that with BT 2010 and the ODAC 11.2 you could actually call a stored procedure outside a package. I keep getting and error from the send pipeline, XML assembler, that the Data at the root level is invalid

  2. tenalinaga January 11, 2011 at 10:07 pm

    Hi Harold,

    I remember this error. When I am trying to insert data into oracleDB using ESB. Reason turned out map name is not resolved and message that i am trying to insert was not in the right format. (at least for me).

    Thanks for you comment.
    TenaliNaga

  3. Alejo March 12, 2011 at 9:18 am

    Hi tenalinaga,
    Can I to call a Oracle PL from itinerary?
    How I do?

    Thanks for you help

    • tenalinaga March 13, 2011 at 5:25 am

      Hi Alejo,

      Yes you can invoke PL/SQL stored procedures from Itineraries.

      In the post below I inserted multiple records into Oracle using WCF insert outbound operation. But idea is request schema sent to oracle could also be schema generated by stored procedure.

      Configuration of the BRE for ports would remain same.

      Multi-record Insertion into Oracle DB using ESB Messaging Scenario

      Thanks,
      TenaliNaga

      • Alejo March 13, 2011 at 1:21 pm

        Hi Tenalinaga

        Thanks for your help

        I must to call a “PL/SQL” using a “Static Resolve” and I do not know how to
        configure the routing properties.

        I have this:
        • Transport Name: WCF-Custom
        • Transport Location: OracleDB://{DataBase}.world/ (taked of the
        attribute “Address” of the binding file)

        Acction:http://Microsoft.LobServices.OracleDB/2007/03/SYSTEM/Package/PKG_
        Datos/SP_GET_DATOS (taked of the attribute “Acction” of the binding
        file)
        • Endpoint Configuration: BindingType=OracleDBBinding

        Do not know how to add the user name and password for authentication (I must to use Oracle Autentication) In the “endpoint configuration” the password attribute is disabled.

        However, I attempt to executed the “PL/SQL”, but this do not return nothing. The message remain in status “Ready to run”.

        How I doing from to make this?

      • tenalinaga March 13, 2011 at 1:47 pm

        Set End Point Outbound Trasport Type to WCF-Custom
        Hi Alejo,

        I do not have config information using static resolver handy. Using BRE is below.

        Set End Point WCF Action to ActionName
        Set End Point Outbound Transport Location to YourOracleLocation
        Set End Point Config to BindingType=Set End Point Config to BindingType=oracleDBBinding&Password=YourPassword&PropagateFaultMessage=true&UserName=YourUserName
        &BindingConfiguration=(&lt) binding name=”oracleDBBinding” enableBizTalkCompatibilityMode=”true” useAmbientTransaction=”false” (/&gt)
        Set End Point TragetNamespace to YourTargetNameSpace

        I had send port messages remain ready to run state before while passing thousands of messages through. I restarted host instance tried small and less frequency messages.

        Thank You,
        TenaliNaga.

      • Alejo March 13, 2011 at 10:26 pm

        Hi Tenalinaga

        I solved the problem

        Your contribution was very helpful.

        I configure the “Resolver” as shown below:

        Action={My action}
        Target Namespace={My namespace}
        Transport Location= oracledb://{database}/
        Transport Name=WCF-Custom
        Endpoint Configuration=
        BindingConfiguration=

        BindingType=
        oracleDBBinding

        FromAddress=
        oracledb://{database}/

        Password=
        {My Password}

        PropagateFaultMessage=
        true

        UserName=
        {My user}

        Thank for you Help

  4. Saboor Miraj Abidi June 28, 2011 at 3:31 am

    Hi Tenalinaga,

    Your post was very useful. I used the same approach to retrieve data from a SP (within a package) that returns 2 weak ref cursors.

    However, i face an absurd issue – while i am getting no errors in the response yet, only 1 cursor is returning with valid values while the other cursor contains no data. I executed the same SP directly and it returned the response as expected. I am guessing that it might be because of the large size of this cursor, could it be the reason? If yes, then do i need to set some specific binding properties?

    Any pointers from you would be highly appreciated as i’ve been stuck in this issue for quite sometime. 😦

    Thanks & Regards,
    Saboor

    • tenalinaga June 28, 2011 at 1:42 pm

      Hi Saboor,

      Thanks. I extracted data from multiple cursors within single SP call. Did not remeber running into any issuses.

      I would check if data is loading into cursors. which you already checked. There should be to two select into cursor statements.

      Also Data is not huge, in all cases I worked with. How large is the data and could you try with small record set. Data size should not be problem too..

      Thank You,
      Naga.

  5. nam August 4, 2011 at 9:50 am

    hi,
    I am not able to see any tables under the Schema(like under HR in this sample ),,Is there any permissions required on the schema level objects so that the tables are visible under that schema to generate schemas in wcf adapter biztalk ?

  6. http://tinyurl.com/gamaneal56923 January 13, 2013 at 2:12 pm

    This post, “BizTalk WCF-Oracle Adapter | Tenalinaga’s Blog” was in fact great. I’m impressing out a reproduce to present to my personal pals. Many thanks,Willa

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: