Exception Handlng with Oracle UTL_SMTP
Posted on 2007-10-05
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.