Export (0) Print
Expand All

Using an Oracle System

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.

The Oracle lab work is divided into two parts. This first lab (Lab 1) allows you to use the Oracle system without needing BizTalk Server or any Microsoft products. You will use the Oracle SQL Plus tool to connect to an Oracle database and modify a data table.

In the second lab (Lab 2), you will create a BizTalk project and orchestration. After you create the application, you will deploy it and use it to connect to an Oracle system by using the Oracle adapter. The goal is to access data through the BizTalk application by using the adapter.

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

To perform the procedures for this lab, you need to install the Oracle client software and its latest update. To use any of the client software tools you will need an Oracle database, network connectivity to that database, and a user account on that system. You do not need BizTalk Server to complete this lab.

In this lab, you will use the Oracle system without using any components of BizTalk Server 2006. The goals are to test your connectivity to Oracle and to ensure that the second lab will work correctly. You will use the Oracle SQL Plus tool to create and manipulate a data table in an Oracle database.

  1. Click Start, point to All Programs, point to Oracle - OraHome92, point to Application Development, and then click SQL Plus. This brings up an Oracle Log On dialog box.

  2. Enter your Oracle UserName, Password, and Host String, and then click OK.

    Bb226415.45950c0a-3852-451d-b43f-84d1dd7713cc(en-us,BTS.20).gif
  3. Enter the following SQL command to display the contents of the emp table:

    SQL>Select * from emp;

  4. Enter the following command to close the SQL Plus window.

    SQL>quit;

    Bb226415.7b62411b-a696-4e4d-8bae-61329d7364a9(en-us,BTS.20).bmp

    The SQL Plus tool allows you to issue SQL commands to the database. You can use the help command to assist with learning SQL Plus commands. In the following figure the help command displays SQL Plus commands.

    Bb226415.389427d8-0aa8-45b6-8f1a-969df9b478ae(en-us,BTS.20).bmp

You will now create a script file named xxMakeTable. When run, this script will create a table, insert data into it, and list the table's contents. SQL Plus uses Notepad as its editor by default.

The commands in the xxMakeTable file execute when you issue the SQL Plus start command. In the following procedure you will also see how to delete a line of data, list the contents of the table, and remove the table from the database by using single SQL commands within SQL Plus.

Bb226415.note(en-us,BTS.20).gifNote
In SQL Plus, an SQL command must end with a semicolon (;). If the semicolon is missing, the command will not execute until a semicolon is entered at the end of the SQL command.

  1. Log on to SQL Plus. At the command prompt, enter:

    edit xxMakeTable

  2. Click Yes when asked if you want to create a new file.

  3. Enter the following SQL code in the xxMakeTable Notepad file, click Save, and then click Exit to exit Notepad.

    REM - Create the table 
    CREATE TABLE PSSUSR.xxTBL (STUDENTNO NUMBER(4) NOT NULL, STUDENTNAME VARCHAR2(20) NOT NULL, GPA NUMBER(3, 2) NULL, GRADELEVEL FLOAT(2) NULL);
    REM - Put data into the table
    INSERT INTO PSSUSR.xxTBL (STUDENTNO, STUDENTNAME, GPA, GRADELEVEL) VALUES (1, 'Smith',  3.45, 9);
    INSERT INTO PSSUSR.xxTBL (STUDENTNO, STUDENTNAME, GPA, GRADELEVEL) VALUES (2, 'Jones',  2.30, 11);
    INSERT INTO PSSUSR.xxTBL (STUDENTNO, STUDENTNAME, GPA, GRADELEVEL) VALUES (3, 'Davis',  3.29, 12);
    REM - List the contents of the table
    SELECT * FROM xxTBL;
    
  4. At the SQL prompt, enter: start xxMakeTable

  5. When the SQL statements execute, the SQL Plus window displays the three lines of data that exist in the new xxTBL table. You can manipulate this data in SQL Plus as follows:

    1. To delete a line of data, enter: delete from xxTBL where STUDENTNO=2;

    2. To display the remaining data, enter: select * from xxTBL;

    3. To display a listing of the format of the table, enter: desc xxTBL;

    4. To remove a table from the database, enter: drop table xxTBL;

    Bb226415.bda498e3-3cbb-4f7c-952e-29655ffbee85(en-us,BTS.20).bmp

In this lab, you logged on to the Oracle system by using the Oracle SQL Plus tool. After you were connected, you created a database script with Notepad. When you started the script, the xxTBL table was created and three records were inserted into that table. These rows were then displayed in the SQL Plus window.

Community Additions

ADD
Show:
© 2014 Microsoft