SQL> create or replace function isdigit(v varchar2) return varchar2 is
  2  v1 varchar2(10);
  3  begin
  4    select translate(trim(v),'1234567890',' ') into v1 from dual;
  5    IF  v1 =' ' THEN
  6        RETURN v||' is NUMBER';
  7     ELSE
  8         RETURN v||' is not NUMBER';
  9     END IF;
 10   END;
 11  /

Function created.

SQL> select isdigit('10') NumOrNot from dual;

NUMORNOT
--------------------------------------------------------------------------------
10 is NUMBER

SQL>  select isdigit('XY10')  NumOrNot from dual;

NUMORNOT
--------------------------------------------------------------------------------
XY10 is not NUMBER

SQL> 

SQL> CREATE OR REPLACE FUNCTION is_digit (
  2      p_candidate     IN  VARCHAR2
  3  )
  4  RETURN BOOLEAN
  5  IS
  6      l_dummy_number      NUMBER;
  7      l_is_digit          BOOLEAN;
  8  BEGIN
  9      BEGIN
 10          l_dummy_number := TO_NUMBER(p_candidate);
 11          l_is_digit := TRUE;
 12      EXCEPTION
 13          WHEN OTHERS THEN
 14              l_is_digit := FALSE;
 15      END;
 16      RETURN (l_is_digit);
 17  END is_digit;
 18  /
 
Function created.
 
SQL> CREATE OR REPLACE FUNCTION bool_to_string (
  2      p_boolean   IN BOOLEAN
  3  )
  4  RETURN VARCHAR2
  5  IS
  6      l_bool_as_string    VARCHAR2(7);
  7  BEGIN
  8      IF (p_boolean IS NULL) THEN
  9          l_bool_as_string := 'NULL';
 10      ELSIF (p_boolean) THEN
 11          l_bool_as_string := 'TRUE';
 12      ELSIF (NOT p_boolean) THEN
 13          l_bool_as_string := 'FALSE';
 14      ELSE
 15          l_bool_as_string := 'UNKNOWN';
 16      END IF;
 17      RETURN (l_bool_as_string);
 18  END bool_to_string;
 19  /
 
Function created.
 
SQL> SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED
SQL> BEGIN
  2      DBMS_OUTPUT.PUT_LINE('is_digit(4)      = ' || bool_to_string(is_digit('4')));
  3      DBMS_OUTPUT.PUT_LINE('is_digit(3.5)    = ' || bool_to_string(is_digit('3.5')));
  4      DBMS_OUTPUT.PUT_LINE('is_digit(2E5)    = ' || bool_to_string(is_digit('2E5')));
  5      DBMS_OUTPUT.PUT_LINE('is_digit(E5)     = ' || bool_to_string(is_digit('E5')));
  6      DBMS_OUTPUT.PUT_LINE('is_digit(-1)     = ' || bool_to_string(is_digit('-1')));
  7      DBMS_OUTPUT.PUT_LINE('is_digit(0.0)    = ' || bool_to_string(is_digit('0.0')));
  8      DBMS_OUTPUT.PUT_LINE('is_digit(7.7e-6) = ' || bool_to_string(is_digit('7.7E-6')));
  9      DBMS_OUTPUT.PUT_LINE('is_digit(+59)    = ' || bool_to_string(is_digit('+59')));
 10      DBMS_OUTPUT.PUT_LINE('is_digit(1+1)    = ' || bool_to_string(is_digit('1+1')));
 11      DBMS_OUTPUT.PUT_LINE('is_digit(2/3)    = ' || bool_to_string(is_digit('2/3')));
 12  END;
 13  /
is_digit(4)      = TRUE
is_digit(3.5)    = TRUE
is_digit(2E5)    = TRUE
is_digit(E5)     = FALSE
is_digit(-1)     = TRUE
is_digit(0.0)    = TRUE
is_digit(7.7e-6) = TRUE
is_digit(+59)    = TRUE
is_digit(1+1)    = FALSE
is_digit(2/3)    = FALSE
 
PL/SQL procedure successfully completed.
 
SQL>

SQL> ed
Wrote file afiedt.buf

  1  create or replace function isdigit(v varchar2) return varchar2 is
  2  n number;
  3  BEGIN
  4  select length(translate(UPPER(v),'1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ','0000000000')) into n from dual;
  5   IF  length(v)=n  THEN
  6       RETURN v||' is NUMBER';
  7   ELSE
  8       RETURN v||' is not NUMBER';
  9   END IF;
 10* END;
SQL> /

Function created.

SQL>