Paging People with Oracle 8i out of the Box

By Hervé Deschamps, Oracle Corporation.

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

Listing A

  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

mail Package header

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.
 

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

mail Package body

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.
 
 

Listing C

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

System_parameters table description and content.

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.
 

Listing D

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)

Alerts table description and content.

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.
 

Listing E

   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

alert Package header

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.
 

Listing F

   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

alert Package body

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.
 

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;

pre-insert trigger on table 'incidents' to kick off an alert.

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'.