Oracle: Send alerts via email

Sometimes we want to send an email when something suspicious happens in a database. Oracle is able to natively send emails.

Example: We want to receive an alert every time a user is deleted, so that we can immediately check if this happened for a good reason or not.

database and perform some checks about the most important data. However this would be overcomplicated, and requires the installation of some tools.

Trigger code

Here we’ll see how to achieve the same result with a trigger.

CREATE TRIGGER audit_user_delete
    BEFORE DELETE
    ON user
    FOR EACH ROW
DECLARE
    v_user_id  INT;
    v_mail     UTL_SMTP.connection;
BEGIN
    v_mail := UTL_SMTP.open_connection('smtp.example.com', 25);
    UTL_SMTP.helo(l_mail_conn, 'smtp.example.com');
    UTL_SMTP.mail(l_mail_conn, 'dba@example.com');
    UTL_SMTP.rcpt(l_mail_conn, 'john.smith@example.com');
    UTL_SMTP.data(l_mail_conn, 'User has been deleted: ' || :old.id);
    UTL_SMTP.quit(l_mail_conn);
END;
/

Here’s what happens when a user is deleted:

  • The audit_user_delete trigger is fired;
  • An UTL_SMTP instance is assigned to v_mail, which sends an email;
  • :old.id is the deleted user’s id.

Reference

Oracle documentation:

Other articles: