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;