Oracle PLSQL Function For to Validate Mail Address
Blog Description:
In this new blog, we will know about how to write a function in oracle plsql for to validate the mail address of customer whenever we are accepting the inputs.
Function for to Validate Mail ID:
create or replace FUNCTION validateemailid (
v_email_id IN VARCHAR2
)
RETURN VARCHAR2
IS
v_str_length NUMBER;
v_dot_position NUMBER;
v_at_position NUMBER;
BEGIN
v_dot_position := INSTR (v_email_id, ‘.’);
v_at_position := INSTR (v_email_id, ‘@’);
v_str_length := LENGTH (v_email_id);
IF ((v_dot_position = 0) OR (v_at_position = 0)
OR (v_dot_position = v_at_position + 1) OR (v_at_position = 1)
OR (v_at_position = v_str_length)
OR (v_dot_position = v_str_length)
OR INSTR (v_email_id, ‘@’, 1, 2) <> 0
) THEN
RETURN ‘Entered mail id is invalid, please pass correct’;
END IF;
IF INSTR (SUBSTR (v_email_id, v_at_position), ‘.’) = 0 THEN
RETURN ‘Entered mail id is invalid, please pass correct mail id.’;
END IF;
RETURN ‘Entered mail id is valid…!!!’;
END validateemailid;
With the help of above function we will validate the mail id of customer and validation message will be triggered out on the bases of inputs.
Demo:
In this example, we will call function with the sample inputs for to check whether the function is working correctly or not?
Here I am writing an anonymous block for to call our created function for to pass the input,
DECLARE
v_mailid VARCHAR2 (100) := ‘jain.pankaj@gmail.com’;
- –Tested for to check invalid mail id ‘jain.pankaj#gmail.com’;
v_msg varchar(100);
BEGIN
SELECT validateemailid (v_mailid)
into v_msg
FROM DUAL;
dbms_output.put_line (v_msg);
END;