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 !

Solution 1

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

Solution 2

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.

Comments

[Wernfried Domscheit]

The (actually your) bug is when you store numeric values as strings.

[Alex Poole]

Using the session parameter for the conversion isn't a bug, it's the documented behaviour 8-) Maybe in *your database the decimal separator used in strings is always the same character, but that isn't a general rule; and there's probably nothing enforcing that even in your DB. (And you shouldn't really store numbers as strings anyway, of course.) Why is it 'ambiguously defined' though?

[schlebe]

@Wernfried: why I use String to save number value is explained in my comment of Littlefoot answer.

[schlebe]

@Alex Poole: it is a conceptual feature bug that SqlServer doesn't make ! If saving number in string is allowed, it is "stupid" to save it one time using point as decimal separator and one time using comma. But if the case you described can occurs, the format doesn't certainly depend on NLS_NUMERIC_CHARACTERS !

[Alex Poole]

It depends how it's saved; if you did update some_table set string_col = 123.456 then the implicit conversion to string will depend on the session NLS settings too, and the string could get a period or a comma. If the application relies on storing and interpreting numbers as strings then it needs to control how it's done, e.g. with wrapper functions/procedures around the table insert/update/select operations to remove the ambiguity (or "stupidity", if you prefer) that the data model introduces.

[schlebe]

We use JPA to save field's values and assigning a string from a number value is managed in java code. But you have right, assigning a number to string on Oracle is not simple. That's the reason of my question and asnwer :-)

[William Robertson]

You can change the default setting for NLS_NUMERIC_CHARACTERS at the session or system level. Perhaps this is what you are looking for? I am struggling to see what is ambiguous or a bug, though.

[schlebe]

@William: I have answered to your comment in last part of Question because I have used to more characters :-)

[schlebe]

I will precise that we use a TB_PARAMETER table with NAME and VALUE fields (to keep simple). The VALUE is defined as VARCHAR2(200) and can contains String, Number, Integer and Date. This technic is very common to save parameters. Another technic is to define TYPE fields and to add INTEGER-VALUE, NUMBER-VALUE NUMBER, DATE-VALUE fields with corresponding type. The problem is that I'm not responsible of the defintation of TB_PARAMETER table.

[schlebe]

I will add that your solution (using REPLACE('.',',')) work for a single SELECT that I can type in SqlPlus or SqlDeveloper but don't work in a view that can be executed by any users !

[Littlefoot]

Ah, parameters ... I understand, I've already seen the same principle, storing everything into the same VARCHAR2 column. Consider altering a table and specifying "real" datatype for that value. Create a function which returns value for a certain parameter. Depending on the "real" datatype indicator, apply different functions (TO_NUMBER, TO_DATE, ...) with appropriate format masks. In other words, don't write SELECT every time you need to fetch something from the TB_PARAMETER table - use a function instead.

[schlebe]

Yes, I use a function has explained in next answer.

[Littlefoot]

OK, so - that's it, right? Solved? Because, if you still expect some built-in Oracle function to do that, I don't know of any. Someone else might.

[schlebe]

your REPLACE solution work ... on SqlServer because CAST or CONVERT functions consider always that numbers in string have a POINT as decimal separator !

[schlebe]

This is not a problem. In one year we will migrate from Oracle to SqlServer