Email Validation using PL/SQL Function

Email
Validation Function using plsql function
CREATE FUNCTION f_email_validate (pi_email_id IN OUT VARCHAR2)
   RETURN BOOLEAN
IS
  
lv_n_check_len     
NUMBER;
  
lv_b_check_in_at   
BOOLEAN;
  
lv_b_check_in_dot  
BOOLEAN;
  
lv_v_extn          
VARCHAR2 (10);
BEGIN
  
lv_n_check_len
:= LENGTH (pi_email_id);
   pi_email_id
:= LOWER (pi_email_id);
   SELECT REPLACE (pi_email_id, ‘ ‘, )
     INTO pi_email_id
     FROM DUAL;
   SELECT REPLACE (pi_email_id, ‘”‘, )
     INTO pi_email_id
     FROM DUAL;
   SELECT REPLACE (pi_email_id, ‘`’, )
     INTO pi_email_id
     FROM DUAL;
   SELECT REPLACE (pi_email_id, CHR (39), )
     INTO pi_email_id
     FROM DUAL;
   SELECT REPLACE (pi_email_id, CHR (92), )
     INTO pi_email_id
     FROM DUAL;
   lv_v_extn := SUBSTR (pi_email_id, INSTR (pi_email_id, ‘.’, 1) + 1);
   IF lv_v_extn NOT IN
         (‘com’, ‘org’, ‘net’, ‘edu’, ‘gov’, ‘us’, ‘biz’, ‘info’, ‘tv’, ‘cc’,
          ‘ws’, ‘ac’, ‘as’, ‘be’, ‘ca’, ‘cc’, ‘de’, ‘dk’, ‘fm’, ‘gs’, ‘il’,
          ‘jp’, ‘kz’, ‘lt’, ‘sk’, ‘in’, ‘ms’, ‘nz’, ‘ph’, ‘ro’, ‘sh’, ‘st’,
          ‘tc’, ‘to’, ‘tv’, ‘uk’, ‘us’, ‘vg’, ‘vu’, ‘ws’, ‘za’)
   THEN
      RETURN (‘Invalid’);
   END IF;
   FOR i IN 1 .. lv_n_check_len
   LOOP
      IF SUBSTR (pi_email_id, i, 1) = ‘@’
      THEN
        
lv_b_check_in_at
:= TRUE;
      ELSIF SUBSTR (pi_email_id, i, 1) = ‘.’
      THEN
        
lv_b_check_in_dot
:= TRUE;
      END IF;
   END LOOP;
   IF lv_b_check_in_at AND lv_b_check_in_dot
   THEN
      RETURN (Valid’);
   ELSE
      RETURN (Invalid’);
   END IF;
END;

  • July 4, 2018 | 15 views