Example: Loading Data into a Table

You can use Database Control to load data into a table. You can load data from a source file that is on your local computer—the one where your browser is running—or from a source file that is on the database host computer—the computer on which the Oracle instance is running. Because Database Control invokes the Oracle SQL*Loader utility to load the data, the format of the data in the source file can be of any format that is supported by SQL*Loader. In this example, you use a comma-delimited text file as the source file. In SQL*Loader terminology, the source file is referred to as the data file.

SQL*Loader also uses a control file to control the loading of data from the data file. The control file is a text file that contains statements written in the SQL*Loader command language. These statements specify where to find the data, how to parse and interpret the data, where to insert the data, and more. Database Control contains a Load Data wizard that takes you through the steps of preparing and running a data load job with SQL*Loader. (A wizard is an online, guided workflow.) The Load Data wizard can automatically create the SQL*Loader control file for you.

In this example, you load data into the PURCHASE_ORDERS table that you created in "Example: Creating a Table". For simplicity, this example loads only three rows.

To prepare for this example, you must create a text file named load.dat on the file system of the database host computer or on the file system of your local computer. The contents of the file should be as follows:

1, Office Equipment, 25-MAY-2006, 1201, 13-JUN-2006
2, Computer System, 18-JUN-2006, 1201, 27-JUN-2006
3, Travel Expense, 26-JUN-2006, 1340, 11-JUL-2006

To load data into the PURCHASE_ORDERS table:

1. Go to the Database Home page, logging in as user SYSTEM.

   See "Accessing the Database Home Page".

2. At the top of the page, click Data Movement.

   The Data Movement subpage appears.

3. In the Move Row Data section, click Load Data from User Files.

   The Load Data: Generate or Use Existing Control File page appears.

4. Select Automatically Generate Control File, and enter host computer credentials (user name and password) for the database host computer.

5. Click Continue.

   The first page of the Load Data wizard appears. Its title is Load Data: Data Files.

6. Follow the steps in the wizard, clicking Next to proceed to each new step.

   For information about using any of the wizard pages, click Help on that page. At the conclusion of the wizard, you submit a job that runs SQL*Loader. A job status page is then displayed. If necessary, refresh the status page until you see a succeeded (or failed) status.

7. If the job succeeded, then confirm that the data was successfully loaded by doing one of the following:

   • View the table data.

     See "Viewing Table Data".

   • Examine the SQL*Loader log file, which is written to the host computer directory that you designated for the SQL*Loader data file.

   Note: If the job succeeds, then it means only that Database Control was able to run the SQL*Loader utility. It does not necessarily mean that SQL*Loader ran without errors. For this reason, you must confirm that the data loaded successfully.

8. If the job failed, then examine the SQL*Loader log file, correct any errors, and try again.