PL/SQL code for Email validation

DECLARE
t_valid NUMBER(1);
t_totallen NUMBER(2);
t_counter NUMBER(2):=0;
t_atpos NUMBER(2):= 1;
i NUMBER(2) := 1;
t_pointpos NUMBER(2):= 1;

mail_ch VARCHAR2(1);

result number; --:GR

BEGIN

t_totallen := LENGTH(:text_item_email);
t_counter := t_totallen;
i := 1;
t_valid := 1;

-------------------------------------------------------------------------------------

IF LENGTH(ltrim(rtrim(:text_item_email))) = 0 THEN
t_valid := 0;
ELSE
---------------------------------------------------------------------------------------
--This is to check special characters are present or not in the email ID
t_counter := t_totallen;

WHILE t_counter > 0
LOOP
mail_ch := substr(:text_item_email,i,1);
i := i+1;
t_counter := t_counter -1;

IF mail_ch IN (' ','!','#','$','%','^','&','*','(',')','-','','"',
'+','|','{','}','[',']',':','>','<','?','/','\','=') THEN
t_valid := 0;
EXIT;
END IF;

END LOOP;

---------------------------------------------------------------------------------------
--This is to check more than one '@' character present or not

t_atpos := instr(:text_item_email,'@',1,2) ;

IF t_atpos > 1 then
t_valid := 0;
END IF;

---------------------------------------------------------------------------------------
--This is to check at minimum and at maximum only one '@' character present

t_atpos := instr(:text_item_email,'@',1) ;

IF t_atpos IN (0,1) THEN
t_valid := 0;
END IF;

---------------------------------------------------------------------------------------
--This is to check at least one '.' character present or not

t_pointpos := instr(:text_item_email,'.',1) ;

IF t_pointpos IN (0,1) THEN
t_valid := 0;
END IF;

---------------------------------------------------------------------------------------

--This is to check at least one character is present between @ and '.' :GR

t_atpos := instr(:text_item_email,'@',1) ;
t_pointpos := instr(:text_item_email,'.',1) ;

IF t_pointpos-t_atpos<=1 THEN
t_valid := 0;
END IF;

---------------------------------------------------------------------------------------

--This is to check at least one character is present after '.' :GR

t_pointpos := instr(:text_item_email,'.',1) ;

IF t_totallen-t_pointpos<=0 THEN
t_valid := 0;
END IF;

---------------------------------------------------------------------------------------

END IF;

if(t_valid=0) then
message('Invalid Email');
result:=show_alert('alert_email');
raise form_trigger_failure;
end if;
END;

---***PL/SQL code for Name Validation***---
DECLARE
t_valid NUMBER(1);
t_totallen NUMBER(2);
t_counter NUMBER(2):=0;
i NUMBER(2) := 1;

name_ch VARCHAR2(1);
--name_ch_ascii NUMBER;

result number; --:GR

BEGIN

t_totallen := LENGTH(:text_item_first_name);
t_counter := t_totallen;
i := 1;
t_valid := 1;

-------------------------------------------------------------------------------------

IF LENGTH(ltrim(rtrim(:text_item_first_name))) = 0 THEN
t_valid := 0;
ELSE
---------------------------------------------------------------------------------------
--This is to check if each character in name lies in the valid ascii range
t_counter := t_totallen;

WHILE t_counter > 0
LOOP
name_ch := upper(substr(:text_item_first_name,i,1));
--name_ch_ascii := convert(name_ch,'US7ASCII');
i := i+1;
t_counter := t_counter -1;

--IF name_ch_ascii not between 65 and 90 THEN
IF name_ch not between 'A' and 'Z' THEN
t_valid := 0;
EXIT;
END IF;

END LOOP;
---------------------------------------------------------------------------------------
END IF;

if(t_valid=0) then
message('Invalid First Name');
result:=show_alert('alert_first_name');
raise form_trigger_failure;
end if;
END;