SQL*Loader

This section will provide a basic understanding of SQL*Loader. Most of the information contained in this section is DIRECTLY extracted from 'ORACLE Server Utilities Users Guide' and all credit should be given to ORACLE. If you require more detailed information than provided in this section, consult the 'ORACLE Server Utilities Users Guide'.

SQL*Loader is a product for moving data from external files into tables in an ORACLE database. SQL*Loader loads data in a variety of formats, performs filtering (selectively loading records based upon the data values), and loads multiple tables simultaneously. During execution SQL*Loader produces a detailed log file with statistics about the load, and may also produce a bad file (records rejected because of incorrect data) and a discard file (records that did not meet your selection criteria). You have control over several loading options.

You must provide two types of input to SQL*Loader to load data from external files into an ORACLE database: the data itself, and control information describing how to perform the load. You must provide a file called the control file as an input to SQL*Loader. The control file tells SQL*Loader how to interpret the data file. For example, it describes the following:

1. The names of the data files
2. The format of the data files
3. The character sets used in the data files
4. The datatypes of the fields in those files
5. How to identify the start and end of data fields
6. Which tables and columns to load

The control file's datatype specifications tell SQL*Loader how to interpret the fields in the data files. SQL*Loader uses this information when working with the fields, and uses it to describe the data that is being passed to ORACLE. ORACLE then converts the data into the datatype specified by the table definition. Some information is mandatory (such as where to find the data and how it corresponds to the database tables). However, many options are also available to describe and manipulate the file data. For example, the instructions can include directions on how to format or filter the data, or to generate unique ID numbers.

You may load data in various formats. It is usually read from one or more data files, but the data may also be placed in the control file after the control file information. Data records may be in fixed or variable format. In fixed format, the data is contained in records which all have the same (fixed) format. That is, the records have a fixed length, and the data fields in those records have fixed length, type, and position.

In variable format (sometimes called stream format), each record is only as long as necessary to contain the data. With character data, if the first item is shorter than the second one, the first record is shorter. Also, the type of data in each record may vary. One record may contain a character string, the next may contain seven integers, the third may contain three decimals and a float, and so on. Operating systems use a record terminator character (such as newline) to mark where variable records end.

Delimited data is of two types: terminated or enclosed. Terminated data is followed by a specified character such as a comma, as in the following example:
1,1,2,3,5,8,13

Enclosed data is preceded and followed by a specified character such as a quotation mark, as in the following example:
'BUNKY'

A final distinction concerns the difference between logical and physical records.

A record or line in a file (either of fixed length or terminated) is referred to as a physical record. Logical record, on the other hand, corresponds to a row in a database table. Sometimes the logical and physical records are equivalent; such is the case when only a few short columns are being loaded.

However, sometimes several physical records must be combined to make one logical record. The examples below will illustrate some of the features of the SQL*Loader.

Example 1. Loading Data into Multiple Tables

CONTROL FILE: The control file for this example.
-- Loads EMP records from first 23 characters
-- Creates and loads PROJ records for each PROJNO listed
-- for each employee

LOAD DATA
INFILE 'ulcase5.dat'
BADFILE 'ulcase5.bad'
DISCARDFILE 'ulcase5.dsc'

a. REPLACE

b. INTO TABLE emp
(empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL)

b. INTO TABLE proj
-- PROJ has two columns, both not null: EMPNO and PROJNO

c. WHEN projno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
c. projno POSITION(25:27) INTEGER EXTERNAL) -- 1st proj

b. INTO TABLE proj
WHEN projno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
projno POSITION(29:31) INTEGER EXTERNAL) -- 2nd proj

b. INTO TABLE proj
WHEN projno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
projno POSITION(33:35) INTEGER EXTERNAL) -- 3rd proj

---------------------------------------------------------------
NOTES:

(a) REPLACE indicates that if there is data in the tables to be loaded
(EMP and PROJ), that data should be deleted before new rows are loaded.


(b) Multiple INTO clauses are used to load two tables, EMP and PROJ. The
same set of records is processed three times using different combinations
of columns each time, to load table PROJ.


(c) WHEN is used to load only rows with non-blank project numbers. When
PROJNO is defined as columns 25..27, rows are inserted into PROJ only if
there is a value in those columns.

DATA FILE - Part of the data file follows.
1234 BAKER 10 9999 101 102 103
1234 JOKER 10 9999 777 888 999
2664 YOUNG 20 2893 425 abc 102
INVOKING SQL*LOADER - The command line for this example.
SQLLOAD / CONTROL=ULCASE5.CTL LOG=ULCASE5.LOG

Example 2 Loading a Delimited, Free-Format File

CONTROL FILE - The control file for this example.
-- Variable-length, delimited and enclosed data format

LOAD DATA
a. INFILE *

b. APPEND

INTO TABLE emp
c. FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(empno, ename, job, mgr,
d. hiredate DATE "DD-Month-YYYY",
sal, comm, deptno CHAR TERMINATED BY ':',
projno,
e. loadseq SEQUENCE(MAX,1))

f. BEGINDATA
7782, "CLARK", "Manager", 7839, 09-June-1981, 2572.50, 10:101
7839, "King", "President", , 17-January-1982, 920.00, 10:102

---------------------------------------------------------------
NOTES:

(a) INFILE * signifies the data is found at the end of the control file.


(b) APPEND indicates that data may be loaded even if the table already
contains rows; the table need not be empty.


(c) The default terminator for the data fields is a comma, and some fields
may be enclosed by a double quote.


(d) The data to be loaded into column HIREDATE appears in the format
DD-Month-YYYY.


(e) The SEQUENCE function is used to generate a unique value in the column
LOADSEQ. This function finds the current maximum value in column LOADSEQ
and adds the increment (1) to it to obtain the value for LOADSEQ for each
row inserted.


(f) BEGINDATA signifies the end of the control information and the
beginning of the data.

INVOKING SQL*LOADER - The command line for this example.
SQLLOAD / CONTROL=ULCASE3.CTL LOG=ULCASE3.LOG


Control File Syntax
The control file usually begins with the phase LOAD DATA, followed by several phrases that describe the data to be loaded. Only comments or the OPTIONS phrase can precede the LOAD DATA phase.

For a complete control file syntax diagram see Appendix C in this manual. Only a subset of the syntax will be explained below. For a complete explanation of the above syntax, see chapter 6 of ``ORACLE7 Server Utilities Users Guide''. Comments Comments may appear anywhere in the command section of the file, but they should not appear in the data. Comments are preceded with a double dash, which may appear anywhere on a line. All text to the right of the double dash is ignored,
until the end of line.

The OPTIONS Clause
The OPTIONS clause is useful when you usually invoke a control file with the same set of options, or when the command line and all its arguments becomes very long. This clause allows you to specify runtime arguments in the control file rather than on the command line.
SKIP = n -- Number of logical records to skip (DEFAULT 0)
LOAD = n -- Number of logical records to load (DEFAULT all)
ERRORS = n -- Number of errors to allow (DEFAULT 50)
ROWS = n -- Number of rows in conventional path bind array (DEFAULT 64)
BINDSIZE = n -- Size of conventional path bind array in bytes
SILENT = {HEADER | FEEDBACK | ERROR | DISCARDS | ALL }
-- Suppress messages during run

For example:
OPTIONS (BINDSIZE=10000, SILENT=(ERRORS, FEEDBACK) )
Values specified on the command line override values specified in the control file. With this precedence, the OPTIONS keyword in the control file established default values that are easily changed from the command line. Continuing Interrupted Loads If SQL*Loader runs out of space for data rows or index entries, the load is discontinued. (For example, the table might reach its maximum number of extents.) Discontinued loads can be continued after more space is made available. When a load is discontinued, any data already loaded remains in the tables, and the tables are left in a valid state. SQL*Loader's log file tells you the state of the tables and indexes and the number of logical records already read from the input data file. Use this information to resume the load where it left off.
For example:
SQLLOAD / CONTROL=FAST1.CTL SKIP=345
CONTINUE\_LOAD DATA statement is used to continue a discontinued direct path load involving multiple tables with a varying number of records to skip. For more information on this command, see chapter 6 of ``ORACLE7 Server Utilities Users Guide''.

Identifying Data Files
To specify the file containing the data to be loaded, use the INFILE or INDDN keyword, followed by the filename. A filename specified on the command line overrides the first INFILE or INDDN statement in the control file. If no filename is specified, the filename defaults to the control filename with an extension or filetype of DAT.

Loading into Non-Empty Database Tables
SQL*Loader does not update existing records, even if they have null columns. If the tables you are loading already contain data, you have three choices for how SQL*Loader should proceed:

1. INSERT - This is the default option. It requires the table to be empty before loading. SQL*Loader terminates with an error if the table contains rows.
2. APPEND - If data already exists in the table, SQL*Loader appends the new rows to it; if data doesn't already exist, the new rows are simply loaded.
3. REPLACE - All rows in the table are deleted and the new data is loaded. This option requires DELETE privileges on the table.

You can create one logical record from multiple physical records using CONCATENATE and CONTINUEIF. See chapter 6 of 'ORACLE7 Server Utilities Users Guide'.

Loading Logical Records into Tables
1. The INTO TABLE clause allows you to tell which table you want to load data into. To load multiple tables, you would include one INTO TABLE clause for each table you wish to load. The INTO TABLE clause may continue with some options for loading that table. For example, you may specify different options (INSERT, APPEND, REPLACE) for each table in order to tell SQL*Loader what to do if data already exists in the table.
2. The WHEN clause appears after the table name and is followed by one or more field conditions. For example, the following clause indicates that any record with the value ``q'' in the fifth column position should be loaded:
WHEN (5) = 'q'
A WHEN clause can contain several comparisons as long as each is preceded by AND. Parentheses are optional but should be used for clarity with multiple comparisons joined by AND. For example:
WHEN (DEPTNO = '10') AND (JOB = 'SALES')
To evaluate the WHEN clause, SQL*Loader first determines the values of all the fields in the record. Then the WHEN clause is evaluated. A row is inserted into the table only if the WHEN clause is true. When the control file specifies more fields for a record than are present in the record, SQL*Loader must determine whether the remaining (specified) columns should be considered null, or whether an error should be generated. TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns. For example, if the following
data
10 Accounting

is read with the following control file

INTO TABLE dept
TRAILING NULLCOLS
( deptno CHAR TERMINATED BY " ",
dname CHAR TERMINATED BY WHITESPACE,
loc CHAR TERMINATED BY WHITESPACE )
and the record ends after DNAME, then the remaining LOC field is set to null. Without the TRAILING NULLCOLS clause, an error would be generated, due to missing data.

Specifying Datatypes
The datatype specification in the control file tells SQL*Loader how to interpret the information in the data file. The server defines the datatypes for the columns in the database. SQL*Loader extracts data from a field in the input file, guided by the datatype specification in the control file. SQL*Loader then sends the field to the server to be stored in the appropriate column. The server does any data conversion necessary to store the data in the proper internal format. The datatype of the data in the file does not necessarily have to be the same as the datatype of the column in the ORACLE table. ORACLE automatically performs conversions - but you need to ensure that the conversion makes sense and does not generate errors.
SQL*Loader does not contain datatype specifications for ORACLE internal datatypes like NUMBER or VARCHAR2. SQL*Loader's datatypes describe data that can be produced with text editors (character datatypes) and with standard programming languages (native datatypes).

Native Datatypes
Some datatypes consist entirely of binary data, or contain binary data in their implementation. These non-character datatypes are the native datatypes:
INTEGER ZONED SMALLINT
VARCHAR FLOAT GRAPHIC
DOUBLE GRAPHIC EXTERNAL BYTEINT
VARGRAPHIC (packed) DECIMAL RAW

These datatypes will not be discussed as most of the datatypes that you will be using will be character datatypes. For more information on SQL*Loader datatypes,

Character Datatypes
The character datatypes are CHAR, DATE, and the numeric EXTERNAL datatypes (INTEGER and DECIMAL). These fields can be delimited, and can have lengths (or maximum lengths) specified in the control file.

1. CHAR - This data field contains character data. The length is optional, and is taken from the POSITION specification if it is not present here. If present, this length overrides the length in the POSITION specification. If no length is given, CHAR data is assumed to have a length of 1. A field of datatype CHAR may also be variable-length delimited or enclosed.

2. To Load LONG Data: If the column in the database table is defined as LONG, you must explicitly specify a maximum length either with a length-specifier on the CHAR keyword, or with the POSITION keyword. This guarantees that a large enough buffer is allocated for the value, and is necessary even if the data is delimited or enclosed.

3. DATE - This data is character data that should be converted to an ORACLE date using the specified date mask. The length specification is optional, unless a varying-length data mask is specified. With a specification like:
DATE "Month dd, YYYY"
the date mask is 14 characters, while the length of a field like
September 31, 1991
is 18 characters. In this case, a length must be specified. Similarly, a length is required for any Julian dates (date mask ``J'') - a field length is required any time the length of the date string could exceed the length of the mask. An explicit length specification, if present, overrides the length in the POSITION clause. Either of these overrides the length derived from the mask. The mask may be any valid ORACLE date mask. If you omit the mask, the default ORACLE date mask of 'dd-mon-yy' is used.

4. Numeric EXTERNAL - The numeric external datatypes are the numeric datatypes (INTEGER, FLOAT, DECIMAL, and ZONED) specified with the EXTERNAL keyword along with optional length and delimiter specifications. These datatypes are the human-readable, character form of numeric data. The data is a number in character form (not binary representation). As such, these datatypes are identical to CHAR and are treated identically, with one exception: the use of DEFAULTIF. If you want the default to be null, use CHAR;

5. delimiter_spec - The boundaries of CHAR, DATE, or numeric EXTERNAL fields may also be marked by specific delimiter characters contained in the input data record. You indicate how the field is delimited by using a delimiter specification after specifying the datatype. Delimited data can be TERMINATED or ENCLOSED.