Today I have come into a very weird behaviour of TO_NUMBER function in ODI which almost drove me mad.
I had a .csv file semi-colon separated to be loaded into an Oracle table with the following structure:
CREATE TABLE TB
I have created a very simple interface for moving data from file to the table. I tried using TO_NUMBER(REPLACE(UNIT_COST,'.',',')), but I was getting the following error:
ORA-01722: invalid number
I then tried using the NLS_NUMERIC_CHARACTERS for telling to the TO_NUMBER function to interpret the point as the decimal separator.
to_number(UNIT_COST,'999999999999999999999999999999999999D9999999999999999' ,'NLS_NUMERIC_CHARACTERS =''.,''')
And I got it working!!!
For more info, see: