Email Validation

CREATE OR REPLACE FUNCTION 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 check_email;



=================================================================================




declare
  v_at  number;
  v_dot number;
begin
  v_at  := instr("field_name", '@');
  v_dot := instr("field_name", '.');
  if v_at = 0 or v_dot = 0 or v_at > v_dot then
 
    show_message('Invalid Email ID Format..,', 'E', true);
 
  end if;
end;