My Oracle Support Banner

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

Last updated on OCTOBER 07, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.1.0.6 [Release 9.2 to 11.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Information in this document applies to any platform.

NOTE: The document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

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

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

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


In this Document
Goal
Solution

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