According to the Oracle documentation,
NLS_NUMERIC_CHARACTERS stores the decimal marker used by the current session. To get the current
NLS_NUMERIC_CHARACTERS, I use this request:
select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS';
And I get this result:
So my decimal marker is 'comma'.
But when I make a SELECT request to get a decimal value, the decimal marker return by Oracle is different. If I run this dummy request:
select 3/2 decimal_value from dual;
I get this result:
So, my questions are: why is it not
1,5 as expected? What need I change to get the decimal marker as defined is
Important note: I can't change my SELECT request.
After reading this article (suggested by @EdStevens in the comments), here are some precisions.
According to the article, the decimal marker is defined by :
- The NLS values defined in the database initialization parameters
- The NLS settings of the OS of the client
- The NLS settings of the Oracle client
- The parameters of the
TO_CHARfunction if it is used in the SELECT statement
In our case, because we don't use
TO_CHAR function in the SELECT statement, the decimal marker should be defined by the NLS settings of the Oracle client (
NLS_NUMERIC_CHARACTERS) but we saw above that is not the case.
Interresting fact, if I use
TO_CHAR function in my SELECT statement:
select TO_CHAR(3/2) decimal_value from dual;
I get this result:
nlsparam is omitted, the documentation says:
If you omit
nlsparamor any one of the parameters, then this function uses the default parameter values for your session.
So, when we use
TO_CHAR function, Toad use the NLS value of the session but not in the others cases.
Is it possible that Toad performs an implicit conversion (NUMBER -> CHAR) before printing the value in the data grid without using NLS parameters? If it is the case, why this implicit conversion don't use
NLS_NUMERIC_CHARACTERS? What can I do to get my number in the correct format?
Here is a little recap of the informations present in the comments:
- The problem I expose above is made with Toad for Oracle (version 22.214.171.124) but I also reproduce this issue on the application I develop who uses OCI drivers.
- The session value of
NLS_LANGUAGEis French and
FRENCH_FRANCE.WE8MSWIN1252. Those values are defined in the Windows registry.
- If I use
SQL*Plus, I get a correct result. Indeed the decimal marker corresponds to the one defined in
NLS_NUMERIC_CHARACTERSand if I change this value, it directly changed the decimal marker I get (that is not the case with Toad).