<b> is_number function return Y for numbers and N for characters </b>

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> ed
Wrote file afiedt.buf

  1  select     is_number(empno,1,length(empno)) empno,
  2     is_number(job,1,length(empno)) job ,
  3*    is_number(empno||job,1,length(empno||job)) empnojob from emp
SQL> /

EMPNO      JOB        EMPNOJOB
---------- ---------- ----------
Y          N          N
Y          N          N
Y          N          N
Y          N          N
Y          N          N
Y          N          N
Y          N          N
Y          N          N
Y          N          N
Y          N          N
Y          N          N
Y          N          N
Y          N          N
Y          N          N

14 rows selected.