Loading Data

Overview

Direct-load Insert

Loading Data with SQL*Loader

Overview

There are a few methods for loading data into tables in Oracle. Some covered in this section are:

• Direct-load insert, that is used to copy data from one table to another table within the same database.
• SQL*Loader, that is used to load data from external files into Oracle tables.

Direct-load Insert

A direct-load insert can be invoked by using the APPEND hint, as shown in the command below:

    INSERT /*+APPEND*/ INTO <table name> [[NO]LOGGING] sub-query;

where sub-query is used to retrieve the desired data.

The LOGGING mode is enabled by default which generates the redo log entries, making complete recovery possible in case of failures.

For example, the following command inserts all existing data in table OLD_EMPLOYEE into table EMPLOYEE.

    SQL> INSERT /*+APPEND*/ INTO employee NOLOGGING  SELECT * FROM old_employee;

Loading Data with SQL*Loader

SQL*Loader loads data from external files into Oracle tables. Some of its features are:

• One or more input file can be used.
• Input fields can be fixed or variable length.
• Input data can be any format -- character, binary ,date, decimals.
• SQL functions can be applied on the input data before the row is stored in the database.

Some files used by SQL*Loader include:

• Control files, that specifies the input format, output tables and optional conditions that may be used to load only part of the records found in the input data files.
• Data files, that contains data in the format defined in the control file.
• Log files, created by SQL*Loader and contains the record of the load
• Bad files, used to hold the records that are rejected during the load.

The SQL*Loader can be executed from the command line or invoked with GUI from Oracle Enterprise Manager which is NOT covered in this guide.

For example, the following command invokes the program named "sqlload" with the user id set to wanghao, (the password will be prompted interactively) and control file set to point.ctl, data file set to data.sql, and log file set a.log.

    $ sqlload userid=wanghao control=point.ctl log=a.log data=data.sql
 

The contents of control file point.ctl are:

LOAD DATA INTO TABLE points FIELDS TERMINATED BY ':'
(ID, D0, D1, D2, D3, D4, D5, D6, D7, D8, D9)

The control file instructs SQL*Loader to load into table POINTS the data whose fields are separated by colon ';'. It also tells the the order of table columns to be inserted.

The data file data.sql is a plain text file in which each line is a record. Some sample portions of the file are:

 0:1:2:3:4:5:6:7:8:9:10

 1:3:4:6:3:34:4:9:8:5:6