Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2007
    Posts
    47

    Unanswered: email validation

    Hi,

    Can anyone please send me function in pl/sql to validate email address.

    Thanks in advance.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    IIRC, it does not exist, cuz there is no way for Oracle to know whether any "arbitrary string" maps to a deliverable email address anywhere around the world.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    why would you validate internally?
    that would seem to be a front-end validation to me.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jan 2007
    Posts
    47
    Hi,

    I need to validate the following rules for email address

    The following pattern applies for domains that are IP addresses, rather than symbolic names. E.g. joe@[123.124.233.4] is a legal email addr. NOTE: The square brackets are required.

    An email addr cannot contain control characters or DLE

    Change .comm to .com

    Thanks in advance

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    We validate email addresses for our Ecom site. One thing that you can do is to validate that the Top Level Domains are legal. We have a cron job that runs the following code once a week to maintain an oracle table of ALL legal TLD's

    Code:
    declare
    wrk_string varchar2(2000);
    tld        varchar2(32);
    CNT        NUMBER;
    
    begin
    
      SELECT  REPLACE(utl_http.request('http://data.iana.org/TLD/tlds-alpha-by-domain.txt'),CHR(10),',')
      INTO WRK_STRING
      FROM DUAL;
      
      WRK_STRING := RTRIM(SUBSTR(WRK_STRING,INSTR(WRK_STRING,',')+1));
      WRK_STRING := RTRIM(SUBSTR(WRK_STRING,INSTR(WRK_STRING,',')+1));
      
      TLD := NULL;                                         
      FOR PNT IN 1 .. LENGTH(WRK_STRING) LOOP
        IF SUBSTR(WRK_STRING,PNT,1) = ',' THEN
          SELECT COUNT(*)
          INTO CNT
          FROM LEGAL_DOMAINS
          WHERE DOMAIN = UPPER(TLD);
          
          IF CNT = 0 THEN
            INSERT INTO LEGAL_DOMAINS VALUES(TLD,'Automatic Generated Entry','Y');
            DBMS_OUTPUT.PUT_LINE('New TLD added - '||tld);
          END IF;
          
          TLD := NULL;
        else
          TLD := TLD||SUBSTR(WRK_STRING,PNT,1);
        end if;  
      END LOOP;
      
      COMMIT;    
    end;
    The structure of the LEGAL_DOMAINS table is

    desc legal_domains
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    DOMAIN NOT NULL VARCHAR2(32)
    DES NOT NULL VARCHAR2(80)
    VALID NOT NULL VARCHAR2(1)

    The valid is a flag that can be manually set to disallow an otherwise valid TLD.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jan 2007
    Posts
    47
    How do I clean up wrong email address. Suppose, is email id has ujju@yahoo.comm, is there any way I can clean up this wrong id to valid email id.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >is there any way I can clean up this wrong id to valid email id.
    Yes, use either DBMS_OUIJA or UTL_CRYSTALBALL to return the valid email id using the bad address as single input.
    Last edited by anacedent; 01-30-07 at 19:16.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by anacedent
    >is there any way I can clean up this wrong id to valid email id.
    Yes, use either DBMS_OUIJA or UTL_CRYSTALBALL to return the valid email id using the bad address as single input.
    You could also use DBMS_RANDOM, on which, AFAIK, DBMS_OUIJA and UTL_CRYSTALBALL are based.

    More seriously, if you validate the domain as beilstwh suggests, then any ".comm" domain would not be correct. Then return the error and let the user correct it. I don't think you could seriously think of automatically correcting email addresses.

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    or, you:
    - require them to type it in twice
    - reuire them to click a verification once you send them an email

    if they have to receive an email to verify then they will be sure to type it in correctly
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •