Validate Email Adress in PL/SQL

Oracle delivers an API in EBS that can validate Email Address Format.
However it does not capture the invalidity if Email String were to be say xxx@host
You may use the below pl/sql function for Email Address Validation.


CREATE OR REPLACE FUNCTION xx_check_email(l_user_name IN VARCHAR2)
  RETURN VARCHAR2 IS
  l_dot_pos    NUMBER;
  l_at_pos     NUMBER;
  l_str_length NUMBER;
BEGIN
  l_dot_pos    := instr(l_user_name
                       ,'.');
  l_at_pos     := instr(l_user_name
                       ,'@');
  l_str_length := length(l_user_name);
  IF ((l_dot_pos = 0) OR (l_at_pos = 0) OR (l_dot_pos = l_at_pos + 1) OR
     (l_at_pos = 1) OR (l_at_pos = l_str_length) OR
     (l_dot_pos = l_str_length))
  THEN
    RETURN 'FAILURE';
  END IF;
  IF instr(substr(l_user_name
                 ,l_at_pos)
          ,'.') = 0
  THEN
    RETURN 'FAILURE';
  END IF;
  RETURN 'SUCCESS';
END xx_check_email;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22816976/viewspace-1328652/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22816976/viewspace-1328652/