SQL> get is_number
  1  Create or Replace
  2  Function IS_number
  3   (vString       in Varchar2,
  4    nStart_pos    in Number,
  5    nStop_Pos     in Number)
  6  Return varchar2 is
  7  nTest_Num     Number := 0;
  8  nbegin_pos    Number := Nvl(nStart_pos, 1);
  9  nEnd_Pos      Number :=  nvl(nStop_Pos, Length(vString));
 10  vSuccess_Flag Varchar2(1);
 11  Begin
 12     Begin
 13        IF vString is Null Then
 14           nTest_Num := 'test';
 15        End IF;
 16        nTest_Num     := To_Number(substr(vString, nBegin_Pos, nEnd_Pos));
 17        vSuccess_Flag := 'Y';
 18      Exception
 19          When Invalid_Number Then
 20               vSuccess_Flag := 'N';
 21          When Others Then
 22               vSuccess_Flag := 'N';
 23     END;
 24     Return vSuccess_Flag;
 25* END;
SQL> /

Function created.

SQL> select is_number('45>4',1,4) from dual;

IS_NUMBER('45>4',1,4)
----------------------------------------------------------------------------------------------------
N

SQL> ed
Wrote file afiedt.buf

  1* select is_number('454',1,4) from dual
SQL> /

IS_NUMBER('454',1,4)
----------------------------------------------------------------------------------------------------
Y

SQL> ed
Wrote file afiedt.buf

  1* select is_number('4a54',1,4) from dual
SQL> /

IS_NUMBER('4A54',1,4)
----------------------------------------------------------------------------------------------------
N
SQL> ed
Wrote file afiedt.buf

  1* select ad,is_number(ad,1,length(ad))  number_or_NOT from adres
SQL> /
AD              NUMBER_OR_
--------------- ----------
1,3rd street    N
134234          Y

CREATE OR REPLACE FUNCTION to_num (p_value IN VARCHAR2)
   RETURN NUMBER
IS
BEGIN
   RETURN TO_NUMBER (p_value);
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END;
/

insert into t values('123');
insert into t values('-123');
insert into t values('123.456');
insert into t values('188.144.66.77');
insert into t values ('-123e-3');
insert into t values ('E3');
insert into t values ('1E3');
insert into t values ('1e3');
insert into t values ('+456')

select  decode(translate(c, ' 0123456789', ' '), null, 'Y', 'N') is_numeric,
        decode(to_num(c), null, 'N', 'Y') to_num,
  c from t;

Y	Y	123
N	N	188.144.66.77
N	Y	-123
N	Y	-123e-3
N	N	E3
N	Y	123.456
N	N	7   7   7
N	N	e3
N	Y	1e3
N	Y	+456