E-mail validation

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
racandradebr
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 3
Joined: Fri, 30 Sep 2005 12:26 am
Location: SP

Does anyone know a routine to validate e-mail address?
User avatar
dr_gori
Moderador
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

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!
emedino
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 2
Joined: Sat, 13 Aug 2005 9:04 am
Location: RS

I did a function and it is working:

Select all

-- 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;
User avatar
Toad
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 253
Joined: Fri, 18 Nov 2005 2:14 pm
Location: Seattle, WA
Contact:

I tried to use this function, but at the time of compiling it returned the following error:

Select all

OWA_PATTERN.MATCH 
 
Wrong number or Types of arguments in Call to 'MATCH' 
 
Line 9, Column 29
User avatar
dr_gori
Moderador
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

Po ... here it worked right ... I'm using Oracle 9.2.0.4.0

Select all

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>  
User avatar
Toad
Rank: DBA Pleno
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

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?
charlesmafra
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 8
Joined: Thu, 19 Aug 2004 9:45 am
Location: Angola
Contact:
Charles Mafra
Oracle Developer

Toad, the function I developed is this, see if it helps you.

Select all

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; 
/ 
User avatar
Toad
Rank: DBA Pleno
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

This worked ok, man!
Thanks.
User avatar
dr_gori
Moderador
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

Ai goes a simple:

Select all

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;
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest