This article will go step by step through a method to use Oracle
8i Release 2's UTL_SMTP package, DBMS_JOB package, and a simple subscription
model to send emails to those people subscribed to specific events that
may occur in the database.
Remember how complicated it used to be to send emails to people from an Oracle database? It used to require programming in C (or other third party language), knowing about daemons, using dbms_pipe, etc. These overly complicated techniques disheartened many developers from adding to their application this fairly trivial and essential functionality. And when they did build it, maintenance was an issue as most code developed in our industry is poorly documented and people change jobs frequently.
Why should users check periodically with the system for given events? Let us take the example of the CDC (Centers for Disease Control) organization in the USA. Should people working for the CDC check periodically their multi-hospital database for specific symptom groups that indicate the presence of dangerous diseases? Should people working for the CDC have to run a number of reports daily for that purpose? Of course not. The system should be able to alert them when certain events happen in the system. By enabling people to work by exception a lot of organizations could reduce operation costs.
It is now very easy to send pages to people. You can even send text
messages on average cell phones. No need for special web enabled ones though
it does not hurt. In this article we will show you how to do all that and
more.
Step 1: The Email Package
With the email package documented in listings A and B, sending an email to a pager only takes one command:
mail.send (
p_sender=> 'noReply@nomail.com',
p_recipient=>
'joe.smith@myPager.com',
p_subject=> 'System
Alert',
p_message=> v_alert_message
);
g_smtp_server varchar2(2000);
g_smtp_server_port pls_integer;
function get_sys_parameter (p_name in varchar2) return varchar2;
procedure send (
p_sender
in varchar2,
p_recipient in
varchar2,
p_message
in varchar2,
p_subject
in varchar2
);
end; -- Package Specification mail
Since Oracle 8.1.6, Oracle Corp. kindly provided us with the built-in
package utl_smtp. This is what we use in this article to send an email.
To open a connection with an SMTP server, utl_smtp requires a server name
and a port. These values are stored in global variables g_smtp_server and
g_smtp_server_port that are declared in the header part of our mail package.
These two variables are initialized by the mail package body, as showed
in listing B.
--
-- Purpose: Send email messages
--
-- modification history
-- Person Date
Comments
-- --------- ------ ------------------------------------------
-- hdd 000913
Creation
function get_sys_parameter (p_name in varchar2) return varchar2
is
v_return varchar2(2000);
begin
select distinct value
into v_return
from system_parameters
where name = p_name;
return v_return;
end;
procedure send (
p_sender
in varchar2,
p_recipient in
varchar2,
p_message
in varchar2,
p_subject
in varchar2
)
is
mail_conn utl_smtp.connection;
begin
mail_conn := utl_smtp.open_connection
(g_smtp_server, g_smtp_server_port);
utl_smtp.helo (mail_conn, g_smtp_server);
utl_smtp.mail (mail_conn, p_sender);
utl_smtp.rcpt (mail_conn, p_recipient);
utl_smtp.data (
mail_conn,
'Subject : '||p_subject
||
CHR (13) ||
CHR (10) ||
'To : ' ||
p_recipient ||
CHR (13) ||
CHR (10) ||
p_message
);
utl_smtp.quit (mail_conn);
end send;
--
-- Mail package init
--
begin
g_smtp_server := get_sys_parameter ('smtp_server');
if g_smtp_server is null then
raise_application_error(-20031,
'Could not find system parameter smtp_server in table system_parameters.');
end if;
g_smtp_server_port := cast(get_sys_parameter('smtp_server_port')
as number);
if g_smtp_server_port is null then
raise_application_error(-20031,
'Could not find system parameter smtp_server_port in table system_parameters.');
end if;
end; -- Package body mail
We chose to store the SMTP server name and port in a table called
system_parameters (Listing C) in order to make our mail package more easily
portable from our development environment to the client's production environment.
We grouped all system parameters into one table so that there is only one
place to go at install time.
SQL> desc system_parameters
Name
Null? Type
-----------------------------------------
-------- ----------------
ID
NOT NULL NUMBER(11)
NAME
NOT NULL VARCHAR2(40)
VALUE
NOT NULL VARCHAR2(2000)
COMMENTS
VARCHAR2(4000)
CREATE_USER_ID
VARCHAR2(10)
CREATE_DATE
DATE
LAST_CHANGE_USER_ID
VARCHAR2(10)
LAST_CHANGE_DATE
DATE
UPDATE_STAMP
NUMBER(5)
SQL> select name, value
2 from
system_parameters;
NAME
VALUE
------------------------------
------------------------------
smtp_server
smtp05.us.oracle.com
smtp_server_port
25
Step 2: The Alert table
All that is required to send an email to somebody is to call the mail package documented above. Such a call can be put in a number of database triggers in a number of tables. Such a call could also be placed in the code of a number of application modules.
In our environment we wanted to follow a structured and encapsulated method to manage alerts. Many people may be subscribed to receive pages for many types of alert. It was necessary to divorce the process of sending batches of alert pages from the database transactions that trigger these pages. The triggering transaction commit process should not have to wait synchronously for the page sender process to complete.
So we first created a database table called 'alerts' to keep a log of
all alerts generated by the system. This table is shown in listing D. Note
that this table does not keep track of who the alert was sent to. There
is only one record per event that triggered an alert.
SQL> desc alerts
Name
Null? Type
-----------------------------------------
-------- -----------------
ID
NOT NULL NUMBER(11)
ALERT_MESSAGE
VARCHAR2(2000)
ALERT_TS_START
DATE
ALERT_TS_END
DATE
CREATE_USER_ID
VARCHAR2(10)
CREATE_DATE
DATE
LAST_CHANGE_USER_ID
VARCHAR2(10)
LAST_CHANGE_DATE
DATE
UPDATE_STAMP
NUMBER(5)
CLE_ID
NOT NULL NUMBER(11)
Columns alert_ts_start and alert_ts_end are used to specify a datetime
range when the alert should be sent. Under certain conditions we may choose
to use this to delay the time when people should be paged or to make the
alert expire very quickly. We could even document an alert in the past
without having it ever sent by the system.
The column CLE_ID is a foreign key to a generic list-of-value application
table called code_list_values. CLE_ID establishes the type of alert message.
Examples of types are 'syndrome' and 'new incident'. Users can subscribe
to any or several types of alerts. For example, Sylvia may only need to
be paged when a 'syndrome' alert is produced by the system. Nasir may need
to be paged whenever either a 'syndrome' or a 'new incident' alert is produced
by the system.
Step 3: Building the Alert package
The second step to build the alert mechanism is to group as much of
the functionality in our package. The only part of alerts that we could
not include in this package is the event specific functionality, like the
format of the actual message.
procedure prepare (p_message in varchar2 default 'No
message supplied',
p_alert_type in varchar2 default 'SYNDROME',
p_ts_start in date default sysdate,
p_ts_end in date default sysdate+20);
procedure send; -- Internal only, do not use. (had to be made public though...)
end; -- Package Specification alert
We felt it necessary to divorce the process of sending batches of
alert pages from the database transactions that trigger these pages. The
triggering transaction commit process should not have to wait synchronously
for the page sender process to complete. Only procedure 'prepare' should
be used by database triggers or application modules that send alerts. The
procedure 'prepare' submits a queued job using dbms_job that runs procedure
'send' later in a separate transaction. So there is no need to call procedure
'send'.
Programming style: for the sake of brevity we have not included
the exception handling code in any of the code that follows. For example,
we do not show how we handle more elegantly a situation where the alert
type 'SYNDROME' has not been loaded in the database like it should be during
the install.
procedure prepare (p_message in varchar2 default 'No
message supplied',
p_alert_type in varchar2 default 'SYNDROME',
p_ts_start in date default sysdate,
p_ts_end in date default sysdate+20) is
v_cle_id
number;
v_jobno
binary_integer;
begin
select id
into v_cle_id
from code_list_values
where value = p_alert_type;
insert into alerts (alert_message,
alert_ts_start, alert_ts_end, cle_id) values
(p_message, p_ts_start, p_ts_end, v_cle_id);
-- submit a job to send the alerts
dbms_job.submit(v_jobno, 'alert.send;',
sysdate+1/24/60/2);
end; -- procedure prepare
procedure send is
cursor alert_pages is
select email_address,
alert_message,
alt.id alt_id
from persons
psn,
alerts alt,
person_paging_subscriptions pps
where alt.cle_id
= pps.cle_id and -- same alert_type
sysdate >= alert_ts_start and
sysdate <= alert_ts_end and
pps.psn_id = psn.id and
psn.email_address is not null;
begin
for cur_alert in alert_pages loop
mail.send (
p_sender=> 'herve.deschamps@oracle.com',
p_recipient=> cur_alert.email_address,
p_subject=> 'x',
p_message=> cur_alert.alert_message
);
if v_old_id !=
0 and v_old_id != cur_alert.alt_id then -- update same alert only once
update alerts
set alert_ts_end = sysdate
where id=v_old_id;
end if;
v_old_id := cur_alert.alt_id;
end loop;
commit;
end; -- procedure send
end; -- package body alert
Procedure 'prepare' has defaults for all its parameters, though
in practice the first 2 parameters will be used by most of the programs
calling it. In contrast, the default value of the two timestamp parameters
is almost never overwritten.
All procedure 'prepare' does is to insert a record in table 'alerts' defined in the previous step and to submit a job to dbms_job that will run procedure 'alert.send' in 30 seconds from the present time.
Procedure 'send' scans the 'alerts' table for active records (i.e. not
expired by the value of alert_ts_end) and sends emails to all the people
who are subscribed the that type of alert (CLE_ID column defined in step
2). Once the alerts are sent their end timestamps are set to the current
time so that they are never sent again.
Step 4: Using the Alert package
When a new incident is recorded in table 'incidents' the system must
send an alert to all people who are subscribed to the 'new incident' alert
type. We do this with a database pre-insert trigger on table 'incidents'
as shown in listing G.
alert.prepare (p_message => 'LEADER ALERT: New incident:
'||v_incident_name||' was initiated. Location: '||
v_location_name||' at '||to_char(sysdate,'mm/dd/rrrr hh24:mi') ||'.',
p_alert_type => 'NEW INCIDENT');
end;
All that is left to do is insert a record in table 'incidents' and
a message will be sent to all people subscribed to the alert type 'new
incident'.