EMAIL FORMAT 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;


select INSTR (:l_user_name, '.'),
INSTR (:l_user_name, '@'),
LENGTH (:l_user_name)
from dual;


begin
if owa_pattern.match(p_email,'^\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}'||
'@\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}$') then
dbms_output.put_line('email válido : '||p_email);
return true;
else
dbms_output.put_line('email inválido : '||p_email);
return false;
end if;
end f_valida_email;