Validate email in PL/SQL

 create or replace function "CHECK_EMAIL"
(l_email IN VARCHAR2)
  RETURN VARCHAR2 IS

  l_dot_pos    NUMBER;
  l_at_pos     NUMBER;
  l_str_length NUMBER;
BEGIN
  l_dot_pos    := instr(l_email
                       ,'.');
  l_at_pos     := instr(l_email
                       ,'@');
  l_str_length := length(l_email);
  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_email
                 ,l_at_pos)
          ,'.') = 0
  THEN
    RETURN 'FAILURE';
  END IF;
  RETURN 'SUCCESS';
end "CHECK_EMAIL";
/

 

Can use in Select query:

SELECT CHECK_EMAIL(your_email@gmail.com) from dual;