Exceptions regarding utl_mail.send()

Question

I am sending emails using utl_mail.send(), and it is possible that some of the people that come through my cursor will not have email address's or will have invalid emails. I am having trouble finding out what exceptions I can use to make sure that my procedure doesnt bomb when it encounters one of these null emails. Example:

.....

procedure sendEmail                 --subroutine of my procedure
    BEGIN
        utl_mail.send(sender => email_from,
                  recipients => email_adr,
                  subject => email_subject,
                  mime_type => 'text/html',
                  message => email_body);

    --EXCEPTION SHOULD GO HERE

    END;


....
WHILE CURSOR%FOUND THEN
LOOP
    IF ..... THEN
        sendEmail;
    END IF;
....
END LOOP;

This might be confusing, but please ask questions if you have any. Thanks for the help.

score 2 · Accepted Answer · answered Nov 09 '11 at 21:58

Why call UTL_MAIL.SEND if EMAIL_ADR is NULL? Why not simply check the email address before trying to send the email, i.e.

BEGIN
  IF( email_adr IS NOT NULL )
  THEN
    utl_mail.send ...
  END IF;
END;

Since it sounds like you are fine with any particular email failing for whatever reason it happens to fail, I'd be tempted to just catch all exceptions and write them to an exceptions table, i.e.

PROCEDURE send_email
AS
  l_error_code    NUMBER;
  l_error_message VARCHAR2(255);
BEGIN
  IF( is_valid_email( email_adr ) )
  THEN
    utl_mail.send ...
  END IF;
EXCEPTION
  WHEN others THEN
    l_error_code    := SQLCODE;
    l_error_message := SQLERRM;
    log_email_failure( email_from,
                       email_adr,
                       email_subject,
                       email_body,
                       l_error_code,
                       l_error_message );
END;

where LOG_EMAIL_FAILURE is simply a logging method that writes the failures to a log table somewhere and IS_VALID_EMAIL is a routine that checks whether the email address is NULL and if it appears valid (there are tons of regular expressions out there depending on how pedantic you want to be). Unless you're going to make the code much more complicated to try to differentiate between, say, transient SMTP errors and permanent SMTP errors, there is probably no need to have separate exception handlers. And, of course, someone would need to monitor the table of failed emails to ensure there isn't a more systemic problem (i.e. the SMTP server is down).

Okay, this might be a stupid question, but using the code above, will my procedure continue to run after an invalid email address is encountered? I am not using the is_valid_email function. I am checking if the email is null, then going to utl_mail.send, so if an invalid email comes in, I want to hit my exception, but then continue on with my procedure. Does that make sense? — Mr Man, Nov 10 '11 at 14:45
@Jonathon - What do you mean by "invalid email address"? If you mean a syntactically invalid address (i.e. one that's missing the @), then SMTP server will reject the email, control will pass to the exception handler of the `send_email` procedure and then back to the calling PL/SQL block for the next iteration of the loop. If you mean an email address that is merely undeliverable but syntactically valid, the SMTP server will generally not throw an exception and you may get a bounce some number of hours or days later. — Justin Cave, Nov 10 '11 at 15:28
I meant syntactically, and it looks like you are correct, once I got a chance to test it. Thanks!! — Mr Man, Nov 10 '11 at 15:31

Exceptions regarding utl_mail.send()

1 Answers1