Exception Handlng with Oracle UTL_SMTP

Posted on 2007-10-05
Last Modified: 2013-12-19
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.
Question by:mdonovan_88
    LVL 12

    Expert Comment

    you should post the procedure ... or do you have a link for the source?
    LVL 73

    Accepted Solution

    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.
    LVL 73

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now