SQL Loader

userid	-- ORACLE username/password
control	-- Control file name
log	-- Log file name
bad	-- Bad file name
data	-- Data file name
discard	-- Discard file name
discardmax	-- Number of discards to allow (Default all)
skip	-- Number of logical records to skip (Default 0)
load	-- Number of logical records to load (Default all)
errors	-- Number of errors to allow (Default 50)
rows	-- Number of rows in conventional path bind array or between direct path data saves (Default: Conventional path 64, Direct path all)
bindsize	-- Size of conventional path bind array in bytes (Default 256000)
silent	-- Suppress messages during run (header,feedback,errors,discards,partitions)
direct	-- use direct path (Default FALSE)
parfile	-- parameter file: name of file that contains parameter specifications
parallel	-- do parallel load (Default FALSE)
file	-- File to allocate extents from
skip_unusable_indexes	-- disallow/allow unusable indexes or index partitions (Default FALSE)
skip_index_maintenance	-- do not maintain indexes, mark affected indexes as unusable (Default FALSE)
readsize	-- Size of Read buffer (Default 1048576)
external_table	-- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_ USED)
columnarrayrows	-- Number of rows for direct path column array (Default 5000)
streamsize	-- Size of direct path stream buffer in bytes (Default 256000)
multithreading	-- use multithreading in direct path
resumable	-- enable or disable resumable for current session (Default FALSE)
resumable_name	-- text string to help identify resumable statement
resumable_timeout	-- wait time (in seconds) for RESUMABLE (Default 7200)
date_cache	-- size (in entries) of date conversion cache (Default 1000)

Command-line parameters may be specified either by position or by keywords.
An example of the former case is 'sqlldr scott/tiger foo';
an example of the latter is 'sqlldr

control=foo userid=scott/tiger'.
One may specify parameters by position before but not after parameters specified by keywords.
For example,

'sqlldr scott/tiger control=foo logfile=log'
is allowed, but 'sqlldr scott/tiger control=foo log' is not, even though the position of the parameter 'log' is correct.

SQL*Loader loads data from external files into tables of an Oracle database. It has a powerful data-parsing engine that puts little limitation on the format of the data in the datafile. You can use SQL*Loader to do the following:

Load data from multiple datafiles during the same load session.
Load data into multiple tables during the same load session.
Specify the character set of the data.
Selectively load data (you can load records based on the records' values).
Manipulate the data before loading it, using SQL functions.
Generate unique sequential key values in specified columns.
Use the operating system's file system to access the datafiles.
Load data from disk, tape, or named pipe.
Generate sophisticated error reports, which greatly aids troubleshooting.
Load arbitrarily complex object-relational data.
Use secondary datafiles for loading LOBs and collections.
Use either conventional or direct path loading. While conventional path loading is very flexible, direct path loading provides superior loading performance.
Use a DB2 Load Utility control file as a SQL*Loader control file with few or no changes involved.

01. CONTROLFILE
02. DATAFILE OR DATAFILES
03. BAD FILE
04. DISCARD FILE
05. LOG FILE
06. DATABASE TABLES AND INDEXES

This is the most vital part of the SQL*Loader. This file is a multiple lined text files created using the syntax as understood by SQL*Loader

Global options such as bindsize, rows, records to skip, and so on
INFILE clauses to specify where the input data is located
Data to be loaded
The second section consists of one or more INTO TABLE blocks. Each of these blocks contains information about the table into which the data is to be loaded, such as the table name and the columns of the table.

The syntax is free-format (statements can extend over multiple lines).
It is case insensitive; however, strings enclosed in single or double quotation marks are taken literally, including case.

In control file syntax, comments extend from the two hyphens (--) that mark the beginning of the comment to the end of the line. The optional third section of the control file is interpreted as data rather than as control file syntax; consequently, comments in this section are not supported.

The CONSTANT keyword has special meaning to SQL*Loader and is therefore reserved. To avoid potential conflicts, Oracle Corporation recommends that you do not use the word CONSTANT as a name for any tables or columns.

SQL*Loader accepts single or multiple files and reads, parses and loads data into the intended tables per the controlfile

SQL*Loader reads data from one or more files (or operating system equivalents of files) specified in the control file. From SQL*Loader's perspective, the data in the datafile is organized as records. A particular datafile can be in fixed record format, variable record format, or stream record format. The record format can be specified in the control file with the INFILE parameter. If no record format is specified, the default is stream record format.

If data is specified inside the control file (that is, INFILE * was specified in the control file), then the data is interpreted in the stream record format with the default record terminator.

PURPOSE

The purpose of this article is to prevent running into many kinds of unclear errors due to incorrect environment settings or unknown limits before starting Sql*Loader.

Checklist before starting Sql*Loader

The first and most important thing to take into a account is setting the environment variables correctly before starting Sql*Loader. Check the value of the environment variables at the machine from where you are starting your Sql*Loader session.

How to check your environment variables (e.g. NLS_LANG):

- UNIX
$ env | grep NLS_LANG
- Windows
Start --> Run --> regedit --> HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\<HOMEx>\NLS_LANG

Set NLS_LANG to the desired territory and characterset to prevent wrong data to be loaded or getting errors due to this data.

Syntax: NLS_LANG=<Language_Territory.Characterset>

Example: NLS_LANG="Dutch_The_Netherlands.WE8ISO8859P15"

Possible problems due to an incorrect value:

- ORA-1722 invalid number

NLS_NUMERIC_CHARACTERS is equal to ',.' or '.,' based upon the Territory (NLS_LANG) you are in.

Example:

The number 13.4 is not valid in this case and produces ORA-1722.

- Characters not loaded correctly due to an incorrect characterset specified.

Reason:

The characterset specified needs to be the characterset of the data to be loaded (unless the CHARACTERSET keyword is used).

2 - Check ORACLE_HOME:

Set above environment variables for the desired ORACLE_HOME from where you want SQL*Loader to be started.

Example: ORACLE_HOME=/u01/app/oracle/product/9.0.1

In Windows you can set your primary ORACLE_HOME using the Home Selector:

Start --> Programs --> Oracle Installation Products --> Home Selector

Possible problem due to an incorrect value:

- ORA-12560: TNS: protocol adapter error

Reason:

The SQL*Net connection fails because of the mixed environments

3 - Check LD_LIBRARY_PATH (Unix only):

Check whether $ORACLE_HOME/lib is included in LD_LIBRARY_PATH.

Example: LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

Possible problem:

- libwtc8 library cannot be found.

4 - Check ORA_NLS33 (or ORA_NLS32) setting

Example: ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data

Possible problem due to an incorrect value:
- Segmentation Fault; Core Dump
Reason:
ORA_NLS33 points to the $ORACLE_HOME of another installation.
For example, the ORACLE_HOME where your Developer software is installed.

Limits / Defaults

1 - Check the field lengths of the data to be loaded.

Specify a length for the fields defined in the controlfile based upon the data to be loaded. Also check if the data to be loaded that it fits in the table columns specified.

A variable length field defaults to 255 bytes for a CHAR. If no datatype is specified, it defaults to a CHAR of 255 bytes as well.

See the 'Utilities Manual' chapter 'SQL*Loader Control File Reference' (see references) part 'Calculating the Size of Field Buffers' or related.

Possible errors are:

- ORA-1401: inserted value too large for column

- Field in data file exceeds maximum length.

2 - Check the datafile File Size Limit on your Operating System (Unix only)

On Unix, the filesize is limited by the shell's filesize limit.
Set the limit of your filesize with ulimit (ksh and sh) or limit
(csh) command to a value larger than the size of your sqlloader datafile.