Setting NLS_NUMERIC_CHARACTERS for decimal

Question

I have one db installation in a test machine, and the second in a production machine. When I run:

select to_number('100,12') from dual 

Then it gives an error in the test machine. However, this expression works fine on a production machine.

Now, when I check NLS_NUMERIC_CHARACTERS, I see "," (comma) on both machines. Anywhere else should I look for a decimal setting?

Hooray!

Answer 1

You can view the current session settings by nls_session_parameters :

 select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'; VALUE ---------------------------------------- ., 

This may differ from the default values ​​for the database, which you can see in nls_database_parameters .

In this session, request errors:

 select to_number('100,12') from dual; Error report - SQL Error: ORA-01722: invalid number 01722. 00000 - "invalid number" 

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

 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 the preferred value in Tool-> Preferences-> Database-> NLS.

But I can also override this session parameter as part of the request, with the optional third parameter nlsparam to to_number() ; although this also makes the second fmt parameter optional, so you will need to choose the appropriate 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 the settings for my session, so the decimal separator is still a period.

Answer 2

Jaanna, the session parameters in Oracle SQL Developer depend on your client computer, and the NLS parameters on PL / SQL are the server.

For example, NLS_NUMERIC_CHARACTERS on the client computer might be ',.' and this is ".", "on the server".

Therefore, when you run a script from PL / SQL and Oracle SQL Developer, the decimal separator may be completely different for the same script, unless you change the session with the expected NLS_NUMERIC_CHARACTERS in the script.

One way to easily check your session parameter:

 select to_number(5/2) from dual;