how to load rational numbers in Oracle database

I have a data file with numbers with this format :

xx.xx (example : 0.3)

I have to transform them with a comma instead of the point to be loaded in the database. Here is my control file:

LOAD DATA
REPLACE
INTO TABLE USER.MYTABLE
FIELDS TERMINATED BY '$'
TRAILING NULLCOLS
(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,
NUMBER_FIELD "to_number(replace(:NUMBER_FIELD, '.', ','))",
FIELD6,FIELD7,FIELD8)

It works good but 0.3 becomes ,3 with no zero. What is wrong there ?

Thank you

I changed my control file as suggested :

LOAD DATA
REPLACE
INTO TABLE USER.MYTABLE
FIELDS TERMINATED BY '$'
TRAILING NULLCOLS
(FIELD1 DECIMAL(6,0),
FIELD2 VARCHAR,
FIELD3 VARCHAR,
FIELD4 VARCHAR,
FIELD5 VARCHAR,
FIELD6 FLOAT,
FIELD7 FLOAT,
FIELD8 DATE,
FIELD9 VARCHAR)

data lines like this :

1$005$05$1$blablabla$0.50$0$01/02/1996$01

I have errors.

Log file :

   Column Name                  Position   Len  Term Encl Datatype

FIELD1     FIRST     4           PACKED DECIMAL (6, 0)
FIELD2     NEXT     *           VARCHAR              
FIELD3     NEXT     *           VARCHAR              
FIELD4                                   NEXT     *           VARCHAR              
FIELD5     NEXT     *           VARCHAR              
FIELD6     NEXT     4           FLOAT                
FIELD7     NEXT     4           FLOAT                
FIELD8     NEXT     *   $       DATE DD/MM/YYYY      
FIELD9     NEXT     *           VARCHAR

I dont know how the lenght is calculated but it is wrong
I have error on FIELD1 : Value larger than specified precision allows for this column.
Error on date field says that the day of the month must be between 1 and last day of the month.

Solution

The session you are querying the number in is formatting it as ,3 partly because of your NLS settings. From your profile you are in France so your NLS_NUMERIC_CHARACTER_SETTING will be ',.', so the decimal separator is a comma. In SQL*Plus and SQL Developer worksheets the default number format doesn't show a leading zero. If you want to see the leading zero you need to specify the format:

select to_char(number_field, 'FM9990D999') from mytable

which will use your session's decimal separator; or force that with:

select to_char(number_field, 'FM9990D999', 'NLS_NUMERIC_CHARACTERS='',.''') from mytable

The FM stops is leaving trailing zeros after the decimal point; if you want to see those then leave that modifier out.

The conversion you are doing on load shouldn't be necessary; you can change the control file to have:

NUMBER_FIELD FLOAT,

and it will be automatically treated as a number (which always has a period decimal separator). That is cleaner than treating the :NUMBER_FIELD as a string, doing the 'decimal' replacement, and then converting to a number using your NLS settings.

You could also specify the NLS settings the other way:

NUMBER_FIELD "to_number(:NUMBER_FIELD, '9999D999', 'NLS_NUMERIC_CHARACTERS=''.,''')",

When you specify a number format, for to_number() or to_char(), you obviously need to allow enough 0/9 placeholder each side of the decimal character for the data you actually have. If you don't know, or don't want to specify, the number of digits then you could set NLS_LANG to a territory that uses a period as a decimal separator before running SQL*Loader, and just do "to_number(:NUMBER_FIELD)". Using FLOAT is simpler though.