Essay About Control File And Separate Data File

Essay Preview: Mr
Report this essay
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