Export (0) Print
Expand All

Executing an Oracle Sample Query

The Oracle system is accessible from a BizTalk Server 2006 system by using the Oracle adapter. This adapter is one of a group of eight line-of-business (LOB) adapters shipped by Microsoft for use with BizTalk Server 2006.

This is the second part of the Oracle lab work. In the first part (Lab 1), you manually accessed and modified data on the Oracle system without the assistance of BizTalk Server 2006 or other Microsoft technologies. In this part (Lab 2), you will create a BizTalk orchestration as part of a Visual Studio 2005 BizTalk project. You will configure ports on this orchestration to use the Oracle adapter to get data from an Oracle system.

Bb226425.note(en-us,BTS.20).gifNote
Microsoft is interested in your feedback about how useful this sample is to you. Please send feedback to docfb@microsoft.com and include the name of the sample in the correspondence.

  • Microsoft BizTalk Server 2006

  • Microsoft Visual Studio 2005

  • Oracle Client software

  • Network connectivity to an Oracle database on another server

  • Microsoft BizTalk Adapters for Enterprise Applications

Bb226425.note(en-us,BTS.20).gifNote
For information about installing and configuring Microsoft BizTalk Adapters for Enterprise Applications, see http://go.microsoft.com/fwlink/?LinkId=56392. This document includes key configuration information for the JD Edwards, PeopleSoft, Oracle, Tibco, and Siebel LOB adapters.

In this lab, you will execute a Get operation against the Oracle system. Specifically you will perform the following tasks:

  • Verify the Oracle prerequisites

  • Set up an Oracle send port in BizTalk Server

  • Create a BizTalk orchestration project

  • Build and deploy the project

  • Test the application and view the XML output

You will use the Oracle database adapter to access the Oracle database from BizTalk Server. This adapter enables the processing of Oracle database schema objects, tables, views, synonyms, procedures, and packages. These are the typical requests and responses executed by an orchestration. The methods available for a schema object are Insert, Update, Delete, Query, and TableChangeEvent. This lab demonstrates how to use the Query method.

Before running a service request, you must create service request and response schemas for the specific Database object. The Add Generated Items/Add Adapter Wizard creates these schemas by directly interrogating the supporting metadata object in Oracle. This lab illustrates the steps required to create schemas for the EMP table and to process a query.

Verifying the Oracle Prerequisites

Before you start creating a BizTalk project for use with the Oracle adapter, you need to be sure the adapter is set up correctly to access Oracle.

  1. Click Start, point to All Programs, point to Microsoft BizTalk Server 2006, and then click BizTalk Server Administration.

  2. In the BizTalk Server Administration console, expand Console Root, expand BizTalk Server 2006 Administration, expand BizTalk Group, expand Platform Settings, and then expand Adapters. Ensure that the Oracle adapter is installed and on the list.

    If the Oracle adapter is not installed, install the BizTalk Adapters for Enterprise Applications (see the earlier "Prerequisites" section). After the adapters are installed, right-click Adapters and then click New - Adapter to install the Oracle adapter. You will have to restart the host instance for this to take effect.

Creating an Oracle ODBC Data Source

For the Oracle adapter to access the Oracle database, you must define an ODBC data source. Each of the LOB adapters uses XML to communicate data requests and results with BizTalk Server 2006. The Oracle adapter uses the ODBC API to call into an Oracle system and execute its data operations.

  1. Click Start, point to All Programs, point to Administrative Tools, and then click Data Source (ODBC)..

    Bb226425.d441403f-e485-453c-b53a-7c41e3ee2e1f(en-us,BTS.20).gif
  2. Click the System DSN tab, and then click Add. This displays the Create New Data Source dialog box.

  3. In the list box, select OraHome92, and then click Finish.

  4. In the Oracle ODBC Driver Configuration dialog box, enter the following values for these fields:

    1. Data Source Name: XXDBPSS (where XX are unique initials)

    2. TNS Service Name: Select DBPSS from the drop-down list

    3. User ID: Enter the user ID you used in Lab 1 when using the SQL Plus tool.

    Bb226425.a6b1e5ba-db95-498d-aded-1d4fb1bc635e(en-us,BTS.20).gif
  5. Click Test Connection. This displays the Oracle ODBC Driver Connect dialog box.

    Bb226425.1da6b9e0-9c20-45f0-a193-fc0586169520(en-us,BTS.20).gif
  6. Enter the password you used in Lab 1 when using the SQL Plus tool, and then click OK. If the test is successful, the Testing Connection dialog box will appear as below.

    Bb226425.4c27315f-4d03-430f-a9ae-d0b753a394a4(en-us,BTS.20).gif
  7. Click OK three times to close the ODBC Data Source Administrator.

Configure BizTalk Send Ports

Now you will verify that the Oracle database adapter is installed and create the Oracle send port.

  1. Click Start, point to All Programs, point to Microsoft BizTalk Server 2006, and then click BizTalk Server Administration.

  2. In the BizTalk Server Administration console, expand Console Root, expand BizTalk Server 2006 Administration, expand BizTalk Group, expand Platform Settings, and then expand Adapters. Ensure that the Oracle adapter is installed and on the list.

    Bb226425.e7f5549a-4866-408d-9581-ca766a685762(en-us,BTS.20).gif

    If the Oracle adapter is not installed, install the BizTalk Adapters for Enterprise Applications (see the earlier "Prerequisites" section). After the adapters are installed, right-click Adapters and then click New - Adapter to install the Oracle adapter. You will have to restart the host instance for this to take effect.

  1. In the BizTalk Server Administration console, expand Console Root, expand BizTalk Server 2006 Administration, expand BizTalk Group, expand Applications, and then expand BizTalk Application 1.

  2. Right-click Send Ports, click New, and then click Static Solicit-Response Send Port. Enter the following values for these fields:

    1. Name: XXOracleDBSendPort

    2. Type: Oracle

    3. Send handler: BizTalkServerApplication

    4. Send pipeline: XMLTransmit

    5. Receive pipeline: XMLReceive

Bb226425.note(en-us,BTS.20).gifNote
We use XX in this lab just to ensure the names are unique. You can use any other letters you want instead of XX, but just make sure you are consistent throughout.

Bb226425.22cec2c7-7af5-40de-9f6c-adb30653c7c0(en-us,BTS.20).gif
  1. Click Configure, and then enter the following property values:

    1. Password: Use the drop-down list and then enter your Oracle password.

    2. Path: c:\oracle\ora92\bin

    3. Service name: XXDBPSS (This is the Oracle Data Source name.)

    4. User Name: PSSUSR

    Bb226425.27114bc6-7f93-40bc-a35f-da7a426f877c(en-us,BTS.20).gif
  2. Click OK twice to close the Send Port Properties dialog box.

    Bb226425.1439198d-deb8-49b0-b1b4-85c0d0d14025(en-us,BTS.20).gif

Creating a BizTalk Orchestration Project

Now you will create a BizTalk project in Visual Studio 2005 and configure an orchestration in the project to handle communication between BizTalk Server and the Oracle system. You will add send and receive ports, build the project, and then deploy the project.

  1. Open Visual Studio 2005 and create a new BizTalk project in the C:\LABS folder. On the File menu, click New. The New Project dialog box appears. In the Templates section, select Empty BizTalk Server project. Enter XX_OracleQuery as the unique project name, and then click OK.

    Bb226425.32261bf3-6b21-4017-b7ed-6d9e7b26a46c(en-us,BTS.20).gif
  2. In Solution Explorer, right-click the project, click Add, and then click Add Generated Items.

    Bb226425.33f7d44a-a13b-4e0d-9f68-11d1117eeebe(en-us,BTS.20).bmp
  3. In the Categories pane, select Add Adapter Metadata, select Add Adapter Metadata on the Templates side, and then click Add.

    Bb226425.71c0d534-e85f-4a24-8b45-80e2efe9c873(en-us,BTS.20).gif
  4. In the Add Adapter Wizard, select the Oracle Database adapter, select the XXOracleDBSendPort send port that you created in the preceding procedure, and then click Next.

    Bb226425.note(en-us,BTS.20).gifNote
    You will enter your particular SQL server in the SQL Server field. This database is contacted by using the OracleDB adapter and the ODBC Data Source XXDBPSS configured for the XXOracleDBSendPort send port.

    Bb226425.66c3b91f-fd80-4cbd-82a8-7ab39169bf01(en-us,BTS.20).gif
  5. On the Select Services to Import page, expand OracleDB and expand XXDBPSS.

    Bb226425.9ed8bb07-f959-4bd6-b253-e5485285cd35(en-us,BTS.20).gif
  6. Expand PSSUSR, expand Tables, select the EMP table, and then click Finish.

    Bb226425.3ebfa0b6-2c68-47b7-976b-5ef038a581a6(en-us,BTS.20).gif
  7. In Solution Explorer, there is a new BizTalk orchestration with two new associated schema files. These files are created by the Add Adapter Wizard. Double-click the BizTalk Orchestration.odx file to open the orchestration.

    Bb226425.d2d256c9-8581-4d02-901e-f4db40ac5471(en-us,BTS.20).gif

This orchestration accepts as input from the File adapter an XML file formatted for the Oracle database. The orchestration uses the Oracle adapter to send the XML file to the Oracle system. Oracle processes the query and generates an output XML file containing the results. This XML file returns to the orchestration through the Oracle adapter, and the File adapter writes the XML file to the output location on disk.

To complete the orchestration, you need to create and configure ports to receive and send the XML files. First, configure a receive port to be used by the File adapter to input the XML containing the query into the orchestration from disk.

  1. Double-click the BizTalk Orchestration.odx file to open the orchestration.

  2. In the BizTalk Orchestration.odx file, right-click the left port surface and then click New Configured Port. This starts the Port Configuration Wizard. On the Welcome to the Port Configuration Wizard page, click Next.

  3. On the Port Properties page, enter XX_File_In for Name, and then click Next.

  4. On the Select a Port Type page, select Create a new Port Type, and then enter or select the following property values:

    Port Type Name: XX_FileIn_Port

    Communication Pattern: One Way

    Access Restrictions: Internal - limited to this project

  5. Click Next to go to the Port Binding page, and then select the following property values:

    Port direction of communication: I'll always be receiving messages on this port

    Port binding: Specify later

  6. Click Next, and then click Finish.

Next, create a send/receive port to send the initial XML input file containing the query to the Oracle system. This port will also receive an output XML file containing the query results from the call to the Oracle database.

  1. In the BizTalk Orchestration.odx file, right-click the right port surface and then click New Configured Port. This starts the Port Configuration Wizard. On the Welcome to the Port Configuration Wizard page, click Next.

  2. On the Select a Port Type page, select Use an existing Port Type. For Available Port Types, select XX_OracleQuery.EMP, and then click Next.

    Bb226425.0cf856c8-9336-4acc-b459-04cc66a94e04(en-us,BTS.20).gif
  3. Click Next to go to the Port Binding page, and then select the following property values:

    Port direction of communication: I'll be sending a request and receiving a response

    Port binding: Specify later

  4. Click Next, and then click Finish. On the port surface you will see the port and the available methods.

Finally, configure a send port to be used by the File adapter to output the XML containing the query results to disk.

  1. In the BizTalk Orchestration.odx file, right-click the left port surface and then click New Configured Port. This starts the Port Configuration Wizard. On the Welcome to the Port Configuration Wizard page, click Next.

  2. On the Port Properties page, enter XX_FileOut for Name, and then click Next.

  3. On the Select a Port Type page, select Create a new Port Type, and then enter or select the following property values:

    Port Type Name: XX_FileOut_Port

    Communication Pattern: One Way

    Access Restrictions: Internal - limited to this project

  4. Click Next to go to the Port Binding page, and then select the following property values:

    Port direction of communication: I'll always be sending messages on this port

    Port binding: Specify later

  5. Click Next, and then click Finish.

Displayed on the port surface are the new ports and the available methods for the Oracle services. Later you will specify the Oracle adapter to send and receive files from the Oracle system.

The XX_File_In and XX_File_Out ports you just created need associated message types.

  1. On the left port surface, click Request on the XX_File_In port. In the Properties window, expand Message Type, expand Multi-part Message, and then click XX_OracleQuery.Query.

    Bb226425.2bb8734b-c324-458e-827e-b022e504f0e0(en-us,BTS.20).gif
  2. On the left port surface, click Request on the XX_File_Out port. In the Properties window, expand Message Type, expand Multi-part Message, and then click XX_OracleQuery.QueryResponse.

    Bb226425.9c0e4c36-6888-4171-a883-cadb52989824(en-us,BTS.20).gif

Insert two Send shapes and two Receive shapes into the orchestration to link to the ports.

  1. Drag a Receive component from the Toolbox and drop it immediately below the start of the orchestration (the green circle). Click the Receive shape, and in the Properties window, enter Recv_File for the Name, and set Activate to true. Doing so will activate the orchestration when an incoming document is received on this receive port. Set Operation to XX_File_In.Operation_1.Request.

  2. Drag a Send component from the Toolbox and drop it immediately below the Recv_File Receive shape. Click the new Send shape, and in the Properties window, enter ToOracle for the Name. Set Operation to XX_ToOracle.Query.Request.

  3. Drag a Receive component from the Toolbox and drop it immediately below the To_Oracle Send shape. Click the Receive shape, and in the Properties window, enter FromOracle for the Name. Set Operation to XX_ToOracle.Query.Response.

  4. Drag a Send component from the Toolbox and drop it immediately below the FromOracle Receive shape. Click the new Send shape, and in the Properties window, enter Send_To_Disk for the Name. Set Operation to XX_File_out.Operation_1.Request. Set Message to Message_2.

Building and Deploying the Project

Now the BizTalk project is complete and you can build and deploy it in Visual Studio 2005.

  1. Click Start, point to All Programs, point to Microsoft Visual Studio 2005, point to Visual Studio Tools, and then click Visual Studio 2005 Command Prompt.

  2. To build the project, you need a strong name key file.At the command prompt, enter the following to create a strong name key file:

    sn -k labs.snk

    Bb226425.3a85f863-a1c4-461a-886a-93c38b9cd07e(en-us,BTS.20).gif
  3. In Solution Explorer, right-click the XX_OracleQuery project, and then click Properties.

  4. Under Common Properties, select Assembly, and then set Assembly Key File to the strong name key file: labs.snk

    Bb226425.dffb8cab-f32c-40fc-a9b8-4464ca2c073b(en-us,BTS.20).gif
  5. Under Configuration Properties, select Deployment, set Application Name to XX_OracleQuery, and then click OK.

    Bb226425.9b63d2ab-a28c-47bd-80be-444e45fb11a9(en-us,BTS.20).gif
  6. In Solution Explorer, right-click the XX_OracleQuery project, and then click Build.

  7. After the build completes successfully, right-click the XX_OracleQuery project, and then click Deploy.

Testing the Application and Viewing the XML Output

Now you will test the application that you have created and deployed. You will create the XML file that starts the orchestration process, and then you will configure folders to receive and send XML files within the application. After you have configured the application, you will run it and view the XML files that the orchestration returns.

  1. In Solution Explorer, right-click EMPService_EMP_x5d.xsd to open the file.

    Bb226425.b7234460-6495-4407-a71f-03ca46768c13(en-us,BTS.20).gif
  2. Right-click EMPService_EMP_x5d.xsd and then click Properties. For the Output Instance Filename enter the following path and file name for the sample XML:

    C:\LABS\XX_ORACLEQUERY\SAMPLEQUERY.XML

    Bb226425.9c0e4ca1-f392-4d74-a2bb-57ee8c6da59b(en-us,BTS.20).gif
  3. Click OK. In the Properties window, select <Schema> and set Root Reference: to Query. This will cause the generated XML to only include the Query xml.

  4. Right-click EMPService_EMP_x5d.xsd and then click Generate Instance. This generates the SampleQuery.xml file. This file will be dropped in the receive location as input to the adapter to start the orchestration process.

    Bb226425.5479b60b-a15a-4b84-a4c5-9c0a78a6496d(en-us,BTS.20).gif

  1. Configure folders for receiving the incoming files and sending the outgoing files. Go to C:\LABS\XX_ORACLEQUERY and create two new subfolders named XXFileIn and XXFileOut.

  2. In the BizTalk Server Administration console, expand Console Root, expand BizTalk Server 2006 Administration, expand BizTalk Group, expand Applications, right-click XXOracleQuery, and then click Configure.

  3. Select Orchestration_1 and click the Host drop-down box. Select BizTalkServerApplication.

  4. Under Receive Ports, click <None>. In the drop-down list, select New Receive Port.

  5. For Name, type XXFileIn, and then click OK. A message box appears stating that you need to designate a receive location. Click OK, and then click New.

    Bb226425.02c660cc-41e6-4717-b09b-5e310f5d446a(en-us,BTS.20).gif
  6. Type or select the following values for the properties:

    Name: FileInLoc

    Type: File

    Receive Handler: BizTalkServerApplication

    Receive Pipeline: XMLReceive

    Bb226425.e33987c0-b4e0-4c59-a58b-78c38177b961(en-us,BTS.20).gif
  7. Click Configure, type C:\LABS\XX_ORACLEQUERY\XXFILEIN for Receive Folder, and then click OK three times.

    Bb226425.acff3800-bbe8-44da-a3c2-ff8910c2b36d(en-us,BTS.20).gif
  8. Click <None> for XX_ToOracle in the drop-down list.

  9. Select New Send Port and then select or type the following values for the properties:

    Name: XX_OracleQuery_Port

    Type: Oracle

    Send Handler: BizTalkServerApplication

    Pipelines: XMLTransmit and XMLReceive

  10. Click Configure, and then enter the following property values:

    1. Password: Use the drop-down list box and then enter your Oracle password.

    2. Path: c:\oracle\ora92\bin

    3. Service name: XXDBPSS (This is the Oracle Data Source name.)

    4. User Name: PSSUSR

    Bb226425.36fe0375-530e-45ec-a8fe-e4da75aa9681(en-us,BTS.20).gif
  11. Click OK twice to close the dialog boxes.

  12. In the Configure Application window, click <None> for XX_FileOut in the drop-down list.

  13. Select New Send Port and type or select the following values for the properties:

    Name: FileOutLoc

    Type: File

    Send Handler: BizTalkServerApplication

    Send Pipeline: XMLTransmit

    Bb226425.50599d4d-937d-4fb3-bfff-2237cbbb0c3c(en-us,BTS.20).gif
  14. Click Configure and type C:\Labs\XX_OracleQuery\XXFileOut for Destination Folder. Keep %MessageID%.xml for File Name because this results in a unique file for each message.

    Bb226425.98b824bb-0bce-428d-b649-6cb9b26143df(en-us,BTS.20).gif
  15. Click OK three times to close the dialog boxes.

  16. In the BizTalk Server Administration console, right-click the XX_OracleQuery application, and then click Start.

  1. In the C:\Labs\XX_OracleQuery directory, change the second line of the Samplequery.xml file from:

    <ns0:Filter>Filter_0</ns0:Filter>
    

    to:

    <ns0:Filter></ns0:Filter>
    
  2. Save the changes and copy the file to the C:\Labs\XX_OracleQuery\XXFileIn folder. This is the receive location that starts the orchestration process.

  3. In a few seconds, an XML file should appear in the C:\Labs\XX_OracleQuery\XXFileOut folder. This should contain the data from the record where the location is AUS01.

    Bb226425.ec3e1d0c-3f4e-4362-802a-d5db03f19f04(en-us,BTS.20).gif

    This returned record data should match the data returned by the query against the Oracle system in Lab 1. By comparing the records you obtained in Lab 1, you can verify that the Get method worked properly.

In this lab, you first verified that the prerequisites were set up correctly to access the Oracle system. Then you used Visual Studio 2005 to create a new BizTalk project containing an orchestration. You configured the BizTalk orchestration to use the Oracle adapter to get data from the Oracle system. To configure the orchestration, you created send, receive, and send/receive ports. You bound these ports to the Oracle adapter, and assigned messages to the appropriate ports.

After you completed the BizTalk project, you used Visual Studio 2005 to build and deploy it. You then configured your new application and ran it to get data from the Oracle system. To verify that the application worked correctly, you compared its output XML file to the file that you received from the Oracle system in Lab 1.

Community Additions

ADD
Show:
© 2014 Microsoft