Helpful Information
 
 
Category: Oracle Development
email validation in pl/sql

i know that pl/sql's strong point is not regular expressions, and i'm well aware of better solutions to do this.

given my requirements per employer, email validation will need to be done in the pl/sql layer. i'd like to implement RFC822/RFC1035 for email validation and was wondering if anyone had some code that does this?

what is pl/sql's? If by 'pl' you mean PERL, then you are mistaken - regular expressions and pattern matching are exactly what the language is meant for.

As for a PCRE to match am email address, you'd better search the forums or do a google. This is perhaps the most common implementation of regular expressions in the history of mankind.

christo

not perl. pl/sql.

i posted this in the oracle forum because pl/sql is specific to oracle.

my bad - over to the 'pl/sql' experts

Don't know of any reg expression code available in pl/sql. How about if you wrote a java function that could be called by the pl/sql?

If you happen to be running Oracle Web Apps, you could use owa_pattern, for regex matching. Or use javascript within the pl/sql block. I haven't done this myself, though the javascript option is in heavy use at my office.

You can find the documentation on owa_pattern at
Oracle's doc site (http://download-west.oracle.com/docs/cd/A97329_01/web.902/a90101/pspatt.htm#1005550)

Hope this helps a bit,
Marty

I don't know how strong your validation needs to be, but you can probably easily use substr() and a For loop to check that there are:
-some numbers/letters/periods
-followed by one @ symbol
-followed by some more numbers/letters
-followed by a period and a valid extension, such as .net, .org, .com, .co.uk, etc.

Basically figure out what your validation rules are, and then use substr to break the varchar2 into pieces, then check the pieces.


And for those who don't know, here's some background info courtesy of Orafaq.com:
What is PL/SQL and what is it used for?
PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL's language syntax, structure and data types are similar to that of ADA. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.
(http://www.orafaq.com/faqplsql.htm)

Hi,

I developped the owa_pattern.match validation string. here it is:

->> ^.+@{1}\w+\.{1}\w{2,3}$ <<-

It matches any email address correctly.

Use it this way:


if owa_pattern.match('email.address@server.com', '^.+@{1}\w+\.{1}\w{2,3}$') then
/*matches*/
else
/*not matches*/
end if;

Procedure to validate the structure of a e-mail address:



procedure pr_validar_dir_correo(p_Correo_electronico varchar2,
p_oErrCode out number,
p_oErrDesc out varchar2)
is
/*=================================================================================================== =====
-- objetivo: validar que una dirección de correo-e esté bien contruida
--
-- Referencias:
-- http://email.about.com/cs/standards/a/email_addresses.htm
-- http://www.remote.org/jochen/mail/info/chars.html
-- http://es.wikipedia.org/wiki/Dominio_de_nivel_superior
--
-- The user name consists of words, separated by dots [`.'].
-- A word is an "atom" or a quoted string.
-- An "atom" is a sequence of ASCII characters (from 33 to 126;
-- excluding braces (`(', `)', `[', `]', `<', `>'),
-- punctuation marks (`.', `,', `;', `:'),
-- two other characters (`\', `"'), spaces (` ')
-- and the mighty (`@').
-- A quoted string begins and ends with a quotation character (`"').
-- In between the quotes, you can put any ASCII character (now from 0 to 177)
-- excluding the quote itself (`"') and the carriage return (`\r').
-- You can quote the quote with a backslash (`\') to include it.
-- The backslash will quote any character.
-- The backslash causes the following character to lose the special meaning it usually would have in the context.
-- For example `\"' does not end the quoted string but appears as a quote in it.
-- I think it's best if we forget all this (quoted or not) quickly.
--
-- Characters You Should Use in Your Email Address
-- What the standard boils down to is using any ASCII alphanumeric character
-- plus some fancy, but otherwise "normal" characters (`!', `#', `$', `% `&', `*' `+', `-' `~',
-- and whatever you can find in between ASCII 33 and 47).
--
-- 2008-02-11 A.Alonso Creación
==================================================================================================== ====*/
k_ARROBA constant varchar2(1):=chr(64);
k_PUNTO constant varchar2(1):=chr(46);

v_Contar_arrobas integer:=0;
v_Ult_caracter varchar2(1);
v_Caracter varchar2(1);
v_Ascii integer;
v_Correo_ajustado varchar2(200);
v_Usuario varchar2(200);
v_Dominio varchar2(200);
v_Dominio_superior varchar2(100);
v_Pos_punto_izquierdo integer;
Begin
p_oErrCode:=0;
p_oErrDesc:=null;
--
v_Correo_ajustado:=trim(p_Correo_electronico);
--
if v_Correo_ajustado is null then
p_oErrCode:=6700;
p_oErrDesc:='La dirección de correo electrónico está vacía';
return;
end if;
v_Contar_arrobas:=0;
v_Ult_caracter:=null;
for i in 1..length(v_Correo_ajustado) loop
v_Caracter:=substr(v_Correo_ajustado,i,1);
v_Ascii:=ascii(v_Caracter);
--print('car['||lpad(i,3)||']="'||v_Caracter||'", ='||lpad(v_Ascii,3)||', Ult="'||v_Ult_caracter||chr(34));
-- debe ser un caracter en el rango del ASCII visible
-- validar que esté entre el conjunto de los caracteres permitidos
if v_Ascii<=31 then
p_oErrCode:=6702;
p_oErrDesc:='No es válido usar el caracter de control con ascii '||v_Ascii||' en la dirección de correo electrónico';
return;
end if;
if v_Ascii=32 then
p_oErrCode:=6704;
p_oErrDesc:='No es válido usar el caracter ESPACIO (ascii '||v_Ascii||') en el correo-e "'||v_Correo_ajustado||k_COMILLAS;
return;
end if;
if v_Ascii>=127 then
p_oErrCode:=6706;
p_oErrDesc:='No es válido usar el caracter con ascii '||v_Ascii||' en el correo-e "'||v_Correo_ajustado||k_COMILLAS;
return;
End if;
-- Otros Caracteres a evitar
-- Referecia: http://www.remote.org/jochen/mail/info/chars.html
if v_Caracter in ('(',')','[',']','<','>', -- excluir paréntesis
',',';',':', -- signos de puntuación
'\','!',chr(34),'#','$','%','/',chr(96),chr(124)) then -- backslach y comillas
p_oErrCode:=6708;
p_oErrDesc:='No es válido usar el caracter "'||v_Caracter||'" (ascii '||v_Ascii||') en el correo-e "'||v_Correo_ajustado||k_COMILLAS;
return;
End if;
-- validar que no sea un caracter punto seguido
if i=1 then
if v_Caracter=k_PUNTO then
p_oErrCode:=6710;
p_oErrDesc:='el primer caracter NO puede ser "'||v_Caracter||'" (ascii '||v_Ascii||') en el correo-e "'||v_Correo_ajustado||k_COMILLAS;
return;
End if;
if v_Caracter=k_ARROBA then
p_oErrCode:=6712;
p_oErrDesc:='El primer caracter NO puede ser "'||v_Caracter||'" (ascii '||v_Ascii||') en el correo-e "'||v_Correo_ajustado||k_COMILLAS;
return;
End if;
Else
if v_Caracter=k_PUNTO then
if v_Ult_caracter=k_PUNTO then
p_oErrCode:=6714;
p_oErrDesc:='No pueden estar seguidos 2 caracteres punto (ascii '||v_Ascii||') en el correo-e "'||v_Correo_ajustado||k_COMILLAS;
return;
elsif v_Ult_caracter=k_ARROBA then
p_oErrCode:=6716;
p_oErrDesc:='Después del caracter "@" no puede seguir el caracter "." (ascii '||ascii('.')||') en el correo-e "'||v_Correo_ajustado||k_COMILLAS;
return;
End if;
Elsif v_Caracter=k_ARROBA then
if v_Ult_caracter=k_PUNTO then
p_oErrCode:=6718;
p_oErrDesc:='Después de un caracter "." (ascii '||ascii('.')||') no puede seguir un caracter "@" en el correo-e "'||v_Correo_ajustado||k_COMILLAS;
return;
end if;
End if;
End if;
if v_Caracter=k_ARROBA then
v_Contar_arrobas:=v_Contar_arrobas+1;
if v_Contar_arrobas>1 then
p_oErrCode:=6720;
p_oErrDesc:='Solo puede tener 1 vez el caracter "'||v_Caracter||'" (ascii '||v_Ascii||') el correo-e "'||v_Correo_ajustado||k_COMILLAS;
return;
End if;
Else
if v_Contar_arrobas=0 then
v_Usuario:=v_Usuario||v_Caracter;
Else
v_Dominio:=v_Dominio||v_Caracter;
End if;
End if;
v_Ult_caracter:=v_Caracter;
End loop;
if v_Contar_arrobas=0 then
p_oErrCode:=6722;
p_oErrDesc:='NO contiene el caracter "@" el correo-e "'||v_Correo_ajustado||k_COMILLAS;
return;
End if;
if v_Dominio is null then
p_oErrCode:=6724;
p_oErrDesc:='NO contiene el nombre del servidor de correo (dominio) el correo-e "'||v_Correo_ajustado||k_COMILLAS;
return;
End if;
-- validar el dominio
-- determinar si el nivel superior es un país
v_Pos_punto_izquierdo:=instr(v_Dominio,k_PUNTO,-1,1);
if v_Pos_punto_izquierdo=0 then
p_oErrCode:=6726;
p_oErrDesc:='NO tiene un "dominio de nivel superior por código de páis" (ccTLD) o un "dominio de nivel superior genérico" (gTLD) el correo-e "'||v_Correo_ajustado||k_COMILLAS;
return;
End if;
--
v_Dominio_superior:=lower(substr(v_Dominio,v_Pos_punto_izquierdo));
if length(v_Dominio_superior)=1 then
p_oErrCode:=6728;
p_oErrDesc:='No puede terminar con el caracter "." el correo-e "'||v_Correo_ajustado||k_COMILLAS;
return;
Elsif length(v_Dominio_superior)=3 then
-- es un dominio de país ccTLD que usa 2 caracteres luego del punto
-- Falta validar que sea uno de los dominios aprovados por la INNAC
null;
Else
-- puede ser uno de los dominios genéricos gTLD
-- referencia al 2008-02-12: http://es.wikipedia.org/wiki/Dominio_de_nivel_superior
if v_Dominio_superior not in (lower('.aero'), -- para la Industria del transporte aéreo
lower('.asia'), -- la región de Asia
lower('.biz'), -- para los Negocios
lower('.cat'), -- para páginas en catalán
lower('.com'), -- para fines comerciales
lower('.coop'), -- para Cooperativas
lower('.edu'), -- para Educación
lower('.gov'), -- para Gobierno y Entidades Públicas
lower('.info'), -- para Información
lower('.int'), -- para Entidades u organizaciones Internacionales
lower('.jobs'), -- para Departamentos de empleo y recursos humanos en empresas
lower('.mil'), -- para el Departamento de Defensa de los Estados Unidos
lower('.mobi'), -- para empresas de telefonía móvil o servicios para móvil.
lower('.museum'), -- para los Museos
lower('.name'), -- para Nombres de personas
lower('.net'), -- para Infraestructura de red
lower('.org'), -- para organizaciones
lower('.pro'), -- para Profesionales
lower('.tel'), -- para servicios de comunicación por internet
lower('.travel')) -- para páginas de la industria de viajes y turismo
then
p_oErrCode:=6730;
p_oErrDesc:='No es válido el dominio "'||v_Dominio_superior||'" en el correo-e "'||v_Correo_ajustado||k_COMILLAS;
return;
End if;
End if;
Exception
when others then
p_oErrCode:=6799;
p_oErrDesc:='En pk_cliente.pr_validar_dir_correo al validar una dirección de correo-e, '||sqlerrm;
End pr_validar_dir_correo;


Bye

Alvalongo

Comments are in french but the code works perfectly.

It has a better validation than my owa_pattern validation.


Validation de l'adresse email en pl/sql.


function fnc_verif_email_adr( p_email_adr in varchar2 )
return boolean

is
-- Définition des caractères spéciaux a intégrés
l_double_quote char( 1 ) := chr(34);
l_single_quote char( 1 ) := chr(39);

-- Les caractères non permis et ceux permis a certain endroit
l_accent varchar2( 20 ) := 'çéèùìòàêûîôâëüïöä';
l_non_permis varchar2( 80 ) := ',`~^\| ' || l_double_quote || l_single_quote || l_accent;
l_permis varchar2(2) := '-.';

-- le nombre de caractère dans chacune des chaines
l_nbr_char_p integer := LENGTH( l_permis );
l_nbr_char_np integer := LENGTH( l_non_permis );

-- le DNS qui ne doit pas avoir plus de 255 caractères
l_dns varchar2(255);

begin
-- Un email null semble valide O0 Nicolas vanheuverzwijn 2009-07-06
if p_email_adr is null then
return false;
end if;
-- Rejet - Présence de 2 Arobas (ou plus) ou d'aucun
if INSTR(p_email_adr , '@',1,2) > 0 or INSTR(p_email_adr , '@',1,1) = 0 then
return false;
end if;

-- récuperation du DNS de l'adresse email
l_dns := LOWER( SUBSTR( p_email_adr , INSTR( p_email_adr , '@' , 1 , 1 ) + 1 ) );

-- Rejet - absence de point dans le DNS ou plusieurs point collé
if INSTR( l_dns , '.' , 1 ) = 0 or INSTR( l_dns , '..' , 1 ) > 0 then
return false;
end if;

-- Rejet - '-' et '.' permis dans un DNS mais pas au debut ni and la fin !
for j in 1..l_nbr_char_p loop
if INSTR( l_dns , SUBSTR( l_permis , j , 1 ) , 1 ) = 1 or
INSTR( l_dns , SUBSTR( l_permis , j , 1 ) , LENGTH( l_dns ) ) = LENGTH( l_dns ) then
return false;
end if;
end loop;

-- Rejet - pas de caractère non-permis dans le DNS
for j in 1..l_nbr_char_np loop
if INSTR( l_dns , SUBSTR( l_non_permis , j , 1 ) , 1) > 0 then
return false;
end if;
end loop;

return true;

exception
when others then
capg_pck_standard.prc_erreur_traitement
('capg_pck_courriel.fnc_adr_verify',
'DB'
,sqlcode
,sqlerrm
);

raise;

end fnc_verif_email_adr;