Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2012
    Posts
    6

    Unanswered: NEED SMTP return code or error code for invalid email address

    Hello,

    I have a PL/SQL procedure. where i am passing the email address as Input paramater as below
    CREATE OR REPLACE PROCEDURE SP_verify (p_recipient IN VARCHAR2)
    AS
    l_mailhost VARCHAR2 (255) := 'e2ksmtp01.e2k.com';
    --'aria.us.oracle.com';
    l_mail_conn UTL_SMTP.connection;
    l_reply UTL_SMTP.reply;
    BEGIN
    l_mail_conn := UTL_SMTP.open_connection (l_mailhost, 25);
    UTL_SMTP.helo (l_mail_conn, l_mailhost);
    l_reply := UTL_SMTP.vrfy (l_mail_conn, p_recipient);
    DBMS_OUTPUT.put_line ('code = ' || l_reply.code);
    DBMS_OUTPUT.put_line ('text = ' || l_reply.text);
    UTL_SMTP.quit (l_mail_conn);
    END;

    But if i pass valid or invalid mail address always get 252 as return code.
    Is there is a way i can get 550 as return code for invalid mail address.Please help.
    It is an urgent requirement

    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
    is google broken for you?

    Let me google that for you
    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
    Mar 2012
    Posts
    6
    I google and after that only i have posted as i did not met the requuirement

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I google and after that only i have posted as i did not met the requuirement

    It appears then that your requirement remains unfulfilled.
    Sorry about that.
    :-(
    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.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    252 indicates that the receiving server will NOT verify the email address for you (very common) but will accept the email and attempt to deliver it. 550 indicates that the server will check for valid addresses and the one you used is invalid. It is not that uncommon for a server to not send a 550 to stop users from being scanned for valid email addresses.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Mar 2012
    Posts
    6
    Thanks beilstwh

    Can you please help me to validate email address.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    if the server your connecting to doesn't allow address validation, there is no way to get a 550 back.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Mar 2012
    Posts
    6
    Hmmm is there anyway other then this we can validate the email address.
    Please help

  9. #9
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Question

    You need to be more specific about what you mean by "email address validation".
    1) If you just need to validate that the user input "makes sense" and it looks like an email address, then a regular expression is the way to go.
    Code:
    dayneo@SANDBOX> declare
      2    type address_list is table of varchar2(32767);
      3    l_list address_list;
      4  begin
      5
      6    l_list := address_list('valid.address@service.com',
      7                           'this is not an address',
      8                           'this is dodgy@service.com',
      9                           'weird,chars.''in this/address\@service.com',
     10                           'name.surname@gmail.com',
     11                           'alias@hotmail.com',
     12                           'CaseInsensitive@branch.corp.us');
     13
     14    for i in l_list.first..l_list.last loop
     15      if not regexp_like(l_list(i), '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$
    ', 'i') then
     16        dbms_output.put_line('"' || l_list(i) || '" is invalid');
     17      else
     18        dbms_output.put_line('"' || l_list(i) || '" is a valid email address'
    );
     19      end if;
     20    end loop;
     21
     22  end;
     23  /
    "valid.address@service.com" is a valid email address
    "this is not an address" is invalid
    "this is dodgy@service.com" is invalid
    "weird,chars.'in this/address\@service.com" is invalid
    "name.surname@gmail.com" is a valid email address
    "alias@hotmail.com" is a valid email address
    "CaseInsensitive@branch.corp.us" is a valid email address
    dayneo@SANDBOX>
    /
    Email addresses can take many forms, I would suggest reading the following for more details on the regular expression used: How to Find or Validate an Email Address

    2) If you want to check that the email address actually exists; well, you are bang outta luck! This is absolutely not possible to get a reliable return. Email works on a routing method which does not guarantee your email ever gets to the recipient.

    Email 101:
    It's like posting a letter at your local post office (assume London) to a destination: 192 Who Knows Road, Marzipan, South Africa. Your local post office in London will accept the letter without knowing if "192 Who Knows Road" even exists in South Africa. All they know is the general direction to send the letter, i.e. South Africa. And that's the only thing they know.

    So they send the letter to South Africa, but when it arrives, the post office there says, "Hey, there is no Marzipan here", and the South African post office sends the letter back to where it came from.

    Now the exact same thing is happening in email, one server does not necessarily know of the other (let alone the user account), but just knows the general direction in which to send the message. This means that the email could come back as undeliverable, or it could never come back and also not be delivered.

    Email is a great communication tool, but it is no where near reliable.

    Implications on systems:
    The bottom line is that you can never reliably "verify" an email address. Because of this, you should always develop your system to fail gracefully when encountering undeliverable messages, and never base business processes on the delivery of email..... in my opinion...

  10. #10
    Join Date
    Mar 2012
    Posts
    6
    Thanks for your reply

    the email address belongs to same LDAP server. Example: firstname.lastname@mailserver.com.

    In this mailserver is the ldap server which is being used by our large group. We need to validate against it.
    so any suggestions here??

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by mubasheer99 View Post
    Thanks for your reply

    the email address belongs to same LDAP server. Example: firstname.lastname@mailserver.com.

    In this mailserver is the ldap server which is being used by our large group. We need to validate against it.
    so any suggestions here??

    what does problem or solution have to do with Oracle RDBMS?
    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.

  12. #12
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Question

    Quote Originally Posted by mubasheer99 View Post
    In this mailserver is the ldap server which is being used by our large group. We need to validate against it.
    So that's the real question... How do you read from an LDAP server from PL/SQL?
    I have no experience of this myself, but this article seems to give a good starting point.

Posting Permissions

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