none
Oracle LOB Adapter polling problem using two BizTalk Servers RRS feed

  • Question

  •  

    Hi Everyone
     
    Part of our BizTalk solution requires polling an Oracle 8i database to check for the existence of records in an interface table which then need to be sucked out.
     
    As you would all be aware in a clustered BizTalk environment BizTalk manages all its internal resources correctly but external resources are another kettle of fish.
     
    This article goes into detail into solving this problem with the SQL Adapter and carefully crafted SQL Stored Procedures with table locks;
    http://adambowron.blogspot.com/2007/02/avoid-deadlocks-using-sql-adapter-and.html
     
    However the issue we have with the Oracle Adapter using it against an Oracle 8i database is that we have to use select statements and don't have the luxury of getting a cursor returned (I have heard this problem is not present in 10g)
     
    Our requirement of the Oracle adapter is to ensure all records are processed once and only once during this polling so that we don't get any missing or duplicate records


    So currently these are our issues;
    Records may be deleted by the Post Poll command before these records are retrieved.  ie; Missing records.
    Records may be processed twice if two receive locations execute the same sql with a short timeframe.  ie; Duplicate records.

     

    The Adapter we are using is the one supplied with the Microsoft Line of Business Adapters.
     
    Has anyone come across this issue and implemented a resolution?

     

    I looked through the forum for similar issues and one recommendation was to use a clustered host however this is (apparently?) not supported with the Oracle LOB Adapter. Another was to disable the receive location on the other server however we would like to avoid this as we may need to perfom somewhere in the vicinity of 20 poll operations given our current requirements and this will cause maintenance issues (beside being a SPOF)


    Many thanks for any assistance

     

    Andrew

    Monday, December 10, 2007 12:55 AM

Answers

  • Geez one week and no bites at all

     

    Anyway we found a solution, Oracle enables table read locks by using a "select for update" syntax such that you can read the rows you want, update them to signify they have been read, and then perform a commit to write the changes back.

     

    No other resource can update the rows while this is being done although they can still be read.

     

    Monday, December 17, 2007 12:38 AM

All replies

  • Geez one week and no bites at all

     

    Anyway we found a solution, Oracle enables table read locks by using a "select for update" syntax such that you can read the rows you want, update them to signify they have been read, and then perform a commit to write the changes back.

     

    No other resource can update the rows while this is being done although they can still be read.

     

    Monday, December 17, 2007 12:38 AM
  • Hi Hatchman

     

    Are you using the Oracle adapter for enterprise applications or the new WCF-based Oracle DB adapter? The new WCF-based Oracle DB adapter, based on ODP.NET, has richer features in terms of datatyping and transactional control. Here is a blog link with more information on the adapter. http://blogs.msdn.com/adapters/archive/2007/10/29/biztalk-oracle-adapter-vs-wcf-based-oracle-db-adapter.aspx

     

    There is now a release candidate version of the BizTalk Adapter Pack. Let us know what you think!

     

    Monday, December 24, 2007 8:24 AM
  • Hi Hatchman,

     

    Thanks for the post. Have exactly the same issue (other than using Oracle 9).

     

    Understand the 'FOR UPDATE' syntax, but you mention that the rows can still be read (but not updated). How do you stop another BizTalk instance taking these rows (and trying to process) if they are currently being processed?

     

    Cheers,

     

    Dan

    Thursday, January 10, 2008 7:08 PM
  • Hi All,

    Yupe, It also happens on my side as well and FOR UPDATE syntax does not seem to kick in with the adapters, I'm still getting the duplicate rows when polling intermittenly.
    The worst part is that I did a quick test :
    1. Create a receive location, poll 50 rows of data, interval 60 seconds, post poll update the status to 'PROCESSING'
    2. Create a send port using file adapter to output xml files and apply the filter using the receive port name
    3. I still found duplicate rows for this even though the poll query only executes less than 10 seconds and the files were generated quite fast.

    I really wish the the current biztalk adapter with ODBC can poll using procedures, I'm stuck with the old adapter now, don't know whether can upgrade to the latest one (management decision) Sad

    Friday, April 11, 2008 9:40 AM
  • We can poll using procedures only when it is wrapped up inside Package .We have tried this and you have to give this command (Call PackageName)  in poll sql statement and in Managed Events we have to select NativeSQL.

    Friday, April 11, 2008 1:43 PM
  • Hi Sanjay,
    Are you referring to BizTalk Oracle Database Adapter which is still using ODBC connection, not the latest one with ODP.Net?
    Because I'm still using the BizTalk Server 2006, not the R2 version.
    I really hope you are, I'm going to test this tomorrow morning Smile
    Thanks man.
    Sunday, April 13, 2008 11:13 AM
  • Hi Sanjay,

    Tested this today, but negative Sad

    Steps :
    1. Create the package below
    2. Set the poll statement as : Call
    rp_jobs_get_pending_jobpkg.RP_JOBS_GET_PENDING_JOB();
    3. Set the managing event : NativeSql
    Result : No rows is being picked up but no errors in the event log

    Alternative :
    3. Set the managing event : Choose
    rp_jobs_get_pending_jobpkg
    Result : Error in the event log : I-OAP0022: Unable to find object describing this table

    I tried to use the package below with procedure inside to get just 1 row from the table, actually I want to retrieve multiple rows if possible, but from what I know, ref cursor is only supported in the new oracle adapter pack with ODP.Net.
    If I can get 1 row each time, that will be good enough from me since at least I can update the status in the poll statement instead of having to rely on post poll statement to update the status to "Processing" status to avoid the next poll to retrieve the same rows.
    Is it because of the return type of the procedure?

    CREATE OR REPLACE PACKAGE rp_jobs_get_pending_jobpkg
    IS
      PJOBID NUMBER;
      PJOBSTATUS VARCHAR(10);

        -- Procedure that retrieves the pending jobs
         PROCEDURE RP_JOBS_GET_PENDING_JOB;

    END;
    /

    CREATE OR REPLACE PACKAGE BODY rp_jobs_get_pending_jobpkg
    IS
        -- Procedure that retrieves the pending jobs
         PROCEDURE RP_JOBS_GET_PENDING_JOB
      AS
      Begin
        Select job_id, job_status into RP_JOBS_GET_PENDING_JOBPKG.PJOBID, RP_JOBS_GET_PENDING_JOBPKG.PJOBSTATUS from JOBS Where rownum = 1;

      End;

    END;
    /

    Thanks.

    Monday, April 14, 2008 4:07 AM