It all sounds Geek to me! [Gautam Arora]

Saturday, October 15, 2005

Form validations for PL/SQL

--***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;

Note: I came across some code for Email validation in PL/SQL (during my Oracle Forms and Reports training) after some googling.Added some more test-cases to it and now its stronger than before.Also used it as a base for name validation.
The code aint pretty but its what you need when u got to submit a mini-project quickly.
Hail Google!

1 comment:

Infactahost.com said...
This comment has been removed by a blog administrator.