How to efficiently convert text to number in Oracle PL/SQL with non-default NLS_NUMERIC_CHARACTERS?

Question

I'm trying to find an efficient, generic way to convert from string to a number in PL/SQL, where the local setting for NLS_NUMERIC_CHARACTERS settings is inpredictable -- and preferable I won't touch it. The input format is the programming standard "123.456789", but with an unknown number of digits on each side of the decimal point.

select to_number('123.456789') from dual;
  -- only works if nls_numeric_characters is '.,'

select to_number('123.456789', '99999.9999999999') from dual;
  -- only works if the number of digits in the format is large enough
  -- but I don't want to guess...

to_number accepts a 3rd parameter but in that case you to specify a second parameter too, and there is no format spec for "default"...

select to_number('123.456789', null, 'nls_numeric_characters=''.,''') from dual;
  -- returns null

select to_number('123.456789', '99999D9999999999', 'nls_numeric_characters=''.,''') from dual;
  -- "works" with the same caveat as (2), so it's rather pointless...

There is another way using PL/SQL:

CREATE OR REPLACE
FUNCTION STRING2NUMBER (p_string varchar2) RETURN NUMBER
IS
  v_decimal char;
BEGIN
  SELECT substr(VALUE, 1, 1)
  INTO v_decimal
  FROM NLS_SESSION_PARAMETERS
  WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';
  return to_number(replace(p_string, '.', v_decimal));
END;
/

select string2number('123.456789') from dual;

which does exactly what I want, but it doesn't seem efficient if you do it many, many times in a query. You cannot cache the value of v_decimal (fetch once and store in a package variable) because it doesn't know if you change your session value for NLS_NUMERIC_CHARACTERS, and then it would break, again.

Am I overlooking something? Or am I worrying too much, and Oracle does this a lot more efficient then I'd give it credit for?

Answer 1

Sorry, I noticed later that your question was for the other way round. Nevertheless it's noteworthy that for the opposite direction there is an easy solution:

A bit late, but today I noticed the special format masks 'TM9' and 'TME' which are described as "the text minimum number format model returns (in decimal output) the smallest number of characters possible." on https://docs.oracle.com/cloud/latest/db112/SQLRF/sql_elements004.htm#SQLRF00210.

It seems as if TM9 was invented just to solve this particular problem:

select to_char(1234.5678, 'TM9', 'NLS_NUMERIC_CHARACTERS=''.,''') from dual;

The result is '1234.5678' with no leading or trailing blanks, and a decimal POINT despite my environ containing NLS_LANG=GERMAN_GERMANY.WE8MSWIN1252, which would normally cause a decimal COMMA.

Answer 2

If you are doing a lot of work per session, an option may be to use ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,' at the beginning of your task.

Of course, if lots of other code is executed in the same session, you may get funky results :-) However we are able to use this method in our data load procedures, since we have dedicated programs with their own connection pools for loading the data.