Archive

Posts Tagged ‘personalization’

Validate Email Addresses with this PL/SQL Function

June 1, 2011 Leave a comment

After becoming frustrated by users entering invalid email addresses into the system, I found this  brilliant piece of code using the regexp_like function. I implemented it into our Oracle EBS customer master right away using Forms Personalization. I’m not going to pretend to completely understand the regexp_like SQL function, but here is how I used it.
Create this function stand-alone or part of your favorite stored package. (I put it inside a package that retrieves email addresses in flexible ways.)

-- function will return 1 upon valid email, 0 on invalid
 function valid(p_email in varchar2)
 return number
 is
 cemailregexp constant varchar2(1000) := '^[a-z0-9!#$%&''*+/=?^_`{|}~-]+(\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*@([a-z0-9]([a-z0-9-]*[a-z0-9])?\.)+([A-Z]{2}|arpa|biz|com|info|intww|name|net|org|pro|aero|asia|cat|coop|edu|gov|jobs|mil|mobi|museum|pro|tel|travel|post)$';

begin
 if regexp_like(p_email,cemailregexp,'i') then
 return 1;
 else
 return 0;
 end if;

exception
 when others then return 0;
 end;

Then, in the Customer Standard form, I created this personlization at both the address and the customer account level where email address can be entered. If you need, you could put this in the contacts as well as any other EBS form that supports personalizations.

Works like a charm! Now onto using regex to clean up my Item Master descriptions 😉