Skip to Main Content
  • Questions

  • How do I use UTL_SMTP to automatically send an email when a database table column value change?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Cornellius.

Asked: January 16, 2017 - 5:13 pm UTC

Last updated: January 17, 2017 - 12:48 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

How do I use UTL_SMTP to send an automated email after my database table (A.K.A. user_objects) column (A.K.A status) value change from "Valid" to "Invalid"?

--Example: I would like an automated email sent when the query listed below has an invalid value using utl_smtp.

select *
from user_objects
where status = 'INVALID';

Thank you for your assistance in advance.

and Connor said...

Options

a) a database job that runs every (say) 5 mins and looks
b) a database trigger that fires after every ddl

Plenty of examples of both on asktom.oracle.com

In both cases, they can just run an anonymous block like this

declare
  l_cnt int;
begin

  select count(*) into l_cnt
  from   user_objects
  where  status = 'INVALID';
  
  if l_cnt > 0 then
    utl_mail.send(sender     => 'the_database@mycompany.com',
                  recipients => 'admin1@mycompany.com,admin2@mycompany.com',
                  subject    => 'ALERT: Invalid',
                  message    => 'We found '||l_cnt||' invalid objects');
  end if;                  
end;


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent response and answer.

Cornellius, January 17, 2017 - 11:23 am UTC

The response to my question was absolutely concise and clear.

utl_smtp is not transactional

Jeffrey Kemp, January 17, 2017 - 10:20 pm UTC

Once your code calls utl_smtp successfully, an email will be sent - so running this from a trigger carries a risk that duplicate emails may be sent.

It would be more reliable to use a queue (e.g. AQ).

Or UTL_MAIL :)

Jeffrey Kemp, January 17, 2017 - 10:21 pm UTC

utl_smtp / utl_mail
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library