Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQL-Loader: NLS_NUMERIC_CHARACTERS

Alan SearleDec 9 2011 — edited Jan 2 2012

Hi Everyone,

We receive a number of files from various locations and some have continental (decimal comma) format numerics.

I have been googling like mad and there seems to be a very clean way to handle this using "NLS_NUMERIC_CHARACTERS" and so I have been experimenting with various versions of the syntax. For example ...

...
bf_A_NUMERIC_FIELD BOUNDFILLER,
A_NUMERIC_FIELD "TO_NUMBER(:bf_A_NUMERIC_FIELD, '0D90', 'NLS_NUMERIC_CHARACTERS=''.,''')",
...

... with me also trying '9,99' and others.

However I cannot seem to get the file to load with the correct decimals and believe that it is a simple syntax issue.

The incoming files (in this case) are formatted thus ...

123.456.789,987

... with . being thousands and , being decimals.

So how should I set the two sections in the TO_NUMBER clause?

Any tips or pointers to good links would be most welcome?

Regards and thanks,
Alan Searle