Function to Validate Email Address in Oracle PLSQL

While uploading the data into Oracle system make sure that you upload the valid email address. Below is a small function that will validate the email address while uploading. The function returns 'VALID' valid address and 'INVALID' for Invalid email id's

CREATE OR REPLACE FUNCTION xxx_validate_email(l_email_id IN VARCHAR2)
RETURN VARCHAR2 IS
l_str_length NUMBER;
l_dot_position NUMBER;
l_at_position NUMBER;
BEGIN
l_dot_position := instr(l_email_id
,'.');
l_at_position := instr(l_email_id
,'@');
l_str_length := length(l_email_id);
IF ((l_dot_position = 0) OR (l_at_position = 0) OR (l_dot_position = l_at_position + 1) OR
(l_at_position = 1) OR (l_at_position = l_str_length) OR
(l_dot_position = l_str_length))
THEN
RETURN 'INVALID';
END IF;
IF instr(substr(l_email_id
,l_at_position)
,'.') = 0
THEN
RETURN 'INVALID';
END IF;
RETURN 'VALID';
END xxx_validate_email;
/
Please share your views about this Article.For more articles visit About Oracle Apps

Related Articles to Read