none
Polling Oracle (w/ Procedure) using Biztalk Adapter pack V2 RRS feed

  • Question

  • I'm having trouble getting this woking. I've walked through this:
    http://msdn.microsoft.com/en-us/library/dd788064(BTS.10).aspx

    My issue is with the polling statement (I think). I guess the Microsoft example would be more helpful if it covered the actual stored procedure itself versus assuming how it was done.

    When I attempt to replicate what is done in the example I get:
    "Reason: "System.NotSupportedException: Specified method is not supported."

    I'm not finding any good examples (including oracle procedures) for polling an oracle DB using a procedure. I need to query the DB, update the status of some rows to have an "in process" flag and return the dataset to biztalk for processing (two statements, unfortunately going to require a procedure since I can't pass two statements to the PollingStatement)
    I'll use a post-poll update to clear the "In-process" rows, this is easy.


    Does anyone have experience with good references or have an example that they could share?
    Wednesday, January 13, 2010 7:29 PM

Answers

  • the polling statement had to be corrected to:
    <GET_ITEMS xmlns="http://Microsoft.LobServices.OracleDB/2007/03/TICCRM/Package/MH_TEMP_PK1">
      <INRECS>OPEN ? FOR SELECT * FROM TICCRM.TTIITM001100_COPY WHERE REFLAG = 1;</INRECS>
    </GET_ITEMS>
    the semi-colon (while not on the microsoft examples) is required. Once I had his corrected, I just had to fix the routing issue (had selected the wrong schema on my orchestration.

    I'm now polling oracle using the WCF-Custom adapter from biztalk.

    Thanks!
    • Marked as answer by MatthewTH Monday, January 18, 2010 8:00 PM
    Monday, January 18, 2010 7:59 PM

All replies

  • Hi,

    This MSDN page is for Oracle EBS adapter, but the flow remains the same, except for the name and the namespace. I hope that this will help you:

    http://msdn.microsoft.com/en-us/library/dd788517(BTS.10).aspx

    Thanks,
    Manas
    Friday, January 15, 2010 4:45 AM
  • That's actually the exact same article in two places.
    Friday, January 15, 2010 2:23 PM
  • Sorry for that - I meant to  point you to this: http://msdn.microsoft.com/en-us/library/dd788293(BTS.10).aspx This has a sample for Oracle EBS adapter for polling a SP. The steps would remain the same for DB adapter.

    Thanks,
    Manas
    Monday, January 18, 2010 10:48 AM
  • Most certaily my fault for not specifying. I'm on BizTalk 2006 R2. I can't open the PollingUsingStoredProc.sln without installing BizTalk 2009 to get the developer components for Visual Studio 2008.

    Regardless, it's the same example as the other two "walk-through" style examples and includes the stored procedure. I'm going to see if I can figure it out using this information and just reading the binding file.

    I'll post back either way.
    Monday, January 18, 2010 2:21 PM
  • Found the stored procedure in the sample. Created my own using my table:

    CREATE or REPLACE PACKAGE mh_temp_pk1 AS
     TYPE item_ref_type IS REF CURSOR RETURN TICCRM.TTIITM001100_COPY%ROWTYPE;
     PROCEDURE get_items(inrecs IN item_ref_type, outrecs OUT item_ref_type);
    END mh_temp_pk1;
    
    CREATE or REPLACE PACKAGE BODY mh_temp_pk1 AS
     PROCEDURE get_items(inrecs IN item_ref_type, outrecs OUT item_ref_type) IS
     BEGIN
      outrecs := inrecs;
     END get_items;
    END mh_temp_pk1;
    

    using the following as the polling statement:

    <GET_ITEMS xmlns="http://Microsoft.LobServices.OracleDB/2007/03/TICCRM/Package/MH_TEMP_PK1"><INRECS>OPEN ? FOR SELECT * FROM TICCRM.TTIITM001100_COPY WHERE REFLAG = 1</INRECS></GET_ITEMS>

    Now I get a warning in the app log and no activity in HAT:

    The adapter "WCF-Custom" raised an error message. 
    Details "Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-06550: line 3, column 73:
    PL/SQL: ORA-00933: SQL command not properly ended
    ORA-06550: line 3, column 17:
    PL/SQL: SQL Statement ignored --->

    I can't run the sample itself, but I see no differences between it and my proof of concept. Anyone got any ideas?

    Monday, January 18, 2010 4:46 PM
  • the polling statement had to be corrected to:
    <GET_ITEMS xmlns="http://Microsoft.LobServices.OracleDB/2007/03/TICCRM/Package/MH_TEMP_PK1">
      <INRECS>OPEN ? FOR SELECT * FROM TICCRM.TTIITM001100_COPY WHERE REFLAG = 1;</INRECS>
    </GET_ITEMS>
    the semi-colon (while not on the microsoft examples) is required. Once I had his corrected, I just had to fix the routing issue (had selected the wrong schema on my orchestration.

    I'm now polling oracle using the WCF-Custom adapter from biztalk.

    Thanks!
    • Marked as answer by MatthewTH Monday, January 18, 2010 8:00 PM
    Monday, January 18, 2010 7:59 PM
  •  

    Hi Matthew,

     

    I am looking for a similar solution please could you explain your solution in little more detail.

     

    I followed the same article and generated a schema for polling, created simple orchestration and specified a polling statement similar to the one you specified.

     

    The but the runtime throws an error saying Polling statement should be a SELECT statement.

     

    Thanks

     

    Shiva S

     

     

     


    Shiva Sadayan
    Monday, March 28, 2011 3:00 PM