Polling Oracle Database Using Stored Procedures, Functions, or Packaged Procedures and Functions

Posted on by

 

This document shows the step to configure a polling operation using Oracle Function or Packaged Procedure and Functions.

Please refer to MSD article for additional detail (http://msdn.microsoft.com/en-us/library/dd788064.aspx).

Procedure

Open Visual Studio, create a new BizTalk project and generate the polling schema using Consume Adapter Service wizard.

clip_image002

Click on “Configure”.

clip_image003

Specify a PollingId as Polling Unique Identifier.

clip_image004

Leave the following form with default settings.

clip_image005

Connect to the database, select “Service (inbound operation)” and choose the Oracle function.

clip_image006

Edit binding file in order to set the following properties:

1. Receive port name

2. Receive location name

clip_image007

3. If BizTalk host is Trusted you should set host trusted equal to true.

clip_image008

From BizTalk Administration import the generated binding file.

clip_image009

If needed, rename the receive port and enable routing for failed messages.

clip_image011

Rename the receive location.

clip_image012

Click on Configure and set the following properties:

PolledDataAvailableStatement

In this example, we use the Oracle function CCM_ADT_PKG.CCM_COUNT, which require a string parameter.

SELECT CCM_ADT_PKG.CCM_COUNT('A02') FROM DUAL

PollingAction

From Visual Studio open the schema “schOracleA02CCM.PollingPackage.CCM_ADT_PKG.xsd” and find for the “action” element.

clip_image013

Copy the url “http://Microsoft.LobServices.OracleDB/2007/03/CCM/PollingPackage/CCM_ADT_PKG/CCM_BIZTALK_ADT_2” and set the PollingAction property.

PollingStatement

From visual studio, generate a XML instance for the schema “schOracleA02CCM.Package.CCM_ADT_PKG.xsd”.

clip_image014

Modify XML content setting up the right parameters.

<ns0:CCM_BIZTALK_ADT_2 xmlns:ns0="http://Microsoft.LobServices.OracleDB/2007/03/CCM/Package/CCM_ADT_PKG">
    <ns0:TIPO_MESSAGGIO>A02</ns0:TIPO_MESSAGGIO>
    <ns0:FL_ELABORAZIONE>0</ns0:FL_ELABORAZIONE>
</ns0:CCM_BIZTALK_ADT_2>

Set Polling Interval to 30 seconds and user name and password.

clip_image015

3 thoughts on “Polling Oracle Database Using Stored Procedures, Functions, or Packaged Procedures and Functions”

  1. Pingback: Polling oracle records from biztalk based using parameters

    • Typically the polling statements are not parametric, or at least the parameters can be specified in the receive location.
      You can however activate an orchestration that sets the parameters and uses a send-receive port to access Oracle.

      Regarding to monitoring, if I understand correctly what you mean, it is enought to enable the tracking in the receive port and see the body of the message through the administrative console.

      Reply ↓

      • Thank you for the Post. It really helped me to overcome few issues I was trying to overcome.

Leave a comment