Welcome Sign in
BizTalk Server Developer Center
Click to Rate and Give Feedback
MSDN
MSDN Library
BizTalk Server
Operations
Using Adapters
Getting Started
Tutorials
 Tutorial: Using the BizTalk Adapter...
Tutorial: Using the BizTalk Adapter for Oracle to Retrieve Data from an Oracle Database

This topic was last updated on: December 5, 2007

The BizTalk Adapter for Oracle can be used to execute a query against an Oracle table and return the results of the query. This walkthrough describes an SDK sample that illustrates this functionality.

  • The appropriate Oracle client must be installed on your BizTalk Server. See BizTalk Adapter for Oracle Database System Requirements for more information on the supported versions of the Oracle client.
  • It is recommended that you install the Oracle Enterprise Manager or the Oracle SQL*Plus utility on the BizTalk Server so that you can verify the results of the walkthrough. These tools can be installed with the Oracle client.
  • This tutorial requires that you have an Oracle Database with the SCOTT schema installed with the table EMP. The scott.sql script in a standard Oracle Database installation that creates the SCOTT schema can be found in the <ORACLE_HOME>\rdbms\admin\ directory and can be executed using the Oracle SQL*Plus utility. For information about using the SQL*Plus utility see the Oracle documentation.
    Note
    The SCOTT schema is installed with the table EMP by default when you create a new Oracle database.

  • Visual Studio 2005 must be installed in order to build and deploy the sample.

This sample picks up an XML file from a folder, sends the file to an orchestration, and then uses the BizTalk Adapter for Oracle to execute a query against a table in the Oracle database. The result of the query is written to an XML file.

This sample was designed in Visual Studio 2005 and was created to illustrate basic functionality using the BizTalk Adapter for Oracle with a BizTalk orchestration.

The sample is located in the following folder:

\Program Files\Microsoft BizTalk Adapters for Enterprise Applications\Oracle(r) Database\Sdk\OracleDBTwoWaySend

The following table shows the files in this sample and describes their purpose.

Runtime Project Filename Runtime Project File Description

TwoWaySend.btproj,

TwoWaySend.sln

Project and solution files for the application.

EMPService.xsd,

EMPService_1.xsd,

EMPService_2.xsd

Schema files for the application.

Note
The adapter schema files in the project were originally created using the Add Adapter Metadata Wizard. For more information on the Add Adapter Metadata Wizard see the topic "How to Add Adapter Metadata to a BizTalk Project" in the BizTalk Server 2006 documentation.

OracleDBTwoWaySend.odx

The orchestration used by the application.

OracleDBTwoWaySend.snk

The strong naming key file.

  • Use the Oracle Enterprise Manager or the Oracle SQL*Plus utility to create the EMP table on an Oracle database if this table does not already exist. The scott.sql script in a standard Oracle database installation that creates the SCOTT schema can be found in the <ORACLE_HOME>\rdbms\admin\ directory and can be executed using the Oracle SQL*Plus utility. For information about using the SQL*Plus utility see the Oracle documentation.

  1. Launch the BizTalk Server 2006 Administration Console. Click Start, Programs, Microsoft BizTalk Server 2006, BizTalk Server Administration.

  2. In the BizTalk Server 2006 Administration Console, expand BizTalk Server 2006 Administration, expand BizTalk Group, expand Platform Settings, and then click Adapters.

  3. Right-click Adapters and point to New, Adapter… to display the Adapter Properties dialog.

  4. Enter a value for the Name field, for example OracleDb.

  5. Select Oracle(r) Database from the list of adapters available in the Adapter dropdown and click OK.

  1. Launch the ODBC Data Source Administrator. Click Start, Settings, Control Panel, Administrative Tools, Data Sources (ODBC).

  2. In the ODBC Data Source Administrator click the System DSN tab and click the Add button.

  3. Select the Oracle driver that was installed with the Oracle client from the list of available drivers. For example, if you installed the Oracle database 10.2.0.1 client, the name of the driver might be Oracle in Ora10201Client.

  4. Click Finish to display the Oracle ODBC Driver Configuration dialog.

  5. Enter a value for the Data Source Name field, this name must match the named defined as the TNS Service Name configured for the Oracle database, for example OraServer.BizTalk. For information about the TNS Service Name, see the Oracle documentation.

  6. Enter the appropriate value for the TNS Service Name field; this entry must match the name defined for the TNS Service Name configured for the Oracle database, for example OraServer.BizTalk.

  7. Enter the value scott into the User ID field. This user account is associated with the EMP database by default.

  8. Click the Test Connection button to verify the connection to the database. When prompted for credentials enter the appropriate credentials for the EMP table into the Password field and click OK. A dialog box will be displayed indicating whether the test succeeded or failed. This connection must succeed in order to use the BizTalk Adapter for Oracle. If the test connection fails there should be information displayed in the dialog box that indicates the reason for the failure.

    Note
    The default password for the EMP table associated with the scott schema is tiger.

  1. In the BizTalk Server 2006 Administration Console, expand BizTalk Server 2006 Administration, expand BizTalk Group, expand Applications, expand BizTalk Application 1, and click Send Ports.

  2. Right-click Send Ports and point to New, Static Solicit-Response Send Port… to display the Send Port Properties dialog.

  3. Enter a value for the Name field, for example OracleDBTwoWaySP.

  4. Select the Oracle database adapter from the list of available adapters in the Type dropdown box and click the Configure button to display the adapter Transport Properties dialog box.

    Note
    This value is the name that was specified when the Oracle database adapter was created in the BizTalk Server 2006 Administration Console.

  5. Enter the following values for the Adapter Required Properties:

    Property Value

    Password

    Password associated with the scott account. By default this password is tiger.

    PATH

    Path to the BIN directory installed with the Oracle client, for example C:\Oracle\product\10.2.0\client_1\BIN

    Service name

    Name defined for the System DSN that is used to connect to the Oracle database. This should match the name defined for the TNS Service Name configured for the Oracle database, for example OraServer.BizTalk.

    User name

    scott

  6. Click OK.

  7. Select the XMLTransmit pipeline from the list of pipelines available in the Send pipeline dropdown.

  8. Select the XMLReceive pipeline from the list of pipelines available in the Receive pipeline dropdown and click OK.

  9. Right-click the send port and click Start to enlist and start the send port.

  1. Create a target folder to be used by the send port, for example C:\Files\Out.

  2. In the BizTalk Server 2006 Administration Console, expand BizTalk Server 2006 Administration, expand BizTalk Group, expand Applications, expand BizTalk Application 1, and click Send Ports.

  3. Right-click Send Ports and point to New, Static One-Way Send Port… to display the Send Port Properties dialog.

  4. Enter a value for the Name field, for example OracleDBTwoWayFileSP.

  5. Select FILE from the list of available adapters in the Type dropdown box and click the Configure button to display the adapter Transport Properties dialog box.

  6. Enter the location of the folder that you created earlier for the Destination Folder property and click OK.

  7. Select the XMLTransmit pipeline from the list of pipelines available in the Send pipeline dropdown and click OK.

  8. Right-click the send port and click Start to enlist and start the send port.

  1. In the BizTalk Server 2006 Administration Console, expand BizTalk Server 2006 Administration, expand BizTalk Group, expand Applications, expand BizTalk Application 1, and click Receive Ports.

  2. Right-click the Receive Ports folder and then click New, One-Way Receive Port... to display the Receive Port Properties dialog.

  3. Enter a value for the Name field, for example OracleDBTwoWayFileRP, and click OK.

  1. Create a folder to be monitored by the file receive location, for example C:\Files\In.

  2. Right-click the new receive port and then click New, Receive Location… to display the Receive Location Properties dialog.

  3. Enter a value for the Name field, for example OracleDBTwoWayFileRL.

  4. Select FILE from the list of available adapters in the Type dropdown box and click the Configure button to display the adapter Transport Properties dialog box.

  5. Enter the location of the folder that you created earlier for the Receive Folder property and click OK.

  6. Select XMLReceive from the list of available pipelines in the Receive pipeline dropdown box and click OK.

  7. Right-click the receive location and click Enable.

  1. Launch Visual Studio 2005 and open TwoWaySend.sln. Click File, Open, Project/Solution… to display the Open Project dialog.

  2. Browse to the TwoWaySend.sln file, click to select this file and click Open to open the solution that contains the sample project.

  3. Click the View menu and select Solution Explorer to display the Solution Explorer.

  4. Double-click the EMPService_1.xsd file in the Solution Explorer to open it.

  5. Right-click the Schema node of EMPService_1.xsd and select the Properties menu option to display the properties for the schema.

  6. Edit the Target Namespace property to use the appropriate values for the adapter name and the System DSN/TNS Service name, for example the Target Namespace property should read as follows:

    http://schemas.microsoft.com/[OracleDb://OraServer.BizTalk/SCOTT/Tables/EMP]

    Where OraServer.BizTalk is the name defined for the System DSN and the TNS Service Name configured for the Oracle database.

    Important
    If the configured value for Target Namespace does not match the namespace specified in the input document instance then a routing failure will occur when the input document instance is processed by BizTalk Server.

  1. Right-click the Schema node of EMPService_1.xsd and select the Properties menu option to display the properties for the schema.

  2. Select Query from the list of available nodes in the Root Reference dropdown box. This should be done so that when you generate a sample document instance it will be generated from the Query node of the schema.

  3. Right-click EMPService_1.xsd in Solution Explorer and click Properties to display the Property Pages dialog.

  4. Click to select the Output Instance Filename option under the General section of the Property Pages dialog.

  5. Click the ellipses button (…) to display the Select Output File dialog.

  6. Specify a folder and name for the output file instance, for example C:\instance.xml and click Save, and then click OK.

    Note
    Do not specify the location of the folder that was specified for the file receive location here.

  7. Right-click EMPService_1.xsd in Solution Explorer and click Generate Instance to generate a document instance in the specified location.

  8. Right-click the Schema node of EMPService_1.xsd and select the Properties menu option to display the properties for the schema.

  9. Select (Default) from the list of available nodes in the Root Reference dropdown box.

  1. Open the generated document instance in a text editor such as Notepad and edit the contents of the document instance as indicated. This removes the filter (functionally equivalent to a WHERE clause) from the query executed against the Oracle database and modifies the maximum number of rows that can be returned by the query:

    <ns0:Query xmlns:ns0="http://schemas.microsoft.com/[OracleDb://OraServer.BizTalk/SCOTT/Tables/EMP]">
  <ns0:Filter></ns0:Filter>
  <ns0:MaxRows>100</ns0:MaxRows>
</ns0:Query>
    Note
    In the example above, Oraserver.BizTalk is a placeholder for the name of the System DSN as viewed in the ODBC Data Source Administrator.

  2. Save the modified document instance.

  1. Right-click the TwoWaySend project in Solution Explorer and click Properties to display the Property Pages dialog for the project.

  2. Click the Deployment option under Configuration Properties in the left pane of the Property Pages dialog.

  3. Enter the appropriate values for the Server property and the Configuration Database property under BizTalk Group in the right pane of the Property Pages dialog and click OK.

  4. Right-click the TwoWaySend project in Solution Explorer and click Deploy to build the project and deploy the assembly to the BizTalk Server configuration database.

  1. In the BizTalk Server 2006 Administration Console, expand BizTalk Server 2006 Administration, expand BizTalk Group, expand Applications, expand BizTalk Application 1, and click Orchestrations.

  2. Click the Refresh button in the BizTalk Server Administration Console toolbar or press the F5 key on your keyboard to refresh the BizTalk Server Administration Console view.

  3. Double-click the orchestration to display the Orchestration Properties dialog.

  4. Click Bindings in the left pane of the dialog to display the Bindings options for the orchestration.

  5. Specify the appropriate values for the binding options, for example:

    Parameter Value

    Host

    BizTalkServerApplication

    FileReceivePort

    OracleDBTwoWayFileRP

    OracleDBSendPort

    OracleDBTwoWaySP

    FileSendPort

    OracleDBTwoWayFileSP

  6. Click OK.

  • In the BizTalk Server 2006 Administration Console, right-click the orchestration and click Start to enlist and start the orchestration.

  • Copy the document instance that was created earlier to the folder that the file receive location is configured to monitor.

  • Open the folder that the file send port is configured to send to and verify that an output document was generated. This file should contain the results of the query that was processed by the BizTalk Adapter for Oracle.

The following sequence of events occurs if the document instance is processed successfully:

  1. The File adapter retrieves the file from the folder and publishes it to the MessageBox as a BizTalk message.
  2. The orchestration subscribes to this published message so the BizTalk Messaging Engine activates an instance of the orchestration and sends the message to the orchestration instance.
  3. The orchestration instance publishes the message back to the MessageBox.
  4. The solicit-response send port subscribes to this published message so the BizTalk Messaging Engine sends the message to the Oracle send port.
  5. The send port hands the message to the BizTalk Adapter for Oracle.
  6. The BizTalk Adapter for Oracle executes a SELECT statement against the specified table using the parameters defined in the input file.
  7. The BizTalk Adapter for Oracle returns the results of the SELECT statement as the response message for the solicit-response port in the orchestration.
  8. The orchestration publishes the result set to the MessageBox.
  9. The File send port subscribes to this message so BizTalk sends the message to the File adapter.
  10. The File adapter writes the message containing the result set to the designated output folder.
Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
Processing
© 2008 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Page view tracker