Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Unanswered: Once again I need help urgently:(

    Hello experts,

    Thanks to everyone I am on my way with my procedure. However, I need some help with oracle exception handlers. I have a database table that has 2fields. FirstEmail and SecondEmail.

    I have a stored procedure that loops through the rows and sends an email to the firstemail and cc's the second email. Works great. I am using UTL_SMTP in oracle 9. Now what I need is if any of the email addresses(I am entering these manually using the insert syntax) are invalid do not send a email to the recipient(firstemail) or the cc'd(SecondEmail) but rather send me an email telling me the email address that caused a problem as well as the error message. I am stumped on how to do this. Here is where I am at

    Code:
     EXCEPTION
             WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
             UTL_SMTP.MAIL(mail_conn, 'xxx@xxx.xxx');
            raise_application_error(-20000, 'Failed to send mail due to the following error: ' || sqlerrm);
             END;
        END LOOP;
    Thanks.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Do something like this, it will continue if an error is detected.

    Code:
    declare
    cursor my_cursor is
    select.....;
    
    begin
    
    for pnt in my_cursor loop
    
      begin
       utl_smtp.mail(mail_conn,'the_address');
      exception
       when utl_smtp.transient_error or utl_smtp.permanent_error then
        utl_smtp.mail(mail_conn,'my_address');
      end;
    end loop;
    end;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Apr 2006
    Posts
    140
    Hello Beilswth

    Thanks for your reply. I have tried this in my original struggles with little success. The structure of your code is exactly like mine but what should I do after the then in your code. What should this code do. Could you explain this. If I leave the code as follows I get the error encountered the symbol END when expecting the following. If either of the emails are invalid I need it to not send an email to those recipients. Is this what yoru example code should be doing. Is the system smart enough to say this email address is wrong so don't send it but rather send me an email telling me the specifics of the error. Thanks again

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Your smtp to your personal account should always word, but if you want to attempt to capture that possible error, then do the following. Every call to utl_smtp must be encloused in it's own anonymous block.


    declare
    cursor my_cursor is
    select.....;

    begin


    for pnt in my_cursor loop

    begin
    utl_smtp.mail(mail_conn,'the_address');
    exception
    when utl_smtp.transient_error or utl_smtp.permanent_error then

    begin
    utl_smtp.mail(mail_conn,'my_address');
    exception
    when others then null;
    end;

    end;
    end loop;
    end;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Apr 2006
    Posts
    140
    Sorry you say "Your smtp to your personal account should always word"...I'm assuming you meant it should always work. O.k I got you on that. I guess maybe what I asking for is not doable since the system has no way of recognizing whether the email is valid or invalid(whether the address exists). So the exception code that you added, where would the exception be stored?

    In my table I just added 1 invalid email address and ran my procedure. This is what happened. The cc'd got an email since he had a valid email address however in the email the recipient address was incorrect. What I wanted then was for the cc'd to not get this email if the recipient email address was invalid. I guess this is not possible..........Is this what you are saying.

    Where are exceptions stored because according to your code if the system encounters an error in this invalid email address it should email me, but it is not. I am using Outlook 2003.

    Do I really need this exception?

    Thanks again.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    When you say the email address is invalid, do you mean that the mailbox doesn't exist or that the address is malformed? Many mail servers will not let you know that a mailbox doesn't exist (error 550) on it's domain for obvious secutiry reasons, however if you have an email address that is malformed (mail@mail@test.com) , then you should get a error 500.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Apr 2006
    Posts
    140
    I mean that the mailbox doesn't exist. So fffff@yahoo.org does not exist. O.k so I was correct. Now when you say I should get a 500 error, where will I get this. Where can I see this 500 error. I just want to test it. According to the code if it sees this error it will report it somewhere but it should continue processing the next row and check to see if it finds error in that row and so forth. It should loop correct?

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I would suggest that you hard code a test without exception handling and see what error you get. See

    http://download-east.oracle.com/docs...p3.htm#1005700
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The PL/SQL procedure will NEVER know whether or not the message was susccessfully or not.
    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.

  10. #10
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    btw probably not much help, but I find calls to RAISE_APPLICATION_ERROR like this
    Code:
     EXCEPTION
             WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
             UTL_SMTP.MAIL(mail_conn, 'xxx@xxx.xxx');
            raise_application_error(-20000, 'Failed to send mail due to the following error: ' || sqlerrm);
    are generally better like this:
    Code:
    EXCEPTION
        WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
            UTL_SMTP.MAIL(mail_conn, 'xxx@xxx.xxx');
    
            raise_application_error
            ( -20000, 'Failed to send mail', TRUE );

Posting Permissions

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