-- To validate data as numeric, a custom PL/SQL is alternate option 
-- to REGEXP_LIKE and TRANSLATE

-- The function below returns 1 or -1

CREATE OR REPLACE FUNCTION isnumeric(i_val IN VARCHAR2)
 RETURN NUMBER
IS
  o_val   NUMBER;
BEGIN
  o_val := TO_NUMBER(i_val); 
  RETURN 1;

EXCEPTION 
 WHEN OTHERS THEN
  RETURN -1;
END isnumeric;
/

-- Check the data validation using above function

SELECT ref_value||
  CASE isnumeric(ref_value)
      WHEN -1 THEN ' Is Non-Numeric'
      ELSE ' Is Numeric'
  END
FROM (SELECT 'a10'  ref_value FROM DUAL  UNION
      SELECT '100'  ref_value FROM DUAL
     );

-- The function below returns boolean

CREATE OR REPLACE FUNCTION isnumeric(i_val IN VARCHAR2) 
 RETURN BOOLEAN
IS
  o_val   NUMBER;
  
BEGIN
  o_val := TO_NUMBER(i_val); 
  RETURN TRUE;

EXCEPTION 
 WHEN OTHERS THEN
  RETURN FALSE;
END isnumeric;
/

-- Check the data validation using above function


SET SERVEROUTPUT ON;
DECLARE
 v_val    VARCHAR2(5) := 'A100';

BEGIN
 DBMS_OUTPUT.PUT(v_val);

 IF isnumeric(v_val) THEN
   DBMS_OUTPUT.PUT_LINE(' Is Numeric');
 ELSE
   DBMS_OUTPUT.PUT_LINE(' Is Non-Numeric');
 END IF;

 v_val := 1000;

 DBMS_OUTPUT.PUT(v_val);

 IF isnumeric(v_val) THEN
   DBMS_OUTPUT.PUT_LINE(' Is Numeric');
 ELSE
   DBMS_OUTPUT.PUT_LINE(' Is Non-Numeric');
 END IF;
END;
/

-- Function using WITH syntax in Oracle 12c

WITH
FUNCTION isnumeric(i_num IN VARCHAR2)
 RETURN NUMBER
 IS
  o_val   NUMBER;
BEGIN
  o_val := TO_NUMBER(i_num); 
  RETURN 1;

EXCEPTION 
 WHEN OTHERS THEN
  RETURN -1;
END isnumeric;
SELECT ref_value||
  CASE isnumeric(ref_value)
      WHEN -1 THEN ' Is Non-Numeric'
      ELSE ' Is Numeric'
  END
FROM (SELECT 'a11'  ref_value FROM DUAL  UNION
      SELECT '100'  ref_value FROM DUAL
     );