• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 929
  • Last Modified:

Exception Handlng with Oracle UTL_SMTP

I've successfully used the UTL_SMTP package for some time in Oracle 9i.  I can't take credit for writing the stored procedure I'm using; I initially adapted one written by an experts exchange user.  I use the procedure to send notification emails to customers and I subsequently update a customer_notified field in a table as part of the process.  I'd like to detect the success of sending the email and leave the customer_notified set to null if any problem is encountered sending the email.  Perhaps an output parameter passed back to the trigger that calls the stored proc?  I tried something to this effect but I may not be managing all the possible exceptions properly which may cause issues in using an output parameter effectively.  Furthermore, I may store multiple email addresses for one customer.  We typically do this in one field on the front-end just separating email addresses with semi colons.  Even if one email sends correctly, but the other fails, I'd still like to consider this a successful send, and thus update the customer_notified field.  Any thoughts / solutions greatly appreciated.
0
mdonovan_88
Asked:
mdonovan_88
  • 2
1 Solution
 
jwahlCommented:
you should post the procedure ... or do you have a link for the source?
0
 
sdstuberCommented:
is this question still open?  any further assistance needed?

For multiple addresses, you can either submit all of them at once, or send individual emails.

The former is more efficient, but if you want to accept any successful send as good, then  you may need to go with the latter.

Put a wrapper around your send process to iterate through the address list and send the same message to each.

Also, a successful send via utl_smtp only means it made it to the smtp server ok.  The message could die on the server, you could get bounce-backs from invalid recipient addresses,
firewalls, network errors, junk filters, etc can all prevent some or any of the messages from making it to your targets but your code will have no means of detecting that.  It can only talk to the smtp server and the communication visibility ends there.
0
 
sdstuberCommented:
I believe I've answered the question.  Given that the user already has working code and ranks themselves beyond the "beginner" level, I think the guidelines I posted are sufficient, but I'm willing to assist further if the asker needs more help and can post the current state of the procedure.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now