SQL*Loader

Reads and interprets input files. It is the most efficient way to load large amounts of data.

SQL Loader - loading data with embedded newlines

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

To avoid this error, set the datatype with a big buffer as:

....
"DESC" CHAR(5000)
....

If you cannot use an Oracle Net database link and the datafile must be accessed by SQL*Loader running on the target system, it is advisable to use only the portable SQL*Loader datatypes (for example:

• CHAR,
• DATE,
• VARCHARC,
• and numeric EXTERNAL.

Datafiles written using these datatypes may be longer than those written with native datatypes. They may take more time to load, but they transport more readily across platforms.

From Checklist before starting SQL*Loader

Check NLS_LANG setting Set NLS_LANG to the desired territory and character set to prevent wrong data to be loaded or getting errors due to this data.

NLS_LANG=<Language_Territory.Characterset>
NLS_LANG="Dutch_The_Netherlands.WE8ISO8859P15"

Possible problems due to an incorrect value

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.

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

• SQL*Loader Concepts
• Applying SQL Function to Fields