Because 1.25 isn't actually a number

Prompted by a stackoverflow post, here's a little demo of the role of NLS_NUMERIC_CHARACTERS in a TO_CHAR.


SQL> alter session set nls_numeric_characters=',.';


Session altered.


SQL> select to_char(1.25) from dual;


TO_C
----
1,25

So what happens ?

Firstly 1.25 might be a number to us, it isn't actually a NUMBER. It is a numeric literal representing the value "1 and a quarter". Oracle's first step is to convert it into its internal representation.

SQL> select dump(1.25) from dual;

DUMP(1.25)

---------------------

Typ=2 Len=3: 193,2,26

To paraphrase Crocodile Dundee, "Now, that's a NUMBER". Okay, it's actually the dump of the NUMBER but it is a condition of Australian citizenship that you work a Mick Dundee reference in at least once a year.

The same concept applied to date literals, such as  date '2011-02-13' but the 'date' keyword makes it more obvious to us that we are telling Oracle that it is something special.

The NLS values don't play any part in this conversion to the internal format. The internal value itself is NLS neutral so it doesn't have the concept of a period or a comma as a decimal separator.

The NLS settings do play a part in the TO_CHAR conversion from the internal format. That's where the comma is being formatted into the string. If you really want to be sure about the formatting, you can add this to the TO_CHAR, and the matter will be settled once and for all.

select to_char(1.25,'999.99','NLS_NUMERIC_CHARACTERS=.,') from dual;