Mr

Document K2.6

Using SQL*Loader

Document K2.6

Using SQL*Loader

1 Introduction

1 Running SQL*Loader

2 Control file

2 Examples

2 Loading from a separate data file using fixed format data

3 Loading from data in the control file using free format data

4 Selecting data

4 Reading multiple rows per record

5 References

Introduction

The SQL*Loader module of the ORACLE Database Management

System loads data into an existing ORACLE table from an external file.

It is available locally only on CMS and PCs with ORACLE version 5.

Throughout this document the CAR database described in Reference A is

used for illustration.

There are several methods other than using SQL*Loader of inserting data

into a table:

¤ The SQL insert command may be used from the SQL*Plus module

(see Reference B), for example:

insert into CAR values ( . . . )

where the values to be inserted into a row of the table are listed

inside the parentheses. Dates and character data must be surrounded

by single quotes; items are separated by commas.

¤ SQL*Forms allows you to add rows interactively using forms. The

forms may contain default values and checks for invalid data (see

Reference C).

¤ ODL loads the table from a control file and separate fixed format

data file. ODL is available on all versions of ORACLE (see

Reference E). SQL*Loader is much more flexible than ODL and

will eventually supersede it on all systems.

SQL*Loader loads the table using a control file (created using a editor).

The data may be in a separate file or may be included in the control file.

The data may be in fixed length fields or may be free format separated by

any designated character. This document introduces only some of the

most useful features of SQL*Loader. Reference F describes

SQL*Loader in detail.

Running SQL*Loader

Before running SQL*Loader, ORACLE must be accessed by typing:

oracle

SQL*Loader takes its instructions from a control file (with filetype

CTL). For example to load from a control file named CARLOAD CTL,

type:

sqlload / carload

The slash (/) indicates that your ORACLE username is based on your

CMS user identifier and so ORACLE does not ask for a username before

running SQL*Loader.

The data may be included in the control file or, more commonly, be in a

separate file (normally with file type DAT). Not all the data need be

read from the file - you can choose to read only specified columns and

records (see 'Selecting data' on page 4). There is no apparent limit on

the length of a data record.

K2.6 (10.90) page 1

SQL*Loader produces a log file (with the same name as the control file

and a filetype of LOG) during execution. You should always look at the

log file after loading data. It may also produce a file with filetype BAD

listing records rejected because of incorrect data, and a discard file (with

filetype DSC) listing records which did not meet the selection criteria

specified in the control file.

Control file

The control file specifies:

¤ the name of the table(s) into which the data is to be loaded (which

must have been created already, see Reference B)

¤ the data or the name of the file(s) containing the data (unless the

filename(s) are specified on the sqlload command line)

¤ whether the table must be empty or whether the data are to be

appended to or replace existing data in the table

¤ the format of the data and its correspondence with the columns of

the table

Examples

The examples assume that the tables CAR and MANUFACTURER have

been created as described in Reference B. The control files and data file

used in the examples are publicly available on CMS.

To access the files, link to minidisk 192 belonging to ЈINFO and access

the disk by a letter you are not using. For example to access the disk as

your F-disk at address 292, type:

link Јinfo 192 292 rr

access 292 f

In the example below, the CAR table is loaded using a control file Loading from a

CARLOAD CTL. The data is in a separate file named