Numeric characters(decimal and group separators) derived from nls_numeric_characters initialization parameter. Its default value, derived from nls_territory initialization parameter, and default value of the nls_territory initialization parameter is operating system depended - derived from NLS_LANG environment variable or registry entry on Linux/Unix or Windows operating systems respectively.
You can override default value of nls_numeric_characters initialization parameter, depending o your needs, instance-wide, session-wide, or statement-wide. Here is a simple example:
/* current value: , (comma) as decimal separator and
. (period) as group separator and
*/
SQL> show parameter nls_numeric_characters
NAME TYPE VALUE
------------------------------------ ----------- -------------
nls_numeric_characters string ,.
SQL> with t1(col) as(
2 select 1.23 from dual union all
3 select 234.43 from dual
4 )
5 select col
6 from t1;
COL
----------
1,23
234,43
Session-wide:
/* set . (period) as decimal separator and
, (comma) as group separator */
SQL> alter session set nls_numeric_characters='.,';
Session altered.
SQL> with t1(col) as(
2 select 1.23 from dual union all
3 select 234.43 from dual
4 )
5 select col
6 from t1;
COL
----------
1.23
234.43
Statement-wide. In this situation we have to use to_char() function and provide number format model:
SQL> with t1(col) as(
2 select 1.23 from dual union all
3 select 234.43 from dual
4 )
5 select to_char(col, '990d00', q'[nls_numeric_characters='.,']') as res
6 , col
7 from t1;
RES COL
------- ----------
1.23 1,23
234.43 234,43
