Does anyone know a routine to validate e-mail address?
E-mail validation
- dr_gori
- Moderador
- Posts: 5024
- Joined: Mon, 03 May 2004 3:08 pm
- Location: Portland, OR USA
- Contact:
Thomas F. G
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
I do not know any ready, but it is very easy to do a "generic".
Example:
* Testa If the character exists
* tests if there is text before that character.
* Forest if there is text after this character.
* Testa If there is a point after @
etc ...
I have heard of well complex email routines, which even give a type of a "Ping" in the URL that comes after @. (Example: joao@blabla.com -> will make a ping in blabla.com to see if it exists).
The ideal is to send an email to subject with a random strain and he confirm this sequence on the site or the program after receiving this email. (That is, it is certain that his email exists and that is his own email)
good luck!
Example:
* Testa If the character exists
* tests if there is text before that character.
* Forest if there is text after this character.
* Testa If there is a point after @
etc ...
I have heard of well complex email routines, which even give a type of a "Ping" in the URL that comes after @. (Example: joao@blabla.com -> will make a ping in blabla.com to see if it exists).
The ideal is to send an email to subject with a random strain and he confirm this sequence on the site or the program after receiving this email. (That is, it is certain that his email exists and that is his own email)
good luck!
I did a function and it is working:
-- esta função verifica se o email tem o @ e o .(ponto).
-- verifica espaço em branco depois do "."
-- verifica espaço em branco depois do "@"
-- retorna false quando não tem e true quando tem
--
function f_valida_email ( p_email in varchar2,
p_erro_tela out varchar2 ) return boolean is
--
v_email varchar2(200);
-- expressão para validar e-mail
v_pattern varchar2(200) := '^[a-z]+[\.\_\-[a-z0-9]+]*[a-z0-9]@[a-z0-9]+\-?[a-z0-9]{1,63}\.?[a-z0-9]{0,6}\.?[a-z0-9]{0,6}\.[a-z]{0,6}$';
begin
v_email := lower(p_email);
if not OWA_PATTERN.MATCH (v_email,v_pattern) then
p_erro_tela := 'email inválido!';
return (FALSE);
end if;
if instr(p_email,'..') > 0 then
p_erro_tela := 'email inválido!';
return (FALSE);
end if;
--
return (TRUE);
--
end f_valida_email;
I tried to use this function, but at the time of compiling it returned the following error:
OWA_PATTERN.MATCH
Wrong number or Types of arguments in Call to 'MATCH'
Line 9, Column 29
- dr_gori
- Moderador
- Posts: 5024
- Joined: Mon, 03 May 2004 3:08 pm
- Location: Portland, OR USA
- Contact:
Thomas F. G
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Po ... here it worked right ... I'm using Oracle 9.2.0.4.0
SQL> desc owa_pattern
FUNCTION AMATCH RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINE VARCHAR2 IN
FROM_LOC NUMBER(38) IN
PAT VARCHAR2 IN
FLAGS VARCHAR2 IN DEFAULT
FUNCTION AMATCH RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINE VARCHAR2 IN
FROM_LOC NUMBER(38) IN
PAT TABLE OF VARCHAR2(4) IN/OUT
FLAGS VARCHAR2 IN DEFAULT
FUNCTION AMATCH RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINE VARCHAR2 IN
FROM_LOC NUMBER(38) IN
PAT VARCHAR2 IN
BACKREFS TABLE OF VARCHAR2(32767) OUT
FLAGS VARCHAR2 IN DEFAULT
FUNCTION AMATCH RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINE VARCHAR2 IN
FROM_LOC NUMBER(38) IN
PAT TABLE OF VARCHAR2(4) IN/OUT
BACKREFS TABLE OF VARCHAR2(32767) OUT
FLAGS VARCHAR2 IN DEFAULT
FUNCTION CHANGE RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINE VARCHAR2 IN/OUT
FROM_STR VARCHAR2 IN
TO_STR VARCHAR2 IN
FLAGS VARCHAR2 IN DEFAULT
PROCEDURE CHANGE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINE VARCHAR2 IN/OUT
FROM_STR VARCHAR2 IN
TO_STR VARCHAR2 IN
FLAGS VARCHAR2 IN DEFAULT
FUNCTION CHANGE RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
MLINE RECORD IN/OUT
ROWS TABLE OF VARCHAR2(32767) IN/OUT
NUM_ROWS NUMBER(38) IN/OUT
PARTIAL_ROW BOOLEAN IN/OUT
FROM_STR VARCHAR2 IN
TO_STR VARCHAR2 IN
FLAGS VARCHAR2 IN DEFAULT
PROCEDURE CHANGE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
MLINE RECORD IN/OUT
ROWS TABLE OF VARCHAR2(32767) IN/OUT
NUM_ROWS NUMBER(38) IN/OUT
PARTIAL_ROW BOOLEAN IN/OUT
FROM_STR VARCHAR2 IN
TO_STR VARCHAR2 IN
FLAGS VARCHAR2 IN DEFAULT
PROCEDURE GETPAT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ARG VARCHAR2 IN
PAT TABLE OF VARCHAR2(4) IN/OUT
FUNCTION MATCH RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINE VARCHAR2 IN
PAT VARCHAR2 IN
FLAGS VARCHAR2 IN DEFAULT
FUNCTION MATCH RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINE VARCHAR2 IN
PAT TABLE OF VARCHAR2(4) IN/OUT
FLAGS VARCHAR2 IN DEFAULT
FUNCTION MATCH RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINE VARCHAR2 IN
PAT VARCHAR2 IN
BACKREFS TABLE OF VARCHAR2(32767) OUT
FLAGS VARCHAR2 IN DEFAULT
FUNCTION MATCH RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINE VARCHAR2 IN
PAT TABLE OF VARCHAR2(4) IN/OUT
BACKREFS TABLE OF VARCHAR2(32767) OUT
FLAGS VARCHAR2 IN DEFAULT
FUNCTION MATCH RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
MLINE RECORD IN
ROWS TABLE OF VARCHAR2(32767) IN
NUM_ROWS NUMBER(38) IN
PARTIAL_ROW BOOLEAN IN
PAT VARCHAR2 IN
RLIST RECORD OUT
ROWS TABLE OF NUMBER(38) OUT
NUM_ROWS NUMBER(38) OUT
FLAGS VARCHAR2 IN DEFAULT
FUNCTION MATCH RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
MLINE RECORD IN
ROWS TABLE OF VARCHAR2(32767) IN
NUM_ROWS NUMBER(38) IN
PARTIAL_ROW BOOLEAN IN
PAT TABLE OF VARCHAR2(4) IN/OUT
RLIST RECORD OUT
ROWS TABLE OF NUMBER(38) OUT
NUM_ROWS NUMBER(38) OUT
FLAGS VARCHAR2 IN DEFAULT
SQL>
- Toad
- Rank: DBA Pleno
- Posts: 253
- Joined: Fri, 18 Nov 2005 2:14 pm
- Location: Seattle, WA
- Contact:
Matheus Gonçalves
matheus.dev
twitter.com/developer__c
matheus.dev
twitter.com/developer__c
My version is:
Version Oracle9i Release 9.2.0.5.0 - Production
Server 9.2
Client 7.3
Should it work when compiling right?!: Roll:
The version of Forms TB interferes?
Version Oracle9i Release 9.2.0.5.0 - Production
Server 9.2
Client 7.3
Should it work when compiling right?!: Roll:
The version of Forms TB interferes?
-
- Rank: Estagiário Sênior
- Posts: 8
- Joined: Thu, 19 Aug 2004 9:45 am
- Location: Angola
- Contact:
Charles Mafra
Oracle Developer
Oracle Developer
Toad, the function I developed is this, see if it helps you.
create or replace function f_valida_email (p_email in varchar2) return boolean deterministic is
-- ####################################################################################################################################################
-- # Data: 31/01/2006 #
-- # Autor: Charles Mafra cmafra@gmail.com #
-- # Objetivo: Validar E-mail usando Expressões Regulares #
-- # Lógica O e-mail é composto por #
-- # 1 String válida um sinal de @ (arroba) e outra string válida #
-- # Sendo a definição de string como: Uma següência de Caracteres entre 'a' e 'z' , 'A' e 'Z' e finalmente '0' e '9' #
-- # mais o sinal de "_" (underscore), o sinal de "." ponto (sendo este válido apenas entre següência de Caracteres) #
-- # Exemplo de Emails válidos: #
-- # joao.silva@hotmail.com #
-- # joao2000.silva@terra.com.br #
-- # joao2.silva.2006@orcangola.co.ao.gov #
-- # joao2.silva.2006@2006.year.net #
-- # Exemplo de Emails inválidos (motivo): #
-- # joao.silva@hot mail.com (espaço em branco) #
-- # joão.silva@hotmail.com (acento) #
-- # joao.silva@hotmail@com (mais de 1 arroba) #
-- # joao.silva@hotmailcom (falta de ponto no domínio) #
-- # #
-- # A função Deterministic tem seu retorno armazenado na SGA para evitar reprocessamento do resultado (aumento de performance) #
-- # Para saber mais sobre expressões regulares visite o site: #
-- # http://oraqa.com/2006/01/20/how-to-check-a-string-format-with-the-owa_patternmatch-function/ #
-- ####################################################################################################################################################
begin
if owa_pattern.match(p_email,'^\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}'||
'@\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}$') then
dbms_output.put_line('email válido : '||p_email);
return true;
else
dbms_output.put_line('email inválido : '||p_email);
return false;
end if;
end f_valida_email;
/
- dr_gori
- Moderador
- Posts: 5024
- Joined: Mon, 03 May 2004 3:08 pm
- Location: Portland, OR USA
- Contact:
Thomas F. G
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Ai goes a simple:
CREATE OR REPLACE FUNCTION xx_check_email(l_user_name IN VARCHAR2)
RETURN VARCHAR2 IS
l_dot_pos NUMBER;
l_at_pos NUMBER;
l_str_length NUMBER;
BEGIN
l_dot_pos := instr(l_user_name
,'.');
l_at_pos := instr(l_user_name
,'@');
l_str_length := length(l_user_name);
IF ((l_dot_pos = 0) OR (l_at_pos = 0) OR (l_dot_pos = l_at_pos + 1) OR
(l_at_pos = 1) OR (l_at_pos = l_str_length) OR
(l_dot_pos = l_str_length))
THEN
RETURN 'FAILURE';
END IF;
IF instr(substr(l_user_name
,l_at_pos)
,'.') = 0
THEN
RETURN 'FAILURE';
END IF;
RETURN 'SUCCESS';
END xx_check_email;
-
- Information
-
Who is online
Users browsing this forum: No registered users and 1 guest