Tutorial: Using the BizTalk Adapter for Oracle to Insert Data Into an Oracle Database

This topic has not yet been rated - Rate this topic

The BizTalk Adapter for Oracle can be used to update an Oracle table with information received from a trading partner or internal application. This walkthrough describes an SDK sample that illustrates this functionality.

Prerequisites

---

• 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.
  

What This Sample Does

---

This sample picks up an XML file from a folder, sends the file to an orchestration, and then uses the Oracle Adapter to execute an INSERT statement on the Oracle database to insert data from the XML file into a table.

How This Sample is Designed and Why

---

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.

Where to Find This Sample

---

The sample is located in the following folder:

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

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

Runtime Project Filename	Runtime Project File Description
OneWaySend.btproj, OneWaySend.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.
OracleDBOneWaySend.odx	The orchestration used by the application.
OracleDBOneWaySend.snk	The strong naming key file.

How to Use This Sample

---

Create the EMP table from the SCOTT sample schema

---

• 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.

Create a new instance of the Oracle(r) Database adapter

---

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.

Create a System DSN to connect to the Oracle database

---

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.

Create a BizTalk 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 Send Ports.
2. Right-click Send Ports and point to New, Static One-Way Send Port… to display the Send Port Properties dialog.
3. Enter a value for the Name field, for example OracleDBOneWaySP.
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 theTNS Service Name configured for the Oracle database, for example OraServer.BizTalk.
   User name	scott

6. Click OK.
7. Select the XML Transmit 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.

Create a file receive 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 OracleDBOneWayFileRP, and click OK.

Create a file receive location

---

1. Create a folder to be monitored by the file receive location, for example C:\Filesource.
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 OracleDBOneWayFileRL.
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.

Modify the Adapter schema target namespace property

---

1. Launch Visual Studio 2005 and open OneWaySend.sln. Click File, Open, Project/Solution… to display the Open Project dialog.
2. Browse to the OneWaySend.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.

Generate a document instance from the Adapter schema

---

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 Insert 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 Insert 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.

Modify the generated document instance

---

1. Open the generated document instance in a text editor such as Notepad and edit the contents of the document instance to more closely approximate a sample employee record. For example the record below describes an employee named Steve with the job title of Architect:
   <ns0:Insert xmlns:ns0="http://schemas.microsoft.com/[OracleDb://OraServer.BizTalk/SCOTT/Tables/EMP]">
     <ns0:Rows>
       <ns0:Record>
         <ns0:EMPNO>8000</ns0:EMPNO>
         <ns0:ENAME>Steve</ns0:ENAME>
         <ns0:JOB>Architect</ns0:JOB>
         <ns0:MGR>7839</ns0:MGR>
         <ns0:HIREDATE>1999-05-31T13:20:00.000-05:00</ns0:HIREDATE>
         <ns0:SAL>5000</ns0:SAL>
         <ns0:COMM>0</ns0:COMM>
         <ns0:DEPTNO>10</ns0:DEPTNO>
       </ns0:Record>
     </ns0:Rows>
   </ns0:Insert>
   

   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.

Build and deploy the project

---

1. Right-click the OneWaySend 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 OneWaySend project in Solution Explorer and click Deploy to build the project and deploy the assembly to the BizTalk Server configuration database.

Bind and Enlist the orchestration

---

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 MMC 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	OracleDBOneWayFileRP
   OracleDBSendPort	OracleDBOneWaySP

6. Click OK.

Start the orchestration

---

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

Drop a document instance into the folder monitored by the file receive location

---

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

Verify that the table is updated

---

• Use the Oracle Enterprise Manager or the Oracle SQL*Plus utility to verify that the table is updated with the information from the file.

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 will activate an instance of the orchestration and send the message to the orchestration instance.
   
3. The orchestration instance processes the message using the logic specified in the orchestration and publishes the message back to the MessageBox.
   
4. The Oracle send port subscribes to this published message and 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 an INSERT statement to update the target table with the data in the message.