oracle - NLS_NUMERIC_CHARACTERS setting for decimal

Question

I have one db setup in a test machine and second in production machine. When I run:

select to_number('100,12') from dual 

Then it gives error in test machine. However, this statement works quite fine in production machine.

Now, when I check for NLS_NUMERIC_CHARACTERS then I see ',' (comma) in both machine. Is there anywhere else I should be looking for the decimal setting?

Cheers!

Answer 1

You can see your current session settings by querying nls_session_parameters:

select value
from nls_session_parameters
where parameter = 'NLS_NUMERIC_CHARACTERS';

VALUE                                  
----------------------------------------
.,                                       

That may differ from the database defaults, which you can see in nls_database_parameters.

In this session your query errors:

select to_number('100,12') from dual;

Error report -
SQL Error: ORA-01722: invalid number
01722. 00000 -  "invalid number"

I could alter my session, either directly with alter session or by ensuring my client is configured in a way that leads to the setting the string needs (it may be inherited from a operating system or Java locale, for example):

alter session set NLS_NUMERIC_CHARACTERS = ',.';
select to_number('100,12') from dual;

TO_NUMBER('100,12')
-------------------
             100,12 

In SQL Developer you can set your preferred value in Tool->Preferences->Database->NLS.

But I can also override that session setting as part of the query, with the optional third nlsparam parameter to to_number(); though that makes the optional second fmt parameter necessary as well, so you'd need to be able pick a suitable format:

alter session set NLS_NUMERIC_CHARACTERS = '.,';
select to_number('100,12', '99999D99', 'NLS_NUMERIC_CHARACTERS='',.''')
from dual;

TO_NUMBER('100,12','99999D99','NLS_NUMERIC_CHARACTERS='',.''')
--------------------------------------------------------------
                                                        100.12 

By default the result is still displayed with my session settings, so the decimal separator is still a period.