1. Overview
This blog explains about, how we can Email validation using PLSQL function.
2. Technologies and Tools Used
The following technology has been used to achieve the same.
Ø Oracle PLSQL
3. Use Case
To Create one function in database 18C
4. Architecture
Oracle Database
- Using Database 18C
- ORALCE PL/SQL
5. Examples
Step 1: Create one function in Database.
Create one function in database.
Code:
CREATE FUNCTION f_email_validate (pi_email_id IN OUT VARCHAR2)
RETURN BOOLEAN
IS
lv_n_check_len NUMBER;
lv_b_check_in_at BOOLEAN;
lv_b_check_in_dot BOOLEAN;
lv_v_extn VARCHAR2 (10);
BEGIN
lv_n_check_len := LENGTH (pi_email_id);
pi_email_id := LOWER (pi_email_id);
SELECT REPLACE (pi_email_id, ‘ ‘, ”)
INTO pi_email_id
FROM DUAL;
SELECT REPLACE (pi_email_id, ‘”‘, ”)
INTO pi_email_id
FROM DUAL;
SELECT REPLACE (pi_email_id, ‘`’, ”)
INTO pi_email_id
FROM DUAL;
SELECT REPLACE (pi_email_id, CHR (39), ”)
INTO pi_email_id
FROM DUAL;
SELECT REPLACE (pi_email_id, CHR (92), ”)
INTO pi_email_id
FROM DUAL;
lv_v_extn := SUBSTR (pi_email_id, INSTR (pi_email_id, ‘.’, -1) + 1);
IF lv_v_extn NOT IN
(‘com’, ‘org’, ‘net’, ‘edu’, ‘gov’, ‘us’, ‘biz’, ‘info’, ‘tv’, ‘cc’,
‘ws’, ‘ac’, ‘as’, ‘be’, ‘ca’, ‘cc’, ‘de’, ‘dk’, ‘fm’, ‘gs’, ‘il’,
‘jp’, ‘kz’, ‘lt’, ‘sk’, ‘in’, ‘ms’, ‘nz’, ‘ph’, ‘ro’, ‘sh’, ‘st’,
‘tc’, ‘to’, ‘tv’, ‘uk’, ‘us’, ‘vg’, ‘vu’, ‘ws’, ‘za’)
THEN
RETURN (‘Invalid’);
END IF;
FOR i IN 1 .. lv_n_check_len
LOOP
IF SUBSTR (pi_email_id, i, 1) = ‘@’
THEN
lv_b_check_in_at := TRUE;
ELSIF SUBSTR (pi_email_id, i, 1) = ‘.’
THEN
lv_b_check_in_dot := TRUE;
END IF;
END LOOP;
IF lv_b_check_in_at AND lv_b_check_in_dot
THEN
RETURN (Valid’);
ELSE
RETURN (Invalid’);
END IF;
END;
Step 2: Run the function in anonymous block.
In this function will validate the input value (email id) and will get the result as entered email id is Valid or Invalid :
Code:
6. Conclusion
We can validate the entered email id is Valid or Invalid.
