SQL*Loader - How to Avoid Messages 'Error Converting Character Length Field to a Number' (Doc ID 471323.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.6
Information in this document applies to any platform.

Goal

You try to load a table with records from an ASCII flat file using VARCHARC dataytpes, like in example below. When the field is empty (BLANKS) then the error:

Record 2: Rejected - Error on table TAB001, column COL002.
error converting character length field to a number

is put into log file and the record is not loaded.

The table definition is this:

CREATE TABLE TAB001
(
   COL001 NUMBER,
   COL002 VARCHAR2(100)
);

The control file is this:

LOAD DATA
INFILE 'ldr.dat'
TRUNCATE
INTO TABLE TAB001
(
   COL001 POSITION(1:5),
   COL002 POSITION(6:40) VARCHARC(5,30)
)

The ASCII flat file 'ldr.dat' with input records (including ruler to help column positioning) is this:

1        10        20        30        40
|        |         |         |         |
||||||||||||||||||||||||||||||||||||||||
0000100024This is the first record
00002     ?
00003   10Small text
0000400000Example 4
0000500029? Question mark at first pos.

You changed the controfile to this (see NULLIF clause):

LOAD DATA
INFILE 'ldr.dat'
TRUNCATE
INTO TABLE TAB001
(
   COL001 POSITION(1:5),
   COL002 POSITION(6:40) VARCHARC(5,30) NULLIF (11:11) = '?'
)

but the errors still persist.

Solution

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 hundreds of Community platforms