Master Note for SQL*Loader (SQLLDR) (Doc ID 1264730.1)

Checklist Before Starting SQL*Loader

Concepts/Definitions

Oracle SQL*Loader (SQLLDR) loads data from external files into tables in a database. It has a robust data parsing engine that works with a variety of data formats in the datafile. It can load data locally or across a network connection. It is possible to load multiple datafiles, load data into multiple tables, manipulate data with SQL functions in the same load session.

Environment Variables  (MOS -160521.1)

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 ( check same MOS ID)

Possible problems due to an incorrect value:

- ORA-1722 invalid number 

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

Example: The NLS_LANG setting above results in ',.' for NLS_NUMERIC_CHARACTERS. 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.2.0

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_NLS10 setting:

Example: ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
ORA_NLS10=$ORACLE_HOME/nls/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.

Note :

Make field setting efficient. 

Field setting is the process of mapping the "fields" in the datafile 
to their corresponding columns in the database. The mapping function 
is controlled by the description of the fields in the control file. 
Field setting is the biggest consumer of CPU time for most loads.

o Use Parallel Loads. Available with direct path data loads, this option 
allows multiple SQL*Loader jobs to execute concurrently.

$ sqlldr control=first.ctl parallel=true direct=true
$ sqlldr control=second.ctl parallel=true direct=true

Ex: 

Run the SQL Loader script (svptrm.ctl control file) to load the data from the trm.txt file

sqlldr APPS/<password>@<sid> control=svptrm.ctl

++++++++++++++++