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!

Accepted Answer

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.

NLS_NUMERIC_CHARACTERS setting for decimal

Solution

Problem