In this article I present how NLS_NUMERIC_CHARACTERS parameter works.

This parameter specifies the characters to use as group separator and decimal character. The group separator separates integer groups (that is, thousands, millions, billions, and so on). The decimal separates the integer portion of a number from the decimal portion.

NLS_NUMERIC_CHARACTERS = “decimal_character group_separator”

Let’s prepare some test data

We need to create table with two fields id and id1 both number type

create table test_tbl(id number, id1 number);

then we set on session level NLS_NUMERIC_CHARACTER to ‘,.’ so our decimal separator is ‘,’ and group separator is “.”. Your current settings for session are visible in view NLS_SESSION_PARAMETERS.

alter session set NLS_NUMERIC_CHARACTERS=',.'

select * from nls_session_parameters
where parameter='NLS_NUMERIC_CHARACTERS';
session SET altered.
PARAMETER                      VALUE                 
------------------------------ ----------------------
NLS_NUMERIC_CHARACTERS         ,.

When you execute INSERT of data type NUMBER into field type NUMBER then NLS_NUMERIC_CHARACTERS are ignored.

insert into test_tbl values(1.23, 1000000.12); 
1 rows inserted. commit; committed.

in other case Oracle would not be able to decide how fields are separated

insert into test_tbl values(1,23, 100000,12);
SQL Error: ORA-00913: too many values

update test_tbl
set id=1,4;
SQL Error: ORA-01747: invalid user.table.column, table.column, 
                      or column specification

delete from test_tbl
where id=1,4;
SQL Error: ORA-00933: SQL command not properly ended

but INSERT of type CHAR into type NUMBER will only work if your INSERT is specified according to NLS_NUMERIC_CHARACTER.

insert into test_tbl values('1,23', '100000,12');
1 rows inserted

rollback; <- I don't need this we still have previous record
rollback complete.

display of course will show NUMBER type according to NLS_NUMERIC_CHARACTER settings because is converted to char

select * from test_tbl;

        ID        ID1
---------- ----------
      1,23  100000,12

Somebody can ask what about ID1 filed why we see 100000,12 and not 100.000,12. Because group separator is important only during explicit conversions NUMBER<->CHAR.

Special letters are used here:

G – group separator

D – decimal separator

Here are some examples. Please notice that you can specify NLS_NUMERIC_CHARACTER on sql statement level (last example).

alter session set NLS_NUMERIC_CHARACTERS=',.';
Session altered.

select id, id1, TO_CHAR(id1,'999G999D99') from test_tbl;
        ID        ID1 TO_CHAR(ID1
---------- ---------- -----------
      1,23  100000,12  100.000,12

--conversion explicit to number then implicit to char so
--group separator is gone :)
select TO_NUMBER ('13.000,11', '99G999D99') n from dual;
         N
----------
  13000,11

--conversion explicit to number is wrong 
select TO_NUMBER ('13,000.11', '99G999D99') n from dual;
ORA-01722: invalid number

--conversion explicit to number then implicit to char 
select TO_NUMBER('13,000.11', '99G999D99', 
                 'nls_numeric_characters=''.,''') n 
from dual;
         N
----------
  13000.11

NLS_TERRITORY influence

Important fact – every time you change in your session NLS_TERRITORY NLS_NUMERIC_CHARACTERS is changed as well.

NLS_TERRITORY has got influence on many NLS session parameters however it’s different story for next article :).

alter session set nls_territory=Poland;
session SET altered.

select * from nls_session_parameters 
where parameter='NLS_NUMERIC_CHARACTERS';

PARAMETER                      VALUE                    
------------------------------ -------------------------
NLS_NUMERIC_CHARACTERS         ,                       

alter session set nls_territory=America;
session SET altered.

select * from nls_session_parameters 
where parameter='NLS_NUMERIC_CHARACTERS';

PARAMETER                      VALUE                    
------------------------------ -------------------------
NLS_NUMERIC_CHARACTERS         .,

Have a fun 🙂

Tomasz