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.
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 !
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';