Skip to main content

An Application Alerting Utility

A few weeks ago, Mike Hichwa asked me to come up with a package that would implement a simple alerting utility: specify a triggering event (based on a query or a PL/SQL expression) and then take the specified actions when it is triggered.

Seeing as he is my boss, I said "OK" and got to work (my favorite kind of work: writing some PL/SQL code). We did our usual bit to expand scope, then did our usual bit to agree that this is enough for a first pass. And then Mike said: "Go ahead and write a blog post about it, share it with the community."

So here goes.

The basic idea is we start up a job using DBMS_SCHEDULER that runs every N minutes. When it runs, it checks to see if any alerts need to be triggered. If so, it then takes one or more actions associated with that alert.

Let's start with our tables.

Utility configuration

How often should the job wake up? Should it disable checking for alerts? What is the name of the "callout" for sending emails? And if I am using Oracle Application Express, what is the workspace name? (needed to use APEX_MAIL to send emails)
CREATE TABLE alert_config
   job_wakeup_minutes   INTEGER,
   keep_checking        VARCHAR2 (1),
   send_email_proc_name VARCHAR2 (300),
   apex_workspace_name  VARCHAR2(100)


All the registered alerts. The trigger code is executed to determine if the alert actions should be run. The trigger type describes the code and how to execute it. You can set the frequency with which the alert is checked, how often to check again once it was triggered, and more.
   id                     NUMBER NOT NULL 
      CONSTRAINT alerts_id_pk PRIMARY KEY,
   alert_name             VARCHAR2 (100) 
      CONSTRAINT alerts_alert_name_unq UNIQUE,
   description            VARCHAR2 (4000),
   trigger_code           VARCHAR2 (4000),
   trigger_type           VARCHAR2 (100),
   check_frequency        VARCHAR2 (1000),
   recurrence_frequency   VARCHAR2 (1000),
   ignore_failures        VARCHAR2 (1),
   is_active              VARCHAR2 (1),
   last_run_id            NUMBER,
   created                DATE NOT NULL,
   created_by             VARCHAR2 (255) NOT NULL,
   updated                DATE NOT NULL,
   updated_by             VARCHAR2 (255) NOT NULL,
   CONSTRAINT trigger_type_choices CHECK
      (trigger_type IN ('ONEROW',
   CONSTRAINT ignore_failures_yn 
      CHECK (ignore_failures IN ('Y', 'N')),
   CONSTRAINT active_yn CHECK (is_active IN ('Y', 'N'))

Alert Actions

The action(s) associated with an alert. In my just-a-little-more-than proof-of-concept version, I implement support for running PL/SQL procedures and sending emails. The rest, I leave up to those of you who have need and/or interest. I will be happy to incorporate them back into the utility if you send me the code.
CREATE TABLE alert_actions (
   id                NUMBER NOT NULL 
      CONSTRAINT alert_actions_id_pk PRIMARY KEY,
   alert_id          NUMBER
      CONSTRAINT alert_actions_alert_id_fk
   action_sequence   INTEGER,
   action_type       VARCHAR2 (100) NOT NULL,
   action_code       VARCHAR2 (4000),
   send_email_to     VARCHAR2 (4000),
   email_subject     VARCHAR2 (4000),
   email_body        CLOB,
   email_query       VARCHAR2 (4000),
   created           DATE NOT NULL,
   created_by        VARCHAR2 (255) NOT NULL,
   updated           DATE NOT NULL,
   updated_by        VARCHAR2 (255) NOT NULL,
   CONSTRAINT action_type_choices CHECK
      (action_type IN ('EMAIL',

Alert Runs

Let's keep track of each run of an alert.
CREATE TABLE alert_runs (
   id                       NUMBER NOT NULL 
      CONSTRAINT alert_runs_id_pk PRIMARY KEY,
   alert_id                 NUMBER
      CONSTRAINT alert_runs_alert_id_fk
   started_at               DATE,
   completed_at             DATE,
   completed_successfully   VARCHAR2 (1),
   results                  VARCHAR2 (4000),
   errors                   VARCHAR2 (4000),
   created                  DATE NOT NULL,
   created_by               VARCHAR2 (255) NOT NULL,
   updated                  DATE NOT NULL,
   updated_by               VARCHAR2 (255) NOT NULL,
   CONSTRAINT success_yn 
      CHECK (completed_successfully IN ('Y', 'N'))

The Alert Log

Very simple table to store tracing and error logging. You might want to substitute this and the associated code in my package with Logger.
CREATE TABLE alert_log (
   id           NUMBER NOT NULL 
      CONSTRAINT alert_log_id_pk PRIMARY KEY,
   info         VARCHAR2 (4000),
   created      DATE NOT NULL,
   created_by   VARCHAR2 (255) NOT NULL,
   updated      DATE NOT NULL,
   updated_by   VARCHAR2 (255) NOT NULL

Start the alert checking job

Well, that's easy. Let's have it wake up every minute.
   alert_mgr.start_check_alerts_job (every_n_minutes_in => 1);

Register alerts

Now I will register my first - and very basic - alert.
   alert_mgr.register_alert (
      alert_name_in             => 'Always True',
      description_in            => 'Trigger is TRUE, display string',
      trigger_code_in           => 'TRUE',
      trigger_type_in           => 'RETURNTRUE',
      check_frequency_in        => 'H',
      recurrence_frequency_in   => 'D',
      ignore_failures_in        => 'Y',
      action_type_in            => 'PROC',
      action_code_in            => 'alert_mgr.log(''PROC EXECUTED'')',
      send_email_to_in          => '',
      is_active_in              => 'Y');
The type of trigger code is "RETURNTRUE" which means that when a PL/SQL expression returns TRUE, the alert is triggered. In this case, the trigger code is, well, a little bit trivial: TRUE.

I tell the utility to check once an hour but to not re-trigger the alert more than once per day. And if (WHEN!) the trigger code evaluates to TRUE, execute a PL/SQL procedure, which in this case simply writes a row out to the alert_log table.

About those frequencies: for my basic utility I implement only the following frequencies:
  • H = Hourly
  • D = Daily
  • NNN = the number of minutes between checks.
So if you want the alert to be checked every two hours, pass 120 for check_frequency_in.

You might want to make things more flexible by using DBMS_SCHEDULER syntax.

Here's a more elaborate alert, with two actions to be taken if triggered:
   l_actions   alert_mgr.actions_t;
   l_actions (1).action_type := 'PROC';
   l_actions (1).action_code := 'alert_mgr.log(''Action 1'')';
   l_actions (2).action_type := 'EMAIL';
   l_actions (2).send_email_to := '';
   l_actions (2).email_subject := 'Subject is action 2';
   l_actions (2).email_body := '<p>The body of the email</p>';

   alert_mgr.register_alert (
      alert_name_in             => 'Two actions',
      description_in            => 'Use the array approach',
      trigger_code_in           => 'select count(*) from dual',
      trigger_type_in           => 'ONEROW',
      check_frequency_in        => 'H',
      recurrence_frequency_in   => 'H',
      ignore_failures_in        => 'Y',
      actions_in                => l_actions,
      is_active_in              => 'Y');
In this case, the type of trigger code is "ONEROW" or "Execute alert actions if the query in trigger_code returns just one row."

You can also specify a query to go with your email, which I demonstrate below. The query returns two items: subject and body. These will then be used to send an email with that associated subject and body to each email address provided in the send_email_to field.
   l_index integer := 1;
   l_actions   alert_mgr.actions_t;
   DELETE FROM earthquake_alerts;
   l_index := 1;
   l_actions (l_index).action_type := 'EMAIL';
   l_actions (l_index).send_email_to := 
   l_actions (l_index).email_query := q'[
   SELECT 'Magnitude ' || magnitude || 
             ' Earthquake detected in ' || location subject,
          'Earthquake ID ' || id body
     FROM earthquakes 
    WHERE magnitude >= 6.5
      AND id NOT IN (SELECT earthquake_id FROM earthquake_alerts)]';
   /* Record as notified */
   l_index := 2;
   l_actions (l_index).action_type := 'PROC';
   l_actions (l_index).action_code := 'earthquake_mgr.record_notifications;';
   /* Record in log */
   l_index := 3;
   l_actions (l_index).action_type := 'PROC';
   l_actions (l_index).action_code := 
         ''Earthquakes checked at '' || 
         TO_CHAR (SYSDATE, ''YYYY-MM-DD HH24:MI:SS''))';
   alert_mgr.register_alert (
      alert_name_in             => 'Notify at 6.5',
      description_in            =>    
        'Send an email for earthquakes rated at 6.5 or higher. '
     || 'Don''t send a notification twice for the same earthquake.',
      trigger_code_in           => 
         'SELECT 1 FROM earthquakes 
           WHERE magnitude >= 6.5
             AND id NOT IN (
               SELECT earthquake_id FROM earthquake_alerts)',
      trigger_type_in           => 
      check_frequency_in        => '1',
      recurrence_frequency_in   => '1',
      ignore_failures_in        => 'Y',
      actions_in => l_actions,
      is_active_in              => 'Y');  

Sending Mail

The main alerting package does not contain any email-related code. The way you send emails may vary greatly, so we made all of that code dynamic. If you want to send emails, you need to register the email procedure with the utility. I do this below, using the email package that comes with the utility (and relies on APEX_MAIL):
   /* Set up email using APEX and the default API from the utility */
   alert_mgr.register_email_proc (
      proc_name_in       => 'alert_email_mgr.send_email',
      workspace_name_in  => 'ALERTS');
In other words, if you plan to use APEX_MAIL to send emails with the alerting utility, then execute the above block with the name of your workspace, and you will be all set.

Note: I do not push emails directly onto the queue in the package body, so you may want to change that after installation.

Installing the App Alert Utility

Initially, I will make the code for App Alerts available on LiveSQL. It will also soon be available on Github.

After you click on the link to the script, log in to run the script, and then click on the download icon:

Problems? Suggestions?

If you run into any problems using this code or if you have suggestions for improving it, please leave a comment on this blog post, DM me on Twitter or send me an email.


Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.
How to Get a Mutating Table ErrorI need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today:
In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer

Quick Answer
Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption.Test to see if that's fast enough (likely will be for many cases).If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory. Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification.Don't put anything in the collection you don't need. [from Giulio Dottorini]Remember: each session that runs this code will use that amount of memory.Background

When you use BULK COLLECT, you retrieve more than row with each fetch, reducing context switchi…

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post.

PL/SQL is a strongly-typed language. Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type).

PL/SQL offers a wide array of pre-defined data types, both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package).

Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types.

You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective…