Script to validate Email Address in Oracle

For example, if you pass following email addresses, the returning result will be as follow:

   chandra.kadali@gmail.com      -  VALID
   chandra.kadali@gmailcom       -  INVALID
   @gmail.com                 -  INVALID
   chandra@.com                  -  INVALID
   chandra.kadali@gmail.         -  INVALID
   chandra. kadali@gmail.com     -  INVALID
   NULL                       -  UNKNOWN


CREATE OR REPLACE FUNCTION xxcc_validate_email(p_email  IN  VARCHAR2)
   RETURN VARCHAR2
 
   --===============================================================================
   -- Filename    :  XXCC_VALIDATE_EMAIL_FNC.sql
   ---------------------------------------------------------------------------------
   -- Language    :  PL/SQL
   -- Location    :  $XXCC_TOP/install/
   -- Purpose     :  This function validates an email address using PL/SQL
   ---------------------------------------------------------------------------------
   -- Description :
   --    Takes a string (p_email) as an input parameter. Through a series of
   --    validation checks, this function determines whether the email address is
   --    valid or invalid.
   --
   ---------------------------------------------------------------------------------
   -- Rules:
   --    The following rules are based on the article "Characters in the local part
   --    of a mail address" by Jochen Topf, found in:
   --   
   --
   --      1) email cannot be NULL (length of NULL value is undetermined)
   --      2) email has to be minimum 5 characters long
   --      3) email requires at least one '@' and one '.' sign
   --      4) email cannot start or end with '@' sign
   --      5) email cannot end with '.' sign
   --      6) '.' sign cannot be before or after '@' sign
   --      7) double dots '..' are not permitted in an email
   --      8) invalid characters are: ` ~ ! # $ % ^ & * ( ) " | { } [ ] : ; , < > ? \ /
   --         and single quote and space characters
   --      9) domain part must consists of strings
   --     10) email cannot contain two '@' sign
   --
   -- Note:
   --    The returned value from this function is usually used against an email
   --    address, which will be (or is supposed to be) used with UNIX's "mail" command.
   --    Therefore, few more restrictions are applied to this functions.
   --
   ---------------------------------------------------------------------------------
   -- Update History:
   -- Date       Updated By   Description
   -- ---------  -----------  ------------------------------------------------------
  --===============================================================================

IS
   v_at_pos         PLS_INTEGER;                -- position variable for at (@) sign
   v_dot_pos        PLS_INTEGER;                -- position variable for dot (.) sign
   v_length         PLS_INTEGER;                -- holds the length of email

   v_email_ch       VARCHAR2(1);                -- check character in an email
   v_result         VARCHAR2(10)  DEFAULT  'VALID';   -- result variable

BEGIN
   ----------------------------------------------------------
   -- variable assignments
   ----------------------------------------------------------
   v_at_pos     :=  INSTR(p_email, '@', -1);  -- position for last occurance of '@' sign
   v_dot_pos    :=  INSTR(p_email, '.', -1);  -- position for last occurance of '.' sign
   v_length     :=  LENGTH(p_email);          -- length of entire email address

   ----------------------------------------------------------
   -- First Round Check:
   --   Rules for invalid email address
   ----------------------------------------------------------
   IF p_email IS NULL            OR   -- email cannot be null
      v_length  < 5              OR   -- email length should be at least 5 characters
      v_at_pos  = 0              OR   -- email requires at least one '@' sign
      v_dot_pos = 0              OR   -- email requires at least one '.' sign
      v_at_pos  = 1              OR   -- email cannot start with '@' sign
      v_dot_pos = v_at_pos - 1   OR   -- dot (.) sign cannot be right before at (@) sign
      v_dot_pos = v_at_pos + 1   OR   -- dot (.) sign cannot be right after at (@) sign
      v_dot_pos = v_length       OR   -- email cannot end with dot '.' sign
      v_at_pos  = v_length       OR   -- email cannot end with at '@' sign
    
      /* double dots are not permitted */
      INSTR(SUBSTR(p_email, 1, (v_at_pos - 1)), '..') > 0 OR
      INSTR(SUBSTR(p_email, v_at_pos + 1), '..')      > 0 OR
    
      /* requires one '.' sign after '@' sign */
      INSTR(SUBSTR(p_email, v_at_pos), '.') = 0           OR
      (INSTR(p_email, '@') > 0 AND INSTR(p_email, '@', INSTR(p_email, '@') + 1) > 0) OR
    
      /* this following code finds out if the domain part is a number or a string */
      UPPER(SUBSTR(p_email, v_at_pos, v_length)) = LOWER(SUBSTR(p_email, v_at_pos, v_length))
   THEN
      v_result := 'INVALID';
   END IF;

   ----------------------------------------------------------
   -- Second Round Check:
   --   Invalid characters should not be in the email address.
   --   Validate using INSTR method
   ----------------------------------------------------------
   FOR i IN 1..v_length
   LOOP
      v_email_ch  :=  SUBSTR(p_email, i, 1);
    
      IF INSTR(' `~!#$%^&*"|(){}[]:;,<>?\/''''', v_email_ch) > 0
      THEN
         v_result := 'INVALID';
         EXIT;
      END IF;
    
   END LOOP;
 
   RETURN (v_result);

EXCEPTION
   WHEN OTHERS THEN
      RETURN 'UNKNOWN';
 
END xxcc_validate_email;