ORA-1722 or ORA-12899 when Loading Valid Numbers and Characters (Doc ID 725966.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 05-Aug-2013***

Symptoms

You try to load an ASCII flat file into a table and you receive for last columns strange errors:

1. Loading NUMBER values into last column

Given is the following table:

create table tab001
(
   id       number,
   text     varchar2(10),
   last_col number
);


The SQL*Loader control file LDR1.CTL:

load data
infile 'ldr1.dat'
truncate
into table tab001
fields terminated by ';'
trailing nullcols
(
   id,
   text,
   last_col
)


The content of input flat file LDR1.DAT:

1;Row 1;1
2;Row 2;1
3;Row 3;1


Start SQL*Loader with command line:

#> sqlldr test/passwd control=ldr1.ctl


Errors mentioned in SQL*Loader log file:

Table TAB001, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

Column Name                    Position   Len   Term Encl Datatype
------------------------------ ---------- ----- ---- ---- -------------------
ID                                  FIRST     *    ;      CHARACTER
TEXT                                 NEXT     *    ;      CHARACTER
LAST_COL                             NEXT     *    ;      CHARACTER

Record 1: Rejected - Error on table TAB001, column LAST_COL.
ORA-01722: invalid number

Record 2: Rejected - Error on table TAB001, column LAST_COL.
ORA-01722: invalid number

Record 3: Rejected - Error on table TAB001, column LAST_COL.
ORA-01722: invalid number


2. Loading VARCHAR2 values into last column

Given is the following table:

create table tab001
(
   id       number,
   text     varchar2(10),
   last_col varchar2(13)
);


The SQL*Loader control file LDR1.CTL:

load data
infile 'ldr2.dat'
truncate
into table tab001
fields terminated by ';'
trailing nullcols
(
   id,
   text,
   last_col
)


The content of input flat file LDR2.DAT:

1;Row 1;Last column 1
2;Row 2;Last column 2
3;Row 3;Last column 3


Start SQL*Loader with command line:

#> sqlldr test/passwd control=ldr2.ctl


Errors mentioned in SQL*Loader log file:

Table TAB001, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

Column Name                    Position   Len   Term Encl Datatype
------------------------------ ---------- ----- ---- ---- -------------------
ID                                  FIRST     *    ;      CHARACTER
TEXT                                 NEXT     *    ;      CHARACTER
LAST_COL                             NEXT     *    ;      CHARACTER

Record 1: Rejected - Error on table TAB001, column LAST_COL.
ORA-12899: value too large for column "TEST"."TAB001"."LAST_COL" (actual: 14, maximum: 13)

Record 2: Rejected - Error on table TAB001, column LAST_COL.
ORA-12899: value too large for column "TEST"."TAB001"."LAST_COL" (actual: 14, maximum: 13)

Record 3: Rejected - Error on table TAB001, column LAST_COL.
ORA-12899: value too large for column "TEST"."TAB001"."LAST_COL" (actual: 14, maximum: 13)

Cause

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