info.michael-simons.eu

CREATE OR REPLACE FUNCTION f_makeNumber(inval IN VARCHAR2) RETURN NUMBER IS
  rv NUMBER;
  numCharSet VARCHAR2(32);
BEGIN
  SELECT VALUE
  INTO   numCharSet
  FROM   nls_session_parameters
  WHERE  parameter = 'NLS_NUMERIC_CHARACTERS';
 
  BEGIN
    SELECT DECODE(
             NVL(LENGTH(TRANSLATE(TRIM(inval),' +-.,0123456789',' ')),0),
              0,TO_NUMBER(
                CASE WHEN VALUE = '.,' THEN
                  REPLACE(inval, ',','.')
                     WHEN VALUE = ',.' THEN
                  REPLACE(inval, '.',',')
                END              
              ),
              NULL
           )
    INTO   rv
    FROM   nls_session_parameters
    WHERE  parameter = 'NLS_NUMERIC_CHARACTERS';
  EXCEPTION WHEN invalid_number THEN
    rv := NULL;
  END;
 
  RETURN rv;
 
END f_makeNumber;
/