Determining if the Field Data in Oracle is of Number type

Problem

You want to query those only from the varchar2 or char field containing number data with no any other character.

Solution

Create a function to determine this:

Create or Replace Function is_number (iNumber in Varchar2)
    Return Varchar2
is
nVal Number; 
Begin
   nVal := To_Number(iNumber);
  -- if there is no error 
   Return('TRUE');
Exception
  when others then
   -- error during conversion 
   Return('FALSE');
end;

Use in query:

Select Empno, Ename, dummychar
   from emp
where is_number(dummychar) = 'TRUE';