none
Wcf-Oracle receive location stops polling after "For Update" is added, no exception is caught. RRS feed

  • Question

  • Hi all,

       I installed "Microsoft BizTalk Adapter Pack 2010" on BizTalk 2010.

       I created a receive location to extract records from Oracle tables with setting:

    PolledDataAvailableStatement: SELECT 1 FROM DUAL

    PollingStatement: Select * from [table] for update

    After enabling the receive location, it doesn't return any record back, although no error is in the event log, this always happens with or without PostPollStatement.

    But if I change the PollingStatement to "Select * from [table]", records are returned with no problem.

     

    Is it related to permission or what? I have no issue to run the same query on "Oracle SQL Developer" tool with the same credential. Googled quite a while, found nobody came across the same issue.

     

    Thank you.

     

    Linda

     

     

     

     

     


    Linda
    Wednesday, October 19, 2011 6:49 PM

Answers

  • Hi Linda,

     

    Please note that “For Update” places a lock on the rows that are being fetched in the Select statement. Based on your PollingStatement (Select * from [table] for update), you are selecting all the records from the table and placing a lock on all of them. If you are able to fetch data using this statement without the “For Update” statement, then you might be having some deadlock issues.

     

    Can you please provide the EXACT values for the following properties? Yes you can obfuscate your DB artefact names :)

     

    • PolledDataAvailableStatement
    • PollingAction
    • PollingInterval
    • PollingStatement (If you are calling a stored procedure, then can you please also briefly describe the logic used in the stored procedure)
    • PollWhileDataFound
    • PostPollStatement (If you are calling a stored procedure, then can you please also briefly describe the logic used in the stored procedure)
    • UseAmbientTransactions
    • TransactionIsolationLevel (If you don’t know how to find this property, then check this link http://msdn.microsoft.com/en-us/library/dd787944(v=bts.10).aspx)
    • TransactionTimeout (If you don’t know how to find this property, then check this link http://msdn.microsoft.com/en-us/library/dd787944(v=bts.10).aspx)

     

    Regards,

    Zia


    Hope this helps. Zia Saeed | Don’t forget to mark the post(s) as “Answered” that answered your question
    Wednesday, October 26, 2011 2:11 PM

All replies

  • What is your post poll statement?
    -- Please mark as answered if this answers your question.
    Thursday, October 20, 2011 3:33 AM
  • The postpoll statement is as simple as: Update tablename set ColumnName='20', I test it in Oracle SQL Developer to make sure the datatype matches.
    Linda
    Thursday, October 20, 2011 1:22 PM
  • Hi,

    Just did a small test using a WCF Custom port with OracleEBSBinding and it worked for me. I had the auto generated receive location and the send port with a filter 

    BTS.ReceivePortName == <<YOUR_RECEIVE_PORTNAME>>
    

    I am using the a similar post poll statement like you mentioned in your post and works well.

    A few things that you can check, make sure the PollingAction is similar to the metadata. Also you can try setting the property Disable Location on Failure , Suspend request message on failure and Include Exception Details in Faults under Messages Tab in WCF Custom Transport Properties.


    Regards,
    Bali
    MCTS: BizTalk Server 2010,BizTalk Server 2006 and WCF
    My Blog:dpsbali-biztalkweblog
    -----------------------------------------------------
    Mark As Answer or Vote As Helpful if this helps.
    • Proposed as answer by DPS Bali Friday, October 21, 2011 2:50 PM
    Friday, October 21, 2011 2:12 PM
  • Thanks for reply, I don't have "PollingAction" configured, it says that "PollingAction" is optional. I could poll data without "PollingAction" when "For Update" wasn't set.

    Can you share what is the "PollingAction" in your auto-generated receive location? (I cannot make a binding file in VS, cause for some reasons I don't see tables under "Service(inbound operations)", only Procedures/Functions/Packages are available.

     

    Thank you.


    Linda
    Friday, October 21, 2011 2:36 PM
  • If you open up your auto-generated schema then you will have a following documentation segment.

     

    <xs:documentation>
      <doc:action xmlns:doc="http://schemas.microsoft.com/servicemodel/adapters/metadata/documentation">Tables/Poll/SCHEMA_NAME/TABLE_NAME</doc:action> 
      </xs:documentation>
    


    Use Tables/Poll/SCHEMA_NAME/TABLE_NAME in your polling Action and try. Hope this helps.

    Also you can use the OracleESBBinding when you are auto generating the Schema,

    that gave me the schema and binding file.

    Did a post on this topic.


    Regards,
    Bali
    MCTS: BizTalk Server 2010,BizTalk Server 2006 and WCF
    My Blog:dpsbali-biztalkweblog
    -----------------------------------------------------
    Mark As Answer or Vote As Helpful if this helps.

    • Proposed as answer by DPS Bali Friday, October 21, 2011 2:50 PM
    • Edited by DPS Bali Saturday, October 22, 2011 7:15 AM
    Friday, October 21, 2011 2:43 PM
  • Linda,

    As side note you can look at my experience with BizTalk Server Adapter Pack 2010 polling Oracle 11g XE.

    HTH

    Regards,

    Steef-Jan Wiggers
    MVP & MCTS BizTalk Server 2010
    http://soa-thoughts.blogspot.com/
    If this answers your question please mark it accordingly


    BizTalk
    Friday, October 21, 2011 3:01 PM
    Moderator
  • Hi Linda,

     

    Please note that “For Update” places a lock on the rows that are being fetched in the Select statement. Based on your PollingStatement (Select * from [table] for update), you are selecting all the records from the table and placing a lock on all of them. If you are able to fetch data using this statement without the “For Update” statement, then you might be having some deadlock issues.

     

    Can you please provide the EXACT values for the following properties? Yes you can obfuscate your DB artefact names :)

     

    • PolledDataAvailableStatement
    • PollingAction
    • PollingInterval
    • PollingStatement (If you are calling a stored procedure, then can you please also briefly describe the logic used in the stored procedure)
    • PollWhileDataFound
    • PostPollStatement (If you are calling a stored procedure, then can you please also briefly describe the logic used in the stored procedure)
    • UseAmbientTransactions
    • TransactionIsolationLevel (If you don’t know how to find this property, then check this link http://msdn.microsoft.com/en-us/library/dd787944(v=bts.10).aspx)
    • TransactionTimeout (If you don’t know how to find this property, then check this link http://msdn.microsoft.com/en-us/library/dd787944(v=bts.10).aspx)

     

    Regards,

    Zia


    Hope this helps. Zia Saeed | Don’t forget to mark the post(s) as “Answered” that answered your question
    Wednesday, October 26, 2011 2:11 PM
  • Hi Zia, thank you very much for taking time to help me out, I appreciated it so much.

    While providing the properties you asked me, I found out the root cause:

    I did not have oracleDBAdapterInboundTransactionBehavior added to ServiceBehavior on "Behavior" tab,  so that TransactionIsolationLevel and transaction Timeout were never set up. --- I think that's what was causing the problem, as once I had them configured, the receive location started polling the records while updating the column as I expected.

     

    Just for future reference in case someone else hits the same issue, I pasted the receive location configuration:

  • PolledDataAvailableStatement
  •     SELECT 1 FROM DUAL
  • PollingAction
  •      (blank -- not set)
  • PollingInterval
  •      500
  • PollingStatement (If you are calling a stored procedure, then can you please also briefly describe the logic used in the stored procedure)
  •      SELECT *  FROM TableName1 FOR UPDATE
  • PollWhileDataFound
  •      False
  • PostPollStatement (If you are calling a stored procedure, then can you please also briefly describe the logic used in the stored procedure)
  •     update TableName1 set FieldName1= '90'
  • UseAmbientTransactions
  •      True (I tried false too, it didn't help)
  • TransactionIsolationLevel (If you don’t know how to find this property, then check this linkhttp://msdn.microsoft.com/en-us/library/dd787944(v=bts.10).aspx)
  • TransactionTimeout (If you don’t know how to find this property, then check this linkhttp://msdn.microsoft.com/en-us/library/dd787944(v=bts.10).aspx)
  •     Add oracleDBAdapterInboundTransactionBehavior to ServiceBehavior, then leave them as default.

  • Linda
Wednesday, October 26, 2011 3:03 PM
  • Linda, I am glad to hear that you have solved the mystery :). Good luck. Zia
    Hope this helps. Zia Saeed | Don’t forget to mark the post(s) as “Answered” that answered your question
    Wednesday, October 26, 2011 6:38 PM