Initiate BizTalk process on Oracle table column value change RRS feed

  • Question

  • Hi Team,

    I have a requirement wherein i need to connect to Oracle database.

    In oracle database there is a table: Processing_Status with column IsProcessing, its value changes from N to Y.

    My Biztalk application should run whenever there is a change in column value from N To Y. Post that I have to fetch data from different database and in the end i have to update the column value back to N.

    Thursday, July 21, 2016 11:17 AM


All replies

  • Hi,

    It depends on which approach best fits to your requirement based on certain conditions, you can decide

    Out of box BizTalk provide two adapter which you can utilize to achive polling functionality from Oracle.

    1) WCF-Custom

    2) WCF-OracleDB

    please refer the following article for more reference.


    Hope this Helps!!!!

    Please Mark as Answered if you satisfy with Reply.

    • Proposed as answer by vikas.a.mehta Thursday, July 21, 2016 11:35 AM
    • Unproposed as answer by RamanDubey Thursday, July 21, 2016 11:48 AM
    Thursday, July 21, 2016 11:35 AM
  • How about Receiving Database Change Notifications. can I use this for my requirement?
    Thursday, July 21, 2016 11:46 AM
  • John/Rachit/Arindam, Please help!
    Thursday, July 21, 2016 11:55 AM
  • You can write a trigger in oracle so that if there is any update it writes a message to queue or file location where Biztalk will be pulling data from.

    Refer: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7004.htm

    Rachit Sikroria (Microsoft Azure MVP)

    Thursday, July 21, 2016 1:01 PM
  • Hello,

    Create a WCF Oracle Receive location -

    In Transport properties - Set your Pooled Data available statement (SELECT * from table where change in column value = Y)

    Option 1 : Call your Orchestration using call orch shape  

    Option 2 : Subscribe to that published message when the column value changed to your process.

    Thursday, July 21, 2016 1:19 PM
  • Hi Raman

    Do you need absolutely real-time updates from Oracle?

    Note that if you are okay with near real-time updates, the simplest way to implement this scenario is using a Polling ReceiveLocation - you can set the Polling Interval to a low enough value for your need.

    The other thing is - do you own the above database? Ideally, with these kind of operations, your IsProcessing flag should support 3 states - N(Not Processed), Y(Processed), P(In Progress). If you can have this, the entire solution will be pretty simple to implement -

    1. Select the rows to process in PollingStatement - Select * from Processing_Status where IsProcessing = 'Y'.

    In PostPollStatement, set these rows to In Progress status (IsProcessing  = 'P') as - 

    UPDATE Processing_Status SET IsProcessing = 'P' WHERE IsProcessing = 'Y'

    2. In your orchestration receive the above result set (say, InputMsg) and perform the operations on the different database using a SendPort.

    3. Finally, from the records in InputMsg from step2., update the IsProcessing = 'N' for those records using another SendPort.

    Thanks Arindam

    Thursday, July 21, 2016 2:31 PM
  • Hi,

    You can use a oracle receive port with WCF-Oracle transport type and under the PolledDataAvailablestatement proiperty write a Query whihc will check for this value change, once that is satisfied you can write an another query for polling data in PollingStatement property. Please refer the image below.


    Regards Pushpendra K Singh

    • Proposed as answer by SOS 111 Thursday, July 21, 2016 5:09 PM
    Thursday, July 21, 2016 2:43 PM