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

Question

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 parameter to tell it i just want to convert with decimal separator ., but second parameter is mandatory in that case as well.

Answer 1

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.

Answer 2

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;