My Oracle Support Banner

SQL*Loader Problems Loading Variable Length Fields Using CHAR and VARCHAR Datatypes (Doc ID 160002.1)

Last updated on JANUARY 30, 2022

Applies to:

Oracle Database - Enterprise Edition - Version 7.0.16.0 and later
Information in this document applies to any platform.
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data.
Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Symptoms

A frequent problem encountered when using SQL*Loader is the situation where a long variable length field - CHAR, VARCHAR2, LONG - gets rejected during a DELIMITED load.

The SQL*Loader log file will usually show the records being rejected with the 'Field in data file exceeds maximum length' error.

In a slightly differing scenario, when loading a single character datatype column (i.e. no delimiter specified), then the data for that record is seen to be truncated.

This can come as a surprise to the user as the data does not actually exceed 2000 characters for VARCHAR2 or the length of a LONG if the column is defined as LONG or LONG RAW.

This problem does not appear if the same data is used for a POSITIONAL load.

Example

The following section demonstrates the problems with easily reproducible examples using a single table, <TABLE_NAME>, containing 3 columns :

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.