How to use correclty TO_NUMBER() Oracle function considering NLS_NUMERIC_CHARACTERS defined on Database server?

Question

Today, I have discovered that Oracle NLS_NUMERIC_CHARACTERS is ambigously defined !

Indeed, since beginning, I thought that NLS_NUMERIC_CHARACTERS is used only to define how a decimal number is displayed.

But in reality, NLS_NUMERIC_CHARACTERS is also used implicitely in TO_NUMBER() function to define the format of STRING number to convert.

My problem is that I'm european and that for me, decimal separator is in reality a comma (,) and not a point (.).

When I display a number, my preference is to display it using a comma. For this reason NLS_NUMERIC_CHARACTERS is set to ',.' where first character define number separator.

Until now, I have no problem with that :-)

My problem is that all String table's field's values containing a number are using POINT character as decimal separator.

When I will convert any string value that represent a number, I can use TO_NUMBER Oracle function like this

select TO_NUMBER(VALUE) from TB_PARAMETER;

This works well if decimal separator is a POINT (NLS_NUMERIC_CHARACTERS = '.,') but this stop to work if decimal separator is a COMMA (NLS_NUMERIC_CHARACTERS = ',.').

I know that I can specify NLS_NUMERIC_CHARACTERS in TO_NUMBER() function like this

select TO_NUMBER(VALUE
                ,'999999999D999999999'
                ,'NLS_NUMERIC_CHARACTERS = ''.,'''
                )
  from TB_PARAMETER;

but this is to verbose for me !

Is there a Oracle function that do the same thing ?

Example:

select CAST_NUMBER(VALUE) from TB_PARAMETER;

Personnaly, I think that the fact that Oracle TO_NUMBER() function use NLS_NUMERIC_CHARACTERS session's parameter to define the format of decimal string to convert is a bug.

Indeed, in database, the decimal separator used in string is always fixed. It is a comma or a point but never, one time a comma and another time a point.

If my analyse is correct, TO_NUMBER() function must always use a fixed value that is a POINT or a COMMA that don't correspond (in all cases) to NLS_NUMERIC_CHARACTERS since this parameters has a session scope and is defined by Oracle client application.

In resume, when you are using TO_NUMBER() function without specifying any NLS_NUMERIC_CHARACTERS in an Oracle view, your view will work correclty until it is executed in a session that has another distinct NLS_NUMERIC_CHARACTERS value !

The same thing certainly exists when you try to convert DATE value saved as string using Oracle TO_DATE() function !

Why it is ambigous !

To answer to some questions in comment, I have added my long explanation in following paragraphs.

Changing NLS_NUMERIC_CHARACTERS at session level or system level is not always possible because NLS_NUMERIC_CHARACTERS has 2 distinct roles or goals.

1. fixing decimal separator display of decimal numbers
2. fixing decimal separator used to convert string in TO_NUMBER() function

Example: suppose that you will create a view that will display decimal numbers and that use TO_NUMBER() to make some calculation.

If decimal separator saved in database is POINT and if it is necessary that the decimal numbers are displayed using COMMA as decimal separator, you can change NLS_NUMERIC_CHARACTERS to define decimal separator as POINT. Oracle TO_NUMBER() function will work correclty but the decimal numbers display on screen will be displayed with POINT as decimal separator !

That's why I say that NLS_NUMERIC_CHARACTERS is ambiguous.

In resume, in a database system where NLS_NUMERIC_CHARACTERS can be '.,' or ',.' AND where decimal number can be saved in Oracle table using a well fixed format (example: decimal separator is POINT) it is unsafe to use TO_NUMBER() without specifying the correct NLS_NUMERIC_CHARACTERS.

The Oracle bug (or misconception if you prefer) is to have defined a parameter (NLS_NUMERIC_CHARACTERS) with 2 distinct roles !

Answer 1

This may be a bit late, but what I found is:

To improve performance, as a preparation, do something like

select value into v_nls_numeric_characters
from nls_session_parameters
where parameter = 'NLS_NUMERIC_CHARACTERS';

Then, if you know that your input a COMMA means a decimal symbol and a POINT means a group separator (e.g. German number format), use this:

to_number(translate(pi_input_text, ',.', v_nls_numeric_characters))

If you are expecting american numbers (a POINT means a decimal symbol and a COMMA means a group separator, use this:

to_number(translate(pi_input_text, '.,', v_nls_numeric_characters))

Answer 2

A function which will always do the right thing, regardless of client NLS settings and regardless of input string,

declare
  mynum  number;

  FUNCTION TO_NUMBER_FNLS(sNumber in varchar2) return number as
      ret  number;
  BEGIN
      select
           to_number(
             case substr(nls.value,1,1)
               when ',' then replace(sNumber, '.', ',')
               else          replace(sNumber, ',', '.')
             end
           )
           into ret
      from nls_session_parameters nls
      where nls.parameter = 'NLS_NUMERIC_CHARACTERS';

      return ret;
  END;

begin
  mynum := TO_NUMBER_FNLS('100.22');
  DBMS_OUTPUT.PUT_LINE('input with dot: '|| mynum);

  mynum := TO_NUMBER_FNLS('100,22');
  DBMS_OUTPUT.PUT_LINE('input with comma: '|| mynum);
end;
/

https://dbfiddle.uk/dim1wC2W

Answer 3

For information, I have created followed function

CREATE FUNCTION TO_X_NUMBER(sNumber IN VARCHAR2) 
  RETURN NUMBER as

BEGIN
    RETURN TO_NUMBER
        (sNumber
        ,'99999999999999999999D99999999999999999999'
        ,'NLS_NUMERIC_CHARACTERS=''.,''');
END TO_X_NUMBER;

and synonym

CREATE PUBLIC SYNONYM TO_X_NUMBER FOR GWHDBA.TO_X_NUMBER; 

GRANT EXECUTE ON TO_X_NUMBER TO PUBLIC; 

I can now use it from another schema as in following example:

SELECT TO_X_NUMBER(PARAM_VALUE) as STANDARD_DEVIATION
  FROM TB_PARAMETER
  WHERE PARAM_NAME = 'STANDARD_DEVIATION';

Answer 4

Well, from my point of view, the best option is to keep numbers in NUMBER datatype columns. How will you apply TO_NUMBER to value = '32.5rzg'?

Other than that, I presume that what you already discovered (applying NLS_NUMERIC_CHARACTERS) is what you can do, apart from using REPLACE and convert all points to commas throughout those string values.