CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle11gr2
Mehmet Balioglu
Mehmet Balioglu

Reputation: 2302

Oracle Invalid Number error with NLS_NUMERIC_CHARACTERS

I have a varchar2 column that I need to convert to number

The format is as follows:

+000000000000030203.38

Database level: NLS_NUMERIC_CHARACTERS = .,

Session Level: NLS_NUMERIC_CHARACTERS = ,.

I have tried: 

select to_number('+000000000000030203.38','S999999999999999999D99', 'NLS_NUMERIC_CHARACTERS='',.''') from MYTAB t

But it throws invalid number error.

What are the correct parameters of to_number function here?

Upvotes: 2

Views: 2333

Answers (1)

David Aldridge
David Aldridge

Reputation: 52346

The first element of the NLS_NUMERIC_CHARACTERS is the decimal separator, the second is the thousands separator.

Your supplied NLS_NUMERIC_CHARACTERS takes precedence, so you're saying that numbers will be expressed with comma as the decimal separator and comma as the thousands separator.

Use the 'NLS_NUMERIC_CHARACTERS=''.,''' instead.

Upvotes: 3

Related Questions