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.