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)
)

Alerts

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.
CREATE TABLE alerts (
   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',
                        'NOROWS',
                        'MULTROWS',
                        'RETURNTRUE',
                        'RETURNFALSE')),
   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
        REFERENCES alerts ON DELETE CASCADE,
   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',
                       'SMS',
                       'PROC',
                       'REST'))
)

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
         REFERENCES alerts ON DELETE CASCADE,
   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.
BEGIN
   alert_mgr.start_check_alerts_job (every_n_minutes_in => 1);
END;

Register alerts

Now I will register my first - and very basic - alert.
BEGIN
   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');
END;
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:
DECLARE
   l_actions   alert_mgr.actions_t;
BEGIN
   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 := 'steven.feuerstein@oracle.com';
   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');
END;
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.
DECLARE
   l_index integer := 1;
   l_actions   alert_mgr.actions_t;
BEGIN
   DELETE FROM earthquake_alerts;
   
   l_index := 1;
   l_actions (l_index).action_type := 'EMAIL';
   l_actions (l_index).send_email_to := 
      'steven.feuerstein@oracle.com';
   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 := 
      'alert_mgr.log(
         ''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           => 
         alert_mgr.c_trigger_type_select_mult_rows,
      check_frequency_in        => '1',
      recurrence_frequency_in   => '1',
      ignore_failures_in        => 'Y',
      actions_in => l_actions,
      is_active_in              => 'Y');  
END;

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):
BEGIN
   /* 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');
END;
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.

Comments

Popular posts from this blog

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 p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel