Until Oracle comes up with an in-built functionality to validate an email address, you can use the following custom stored function. Compile it in the database and pass an email address to see if the email address is VALID, INVALID or UNKNOWN.
For example, if you pass following email addresses, the returning result will be as follow:
test_mail@gmail.com - VALID
test_mail@gmailcom - INVALID
@gmail.com - INVALID
test.@.com - INVALID
@gmail. - INVALID
test_mail@gmail.com - INVALID
NULL - UNKNOWN
CREATE OR REPLACE FUNCTION xxcc_validate_email(p_email IN VARCHAR2)
RETURN VARCHAR2
--===============================================================================
-- Filename : XXCC_VALIDATE_EMAIL_FNC.sql
---------------------------------------------------------------------------------
-- Programmer : test_mail
-- Date : 24-Jul-2013
-- Language : PL/SQL
-- Location : $XXCC_TOP/install/
-- Purpose : This function validates an email address using PL/SQL
---------------------------------------------------------------------------------
-- Description :
-- Takes a string (p_email) as an input parameter. Through a series of
-- validation checks, this function determines whether the email address is
-- valid or invalid.
--
---------------------------------------------------------------------------------
-- Rules:
-- The following rules are based on the article "Characters in the local part
-- of a mail address" found in:
-- <http://www.ramindia03.blogspot.com>
--
-- 1) email cannot be NULL (length of NULL value is undetermined)
-- 2) email has to be minimum 5 characters long
-- 3) email requires at least one '@' and one '.' sign
-- 4) email cannot start or end with '@' sign
-- 5) email cannot end with '.' sign
-- 6) '.' sign cannot be before or after '@' sign
-- 7) double dots '..' are not permitted in an email
-- 8) invalid characters are: ` ~ ! # $ % ^ & * ( ) " | { } [ ] : ; , < > ? \ /
-- and single quote and space characters
-- 9) domain part must consists of strings
-- 10) email cannot contain two '@' sign
--
-- Note:
-- The returned value from this function is usually used against an email
-- address, which will be (or is supposed to be) used with UNIX's "mail" command.
-- Therefore, few more restrictions are applied to this functions.
--
----------------------------------------------------------------------------------------------------
-- Update History:
-- Date Updated By Description
-- --------- --------------------------- ---------------------------------------------------------
-- 24-Jul-13 Authour R Added test for double 'at' symbol in email.
-- Modified invalid character test to be in a single string.
-- 12-Aug-13 Authour R First version.
--==================================================================================================
IS
v_at_pos PLS_INTEGER; -- position variable for at (@) sign
v_dot_pos PLS_INTEGER; -- position variable for dot (.) sign
v_length PLS_INTEGER; -- holds the length of email
v_email_ch VARCHAR2(1); -- check character in an email
v_result VARCHAR2(10) DEFAULT 'VALID'; -- result variable
BEGIN
----------------------------------------------------------
-- variable assignments
----------------------------------------------------------
v_at_pos := INSTR(p_email, '@', -1); -- position for last occurrence of '@' sign
v_dot_pos := INSTR(p_email, '.', -1); -- position for last occurrence of '.' sign
v_length := LENGTH(p_email); -- length of entire email address
----------------------------------------------------------
-- First Round Check:
-- Rules for invalid email address
----------------------------------------------------------
IF p_email IS NULL OR -- email cannot be null
v_length < 5 OR -- email length should be at least 5 characters
v_at_pos = 0 OR -- email requires at least one '@' sign
v_dot_pos = 0 OR -- email requires at least one '.' sign
v_at_pos = 1 OR -- email cannot start with '@' sign
v_dot_pos = v_at_pos - 1 OR -- dot (.) sign cannot be right before at (@) sign
v_dot_pos = v_at_pos + 1 OR -- dot (.) sign cannot be right after at (@) sign
v_dot_pos = v_length OR -- email cannot end with dot '.' sign
v_at_pos = v_length OR -- email cannot end with at '@' sign
/* double dots are not permitted */
INSTR(SUBSTR(p_email, 1, (v_at_pos - 1)), '..') > 0 OR
INSTR(SUBSTR(p_email, v_at_pos + 1), '..') > 0 OR
/* requires one '.' sign after '@' sign */
INSTR(SUBSTR(p_email, v_at_pos), '.') = 0 OR
(INSTR(p_email, '@') > 0 AND INSTR(p_email, '@', INSTR(p_email, '@') + 1) > 0) OR
/* this following code finds out if the domain part is a number or a string */
UPPER(SUBSTR(p_email, v_at_pos, v_length)) = LOWER(SUBSTR(p_email, v_at_pos, v_length))
THEN
v_result := 'INVALID';
END IF;
----------------------------------------------------------
-- Second Round Check:
-- Invalid characters should not be in the email address.
-- Validate using INSTR method
----------------------------------------------------------
FOR i IN 1..v_length
LOOP
v_email_ch := SUBSTR(p_email, i, 1);
IF INSTR(' `~!#$%^&*"|(){}[]:;,<>?\/''''', v_email_ch) > 0
THEN
v_result := 'INVALID';
EXIT;
END IF;
END LOOP;
RETURN (v_result);
EXCEPTION
WHEN OTHERS THEN
RETURN 'UNKNOWN';
END xxcc_validate_email;
