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;
/

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; /