NLS NUMERIC CHARACTERS setting for decimal

Question

I have one db setup in a test machine and second in production machine  When I run   select to number  100 12   from dual    Then it gives error in test machine  However  this statement works quite fine in production machine   Now  when I check for NLS NUMERIC CHARACTERS then I see      comma  in both machine  Is there anywhere else I should be looking for the decimal setting   Cheers

User · Accepted Answer

You can see your current session settings by querying nls_session_parameters:

select value
from nls_session_parameters
where parameter = 'NLS_NUMERIC_CHARACTERS';

VALUE                                  
----------------------------------------
.,

That may differ from the database defaults, which you can see in nls_database_parameters.

In this session your query errors:

select to_number('100,12') from dual;

Error report -
SQL Error: ORA-01722: invalid number
01722. 00000 -  "invalid number"

I could alter my session, either directly with alter session or by ensuring my client is configured in a way that leads to the setting the string needs (it may be inherited from a operating system or Java locale, for example):

alter session set NLS_NUMERIC_CHARACTERS = ',.';
select to_number('100,12') from dual;

TO_NUMBER('100,12')
-------------------
             100,12

In SQL Developer you can set your preferred value in Tool->Preferences->Database->NLS.

But I can also override that session setting as part of the query, with the optional third nlsparam parameter to to_number(); though that makes the optional second fmt parameter necessary as well, so you'd need to be able pick a suitable format:

alter session set NLS_NUMERIC_CHARACTERS = '.,';
select to_number('100,12', '99999D99', 'NLS_NUMERIC_CHARACTERS='',.''')
from dual;

TO_NUMBER('100,12','99999D99','NLS_NUMERIC_CHARACTERS='',.''')
--------------------------------------------------------------
                                                        100.12

By default the result is still displayed with my session settings, so the decimal separator is still a period.

User · Answer

To know SESSION decimal separator  you can use following SQL command   ALTER SESSION SET NLS NUMERIC CHARACTERS          select SUBSTR value 1 1  as  SEPARATOR          using NLS-PARAMETER  as  Explanation    from nls session parameters   where parameter    NLS NUMERIC CHARACTERS   UNION ALL  select SUBSTR 0 5 1 1  as  SEPARATOR           using NUMBER IMPLICIT CASTING  as  Explanation    from DUAL    The first SELECT command find NLS Parameter defined in NLS SESSION PARAMETERS table  The decimal separator is the first character of the returned value   The second SELECT command convert IMPLICITELY the 0 5 rational number into a String using  by default  NLS NUMERIC CHARACTERS defined at session level   The both command return same value   I have already tested the same SQL command in PL SQL script and this is always the same value COMMA or POINT that is displayed  Decimal Separator displayed in PL SQL script is equal to what is displayed in SQL   To test what I say  I have used following SQL commands   ALTER SESSION SET NLS NUMERIC CHARACTERS          select  DECIMAL-SEPARATOR on CLIENT        TO CHAR  5          from dual   DECLARE     S VARCHAR2 10          BEGIN      select  5 INTO S from dual       DBMS OUTPUT PUT LINE  DECIMAL-SEPARATOR in PL SQL        S          END      The shorter command to know decimal separator is   SELECT  5 FROM DUAL    That return 0 5 if decimal separator is a COMMA and 0 5 if decimal separator is a POINT

User · Answer

Jaanna  the session parameters in Oracle SQL Developer are dependent on your client computer  while the NLS parameters on PL SQL is from server   For example the NLS NUMERIC CHARACTERS on client computer can be      while it s      on server    So when you run script from PL SQL and Oracle SQL Developer the decimal separator can be completely different for the same script  unless you alter session with your expected NLS NUMERIC CHARACTERS in the script    One way to easily test your session parameter is to do   select to number 5 2  from dual

User · Answer

Best way is   SELECT to number replace  Str         100  --into num2  FROM dual

[oracle] NLS_NUMERIC_CHARACTERS setting for decimal

Examples related to oracle

Examples related to oracle11g

Examples related to oracle-sqldeveloper