title: How to efficiently convert text to number in Oracle PL/SQL with non-default NLS_NUMERIC_CHARACTERS?
body: <p>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.</p>
  
  <pre><code>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...
  </code></pre>
  
  <p><code>to_number</code> accepts a 3rd parameter but in that case you to specify a second parameter too, and there is no format spec for "default"...</p>
  
  <pre><code>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...
  </code></pre>
  
  <p>There is another way using PL/SQL:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>which does <em>exactly</em> 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.</p>
  
  <p>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?</p>