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.

Answer 1

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).