How to convert from string to number in Oracle using TO_NUMBER function with fixed decimal point char?

I need to convert in procedure from string to decimal with fixed decimal separator . independant on culture settings. Next, I know decimal number is limited just with 6 decimal places after . and there is no limitiation on number of digits before .. Using Oracle documentation and its examples for format strings I have now just this solution:

v_number := TO_NUMBER(v_string, '9999999999999999999999999999999999D999999', 'NLS_NUMERIC_CHARACTERS = ''. ''');

Number of 9 chars before D is maximum number allowed. I find this format string as pretty awful. Is there any better format string for this general conversion or some way to omit second parameter of function? In general I just need to pass to function NLS par开发者_StackOverflow社区ameter to tell it i just want to convert with decimal separator ., but second parameter is mandatory in that case as well.

You can't call the to_number function with the third parameter and not the second. I would suggest putting the "ugly" format string in a package constant and forget about it.

You could also use dbms_session.set_nls to modify your NLS settings and be able to use to_number without arguments.

Handles both comma and period.

FUNCTION to_number2(p_num_str VARCHAR2) RETURN NUMBER AS
BEGIN
  RETURN TO_NUMBER(REPLACE(p_num_str, ',', '.'), '999999999999D999999999999', 'NLS_NUMERIC_CHARACTERS=''.,''');
END;

CREATE OR REPLACE FUNCTION IS_NUMBER(P_VAR IN VARCHAR2)
RETURN NUMBER
IS
  P_NUMBER NUMBER := 0;
  RIG VARCHAR2(10) := '';
  FORMAT VARCHAR2(100) := '999999999999D999999999999';
  RES VARCHAR2(100) := '';
BEGIN
  SELECT VALUE INTO RIG 
  FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';
IF SUBSTR(RIG,1,1) = '.' THEN
 RES := REPLACE(P_VAR,',','.');
ELSE
 RES := REPLACE(P_VAR,'.',',');
END IF;
 P_NUMBER := TO_NUMBER(RES,FORMAT,'NLS_NUMERIC_CHARACTERS='''||RIG||'''');
 P_NUMBER := ROUND(P_NUMBER,5); --FIVE DIGITS AFTER DECIMAL POINT IS ENOUGH
RETURN P_NUMBER;
EXCEPTION
 WHEN OTHERS THEN RETURN -1;
END;

select to_number('   12.5   ') + to_number('   12   ') from dual;