Connecting to the Oracle Database in Visual Studio

To use the Consume Adapter Service BizTalk Project Add-in or the Add Adapter Service Reference Visual Studio Plug-in, you must first connect to the Oracle database. Both the Consume Adapter Service Add-in and the Add Adapter Service Reference Plug-in present a dialog box through which you configure a connection by setting the following:

  • Connection parameters. These are the parameters that are used to build the connection URI. You must specify a data source (Oracle net service name); other parameters are optional.

  • User name password credentials for the Oracle database. These are used to authenticate you on the Oracle database when the connection is established. You must specify a user name and password.

  • Binding properties. Binding properties are optional, and whether you specify them depends primarily on whether you want to target the POLLINGSTMT operation.

Cc185229.Important(en-US,BTS.10).gifImportant
When you want to target the POLLINGSTMT operation, you must specify a valid SQL SELECT statement for the PollingStatement binding property. If you want to change the namespace of the POLLINGSTMT operation then you must specify the PollingId connection parameter. This is because the Oracle Database adapter uses the SQL SELECT statement and the PollingId to generate the strongly-typed metadata that it returns for the POLLINGSTMT operation. You must specify the same value for these parameters at run time. For more information, see Receiving Polling-based Data-changed Messages.

When you configure the connection to the Oracle database, you only have to specify binding properties and connection parameters that are needed to establish the connection and that affect the metadata returned by the Oracle Database adapter for the operations you want to target. However, you might also want to specify values for any additional binding properties and connection parameters that will be used at run time. This is because:

  • The Consume Adapter Service Add-in creates a BizTalk port binding file from the binding properties and connection parameters that you specify when you configure the connection, and adds this file to your project.

  • The Add Adapter Service Reference Plug-in creates an app.config file from the binding properties and connection properties that you specify when you configure the connection, and adds this file in your project directory.

Perform the following steps to connect to an Oracle database using the Consume Adapter Service Add-in or Add Adapter Service Reference Plug-in.

  1. To connect using the Consume Adapter Service Add-in in a BizTalk solution:

    1. Create a BizTalk project using Visual Studio.

    2. Right-click the project in Solution Explorer, point to Add, and then click Add Generated Items.

    3. In the Add Generated Items dialog box, do the following:

      Use this To do this

      Categories

      Click Consume Adapter Service.

      Templates

      Click Consume Adapter Service.

    4. Click Add. The Consume Adapter Service Add-in opens.

    To connect using the Add Adapter Service Reference Plug-in in a programming solution:

    1. Create a project using Visual Studio.

    2. Right-click the project in Solution Explorer, and then click Add Adapter Service Reference. The Add Adapter Service Reference Plug-in opens.

  2. From the Select a binding drop-down list, select oracleDBBinding and click Configure.

  3. In the Configure Adapter dialog box, click the Security tab, and from the Client credential type drop-down list box, select Username and specify the user name and password to connect to the Oracle database. Make sure you adhere to the following considerations when specifying the user name and password to connect to an Oracle database:

    • User name. The Oracle Database adapter preserves the case of the value that you enter for the user name when it opens a connection on the Oracle database. User names on the Oracle database are case-sensitive. You should ensure that you provide Oracle user names to the Oracle Database adapter in the case expected by your Oracle database. Typically, this means that the user name in the SCOTT/TIGER credential should be upper case: "SCOTT".

    • Password. The Oracle Database adapter preserves the case of the value that you enter for the password when it opens a connection on the Oracle database. For release 10g and earlier, passwords on the Oracle system are not case-sensitive.

  4. Click the URI Properties tab, and specify values for the connection parameters. For more information about the connection URI for the Oracle Database adapter, see The Oracle Database Connection URI.

    Cc185229.note(en-US,BTS.10).gifNote
    If the connection parameters contain any reserved characters, you must specify them as-is in the URI Properties tab, that is, without using any escape characters. However, if you specify the URI directly in the Configure a URI field and the connection parameters contain reserved characters, you must specify the connection parameters using proper escape characters.

  5. Click the Binding Properties tab, and then specify values for the binding properties, if any, required by the operations you want to target. For example, if you want to target the POLLINGSTMT operation, you must set the PollingStatement binding property. For more information about binding properties, see Working with BizTalk Adapter 3.0 for Oracle Database Binding Properties.

  6. Click OK.

  7. Click Connect. After the connection is established, the connection status is shown as Connected.

    The following figure shows the Consume Adapter Service Add-in immediately after the connection is established. The graphical user interface is the same for the Add Adapter Service Reference Plug-in.



    Consume Adapter Service dialog box connected
Show: