Home > Uncategorized > Validate Email Addresses with this PL/SQL Function

Validate Email Addresses with this PL/SQL Function

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 😉

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: