My Oracle Support Banner

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

Last updated on APRIL 03, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 7.0.16.0 and later
Information in this document applies to any platform.
***Checked for relevance on 22-MAR-2012***

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 :

CREATE TABLE <TABLE_NAME>
(
   EMP_NAME  VARCHAR2(15),
   EMP_ID    NUMBER,
   REFERENCE VARCHAR2(500)
);

and a data file (longrecords.txt) containing 3 rows of data, with one field 350 characters in length:

SMITH,123,ThisisalongtextfieldwhichisLESSTHAN255characterslong
JONES,456,ThisisalongtextfieldwhichisLESSTHAN255characterslong
DAVIES,789,ThisisalongtextfieldwhichisMORETHAN255characterslongXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXTHISIS100XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXTHISIS200XXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXTHISIS255XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXTHISIS350

 There are three different control file examples A, B and C:

- CONTROL FILE A - no length specified for field
- CONTROL FILE B - length of field specified as CHAR(400)
- CONTROL FILE C - with VARCHAR (400) defined

Each demonstrates the behavior of the SQL*Loader utility in different scenarios, highlighting potential problems and clarifying the solutions to them.

CONTROL FILE A - no length specified for field

LOAD DATA
INFILE 'longrecords.txt'
INTO TABLE <TABLE_NAME>
FIELDS TERMINATED BY ','
(
  EMP_NAME,
  EMP_ID,
  REFERENCE
)

Extract from the SQL*Loader log file for the load with CONTROL FILE A:

Table <TABLE_NAME>, loaded from every logical record.
Insert option in effect for this table: INSERT

Column Name                 Position   Len   Term Encl Datatype
--------------------------- ---------- ----- ---- ---- --------------------
EMP_NAME                    FIRST      *     ,         CHARACTER
EMP_ID                      NEXT       *     ,         CHARACTER
REFERENCE                   NEXT       *     ,         CHARACTER

Record 3: Rejected - Error on table <TABLE_NAME>, column REFERENCE.
Field in data file exceeds maximum length

Table <TABLE_NAME>:
2 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

CONTROL FILE B - length of field specified as CHAR(400)

LOAD DATA
INFILE 'longrecords.txt'
INTO TABLE <TABLE_NAME>
FIELDS TERMINATED BY ','
(
   EMP_NAME,
   EMP_ID,
   REFERENCE char(400)
)

Extract from the SQL*Loader log file for the load with CONTROL FILE B:

Table <TABLE_NAME>, loaded from every logical record.
Insert option in effect for this table: INSERT

Column Name               Position   Len   Term Encl Datatype
------------------------- ---------- ----- ---- ---- -------------------
EMP_NAME                  FIRST      *     ,         CHARACTER
EMP_ID                    NEXT       *     ,         CHARACTER
REFERENCE                 NEXT       400   ,         CHARACTER

Table <TABLE_NAME>:
3 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

CONTROL FILE C - with VARCHAR (400) defined

LOAD DATA
INFILE 'longrecords.txt'
INTO TABLE <TABLE_NAME>
FIELDS TERMINATED BY ','
(
   EMP_NAME,
   EMP_ID,
   REFERENCE varchar(400)
)

Extract from the SQL*Loader log file for the load with CONTROL FILE C:

Table <TABLE_NAME>, loaded from every logical record.
Insert option in effect for this table: INSERT

Column Name                Position   Len   Term Encl Datatype
-------------------------- ---------- ----- ---- ---- ------------------
EMP_NAME                   FIRST      *     ,         CHARACTER
EMP_ID                     NEXT       *     ,         CHARACTER
REFERENCE                  NEXT       402             VARCHAR

Record 1: Rejected - Error on table <TABLE_NAME>, column REFERENCE.
Variable length field exceeds maximum length.
Record 2: Rejected - Error on table <TABLE_NAME>, column REFERENCE.
Variable length field exceeds maximum length.
Record 3: Rejected - Error on table <TABLE_NAME>, column REFERENCE.
Variable length field exceeds maximum length.

Table <TABLE_NAME>:
0 Rows successfully loaded.
3 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

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.