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;
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;
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;
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:
Comments
Post a Comment