[oracle] NLS_NUMERIC_CHARACTERS setting for decimal

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!

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.

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;

Best way is,

SELECT to_number(replace(:Str,',','')/100) --into num2 
FROM dual;