Error loading with Oracle sql loader

Question

I really dont see where is the problem. I hope you can help.

Here is data file :

01/04/2013$1.300
01/10/2015$0.100
01/12/2016$0.500

This file has to be loaded in TableA :

2 columns : thedate (type DATE) and therate (type NUMBER(5,3))

Here is the ctl file :

LOAD DATA
REPLACE
INTO TABLE TABLEA
FIELDS TERMINATED BY '$'
TRAILING NULLCOLS
(THEDATE,
THERATE "to_number(:THERATE, '99999D999', 'NLS_NUMERIC_CHARACTERS=''.,''')")

Loading, I have this error in the log on all records :

Column Name                  Position   Len  Term Encl Datatype

THEDATE                             FIRST     *   $       CHARACTER            
THERATE                             NEXT     *   $       CHARACTER            
SQL string for column : "to_number(:THERATE, '99999D999', 'NLS_NUMERIC_CHARACTERS=''.,''')"

Record 1: Rejected - Error on table TABLEA, column THERATE. ORA-01438: value larger than specified precision allowed for this column

There must be more data. Given data set get loaded successfully. — atokpas

atokpas atokpas · Accepted Answer · 2016-11-25T10:22:01

The same code(just few modification but not relevant to your error) works fine for me.

[oracle@localhost Desktop]$ cat data.txt 
01/04/2013$1.300
01/10/2015$0.100
01/12/2016$0.500

[oracle@localhost Desktop]$ cat control.ctl 
LOAD DATA
INFILE 'data.txt'
REPLACE
INTO TABLE TABLEA
FIELDS TERMINATED BY '$'
TRAILING NULLCOLS
(THEDATE "to_date(:THEDATE, 'MM-DD-YYYY')",
THERATE "to_number(:THERATE, '99999D999', 'NLS_NUMERIC_CHARACTERS=''.,''')")

[oracle@localhost Desktop]$ sqlldr jay/jay control file=control.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Fri Nov 25 16:04:50 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4
[oracle@localhost Desktop]$ sqlplus jay/jay

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 25 16:04:56 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from tablea;                   

THEDATE      THERATE
--------- ----------
04-JAN-13        1.3
10-JAN-15         .1
12-JAN-16         .5

Error loading with Oracle sql loader

1 Answers