Oracle SQL sqlldr not importing a latitude/longitude as float

Question

I am trying to import some data with sql*loader but I can't import a latitude/longitude. On the table those columns are FLOAT(126) and on the data file is just text. I tried with FLOAT EXTERNAL on the sqlldr control file but it doesn't work. I got ORA-01722: invalid number.

Describe my_table;

Name                      Null     Type         
------------------------- -------- ------------ 
PRE_ID                    NOT NULL NUMBER(38)   
PRE_DH                    NOT NULL TIMESTAMP(6) 
PRE_PRO                   NOT NULL NUMBER(38)   
PRE_INF                   NOT NULL NUMBER(38)   
PRE_TPL                   NOT NULL NUMBER(38)   
PRE_LAT                   NOT NULL FLOAT(126)   
PRE_LNG                   NOT NULL FLOAT(126)

data file:

55831;08/12/2016 16:48:07;1;-128;2;-22.4741249084473;-50.55194854736336
55831;09/12/2016 08:02:06;1;-128;2;-22.5002975463867;-50.8194427490234
55831;09/12/2016 19:12:06;1;-128;2;-22.5002975463867;-50.8194427490234

and sqlldr control file:

load data 
infile 'my_file.csv' "str '\r\n'"
append
into table my_table
fields terminated by ';'
trailing nullcols
           ( PRE_ID CHAR(4000),
             PRE_DH TIMESTAMP "DD/MM/YYYY HH24:MI:SS",
             PRE_PRO CHAR(4000),
             PRE_INF CHAR(4000),
             PRE_TPL CHAR(4000),
             PRE_LAT FLOAT EXTERNAL,
             PRE_LNG FLOAT EXTERNAL,
             )

log file:

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

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
PRE_ID                              FIRST  4000   ;       CHARACTER            
PRE_DH                               NEXT     *   ;       DATETIME DD/MM/YYYY HH24:MI:SS
PRE_PRO                              NEXT  4000   ;       CHARACTER            
PRE_INF                              NEXT  4000   ;       CHARACTER            
PRE_TPL                              NEXT  4000   ;       CHARACTER            
PRE_LAT                              NEXT     *   ;       CHARACTER            
PRE_LNG                              NEXT     *   ;       CHARACTER            


value used for ROWS parameter changed from 64 to 1
Record 1: Rejected - Error on table MY_TABLE, column PRE_LAT.
ORA-01722: invalid number

Record 2: Rejected - Error on table MY_TABLE, column PRE_LAT.
ORA-01722: invalid number

Record 3: Rejected - Error on table MY_TABLE, column PRE_LAT.
ORA-01722: invalid number

Answer 1

You're seeing this because the operating system environment is set up in a way that causes Oracle to treat a comma as a decimal separator and a period as a group separator. Your error messages are in English, interestingly, so not sure exactly what you have that set to, but you can see the same thing with something like NLS_LANG="FRENCH_FRANCE.WE8ISO8859P1".

From the log you can see that the field in your CSV file is being read as character data. The target column is a float (any type of number column would have the same issue), which means an implicit conversion is happening, and is using your NLS settings. You can see the same thing more simply with:

alter session set NLS_NUMERIC_CHARACTERS='.,';
select to_number('-22.4741249084473') from dual;

TO_NUMBER('-22.4741249084473')
------------------------------
             -22.4741249084473

alter session set NLS_NUMERIC_CHARACTERS=',.';
select to_number('-22.4741249084473') from dual;

Error report -
ORA-01722: invalid number

Same conversion, but the alter session is swapping the meaning of the comma and period.

You can either explicitly set your environment to something with the right NLS numeric characters via NLS_LANG:

export NLS_LANG="ENGLISH_UNITED KINGDOM.WE8ISO8859P1"

or just that specific setting:

export NLS_NUMERIC_CHARACTERS='.,'

... before running SQL*Loader.