error when insert number via SQLloader

0
votes

I am trying to insert a numerical value into Oracle DB via SQL*loader but I still get the error:

Record 1: Rejected - Error on table my_table, column column_1. ORA-01722: invalid number

the line form SQL*loader which inserts the value is

column_1  INTEGER  "TO_NUMBER(REPLACE(:column_1, ' ', ''), '99999999D99')"

while the value I need to insert is: 30 000 000,00

If I remove the mask ('99999999D99'), it inserts a number but is something wrong. The inserted value is: 807415859

Can someone help me to understand what am I doing wrong?

2
oracleinsertsql-loader
Did you try changing the mask to '99 999 999D99' instead of doing the REPLACE? Your SQL*Loader line would then look like - column_1 INTEGER "TO_NUMBER(:column_1, '99 999 999D99')" - vmachan
what's the actual text, that corresponds to the inserted value 807415859 ? - Maheswaran Ravisankar
@Maheswaran Ravisankar the actual value from .csv file is '30 000 000,00' - mikcutu
@vmachan: if I apply the change you mentioned, I have this error: Record 1: Rejected - Error on table my_table, column column_1. ORA-01481: invalid number format model - mikcutu

2 Answers

0
votes

After few more tests, the answer is:

column_1 "to_number(replace(replace(:column_1, ',', '.'), ' ', ','), '99999999.99')"
0
votes

You might find the TRANSLATE() function easier. Converts comma to period and space to NULL in one step. Test like this:

SQL> select translate('30 000 000,00', ', ', '.') fixed
    from dual;

FIXED
-----------
30000000.00

SQL>

Full answer then:

column_1 "to_number(translate(:column_1, ', ', '.'), '99999999.99')"

Be sure to add a comment that the space needs to be last!