1

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:

VALUE
, 

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:

DECIMAL_VALUE
1.5

So, my questions are: why is it not 1,5 as expected? What need I change to get the decimal marker as defined is NLS_NUMERIC_CHARACTERS?

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 :

  1. The NLS values defined in the database initialization parameters
  2. The NLS settings of the OS of the client
  3. The NLS settings of the Oracle client
  4. The parameters of the TO_CHAR function 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:

DECIMAL_VALUE
1,5

If nlsparam is omitted, the documentation says:

If you omit nlsparam or 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 9.1.0.62) but I also reproduce this issue on the application I develop who uses OCI drivers.
  • The session value of NLS_TERRITORY is France, NLS_LANGUAGE is French and NLS_LANG is 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_CHARACTERS and if I change this value, it directly changed the decimal marker I get (that is not the case with Toad).
  • 1
    What is the value of NLS_LANGUAGE and NLS_TERRITORY ? I cannot replicate your results with Oracle 12c. – Mark Stewart Jul 20 '18 at 13:51
  • @MarkStewart NLS_LANGUAGE is FRENCH and NLS_TERRITORY is FRANCE. – Pierre Jul 20 '18 at 13:52
  • 2
    What client are you using? – Michael Kutz Jul 20 '18 at 13:53
  • 2
    @Pierre - It is the application's responsibility to translate the data Typ=2 Len=3: 193,2,51 into strings 1.5 or 1,5, not the RDBMS. Check with SQL*Plus and/or SQL*Developer. – Michael Kutz Jul 20 '18 at 14:04
  • 1
    @MarkStewart I tested to change the value of NLS_LANG in the registry and it has an impact on the results I got from Toad and also from my application who uses OCI. – Pierre Jul 20 '18 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.