Tag Archives: Check Valid email in pl/sql

Function to Check Validity of Email (Regular Expression)

ORACLE APPS, , ,

The function is to check email so that it does not contain any junk character.It returns 0 for false and 1 for true.

CREATE OR REPLACE FUNCTION EMAIL_VALID (p_email IN VARCHAR2)
RETURN NUMBER
IS
cemailregexp CONSTANT VARCHAR2 (1000)
:= ‘^[a-z0-9!#$%&”*+/=?^_`{|}~-]+(\.[a-z0-9!#$%&”*+/=?^_`{|}~-]+)*@([a-z0-9]([a-z0-9-]*[a-z0-9])?\.)+([A-Z]{2}|arpa|biz|com|info|intww|name|net|org|pro|aero|asia|cat|coop|edu|gov|jobs|mil|mobi|museum|pro|tel|travel|post)$’ ;
BEGIN
IF REGEXP_LIKE (p_email, cemailregexp, ‘i’)
THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;