Example :

CREATE FUNCTION is_number (p_string IN VARCHAR2)
   RETURN INT
IS
   v_new_num NUMBER;
BEGIN
   v_new_num := TO_NUMBER(p_string);
   RETURN 1;
EXCEPTION
WHEN VALUE_ERROR THEN
   RETURN 0;
END is_number;
This new function called is_number would return 1 if the value is numeric and 0 if the value is NOT numeric. You could execute the is_number function as follows:

SELECT is_number('123') FROM dual;
Result: 1

SELECT is_number('123b') FROM dual;
Result: 0

create or replace procedure Main
AS
cursor data 
IS
select COMPANY_ID,INTERNAL_CONTACT, ZIP_CODE, ZIP_EXTENSION from temp_tmbl;

v_zipcode NUMBER;

BEGIN
for i in data loop

select is_number(i.ZIP_CODE) into v_zipcode from dual;

IF v_zipcode=0 then
DBMS_OUTPUT.PUT_LINE ('Datas is not numeric'||i.company_id);
END IF;

end loop;

cursor data 
  IS
  select CHAIN_ID,INTERNAL_CONTACT, ZIP_CODE, ZIP_EXTENSION from keerthi_stg;
  v_zipcode NUMBER;
  BEGIN
  for i in data loop

  select is_number('i.ZIP_CODE') into v_zipcode from dual;
  
  IF v_zipcode=1 THEN 
  DBMS_OUTPUT.PUT_LINE ('Datas are numeric'|| v_zipcode);
  else
  DBMS_OUTPUT.PUT_LINE ('Datas are not numeric'|| v_zipcode);
  END IF;
  

 
 CREATE FUNCTION is_number (p_string IN VARCHAR2)
   RETURN INT
IS
   v_new_num NUMBER;
BEGIN
   v_new_num := TO_NUMBER(p_string);
   RETURN 1;
EXCEPTION
WHEN VALUE_ERROR THEN
   RETURN 0;
END is_number;
This new function called is_number would return 1 if the value is numeric and 0 if the value is NOT numeric. You could execute the is_number function as follows:

select is_number('i.ZIP_CODE')

select is_number(i.ZIP_CODE)

select is_number('i.ZIP_CODE')

select is_number(i.ZIP_CODE)

select i.ZIP_CODE, is_number(i.ZIP_CODE)

CREATE FUNCTION is_number (p_string IN VARCHAR2)
   RETURN INT
IS
   v_new_num NUMBER;
BEGIN
   v_new_num := TO_NUMBER(NVL(p_string,'A'));
   RETURN 1;
EXCEPTION
   ...

 IF translate(i.zip_code, ' 1234567890',' ')   IS NOT NULL then
--- is not numeric zip
else
-- is numeric zip
end if