NLS_NUMERIC_CHARACTERS as easy as possible

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                 
------------------------------ ----------------------

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;

--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;


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                    
------------------------------ -------------------------

Have a fun 🙂








2 thoughts on “NLS_NUMERIC_CHARACTERS as easy as possible

  1. Good afternoon Tomasz

    Very good Article.

    I have one column “a_valor varchar2(35 byte)”, some rows in this field have decimals, when executed the query en sql developer works well, if the execute from JPA in my server weblogic it also works well, but en other server weblogic what is in the cloud and point at the same database generate the error: “java.sql.SQLSyntaxErrorException: ORA-01722: invalid number”
    The line that generate the error is:

    ROUND((pe.A_VALOR * 12 )) AS q_periodos

    My question is: if I change the line above by:

    ROUND(((TO_NUMBER(pe.A_VALOR)) * 12 )) AS q_periodos

    The error mentionated is solved or at to_number I must add parameters

    Thank you for your support and sorry my english.

    Jimmy Velasquez

  2. Hello there
    I’ve tried your first Solution , it didn’t Workout , the NLS_NUMERIC_CHARACTER not changing specially when i control it from an oracle apex database session’s part.
    and there are a limitation when we are using the nls_territory that will create a Shadow application for oracle apex.
    how we can go forward through this.


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.