simple pager for Apex 3.0

Although i can claim that in the last 25 years i never got a phone call in the middle of the night for job reasons, i like to know as soon as possible when a major problem has occurred, especially if the error condition prevents users from doing their work.

However, if the application users are anonymous visitors of a web site, it can be difficult, if not impossible, to be contacted timely in case of problems that may arise for a variety of reason, including bugs at different levels, service outages, networking problems, unexpected usage patterns and so on.

If you are an Oracle Application Express developer or administrator you know that in the Monitor Activity section there is a useful built-in report displaying a list of unhandled exceptions that have occurred in the specified time frame, set by default to 1 day.

This report covers errors in the Apex domain, so we won't find any entries if the http server is down, that is when a user got a HTTP-404 error, but we will find entries if an Apex user got a broken report caused by an illegal parameter value, when an Apex process failed and the error was not trapped inside the program, including many other database related issues like a constraint violation or an exceeded tablespace quota that prevented the successful termination of a DML statement, always speaking in the context of Apex applications.

Clearly this approach implies an active participation on our side, because we must explicitly go to the Monitor Activity Page and run the report to know the information, and we know how easy is to forget a task when we have a busy agenda.

What I'm going to suggest here is a more passive approach that lets the computer do the work for us, that is in the end why these damned machines were invented in the first place.

The idea is to have a scheduled job that collects the information and sends us simple reports in form of emails at regular intervals.

Moreover, if you are using a mobile service provider integrating email and SMS, you can easily set up an automated paging system as i did.

And now for the real stuff.

As of Oracle Application Express version 3.0, we have finally all the required bits to carry out this task using supported objects, therefore we no longer need to have special privileges granted for accessing internal objects, in other words the most typical and least appreciated phrase in IT's history: "with the next software release you'll able to do that" has been fulfilled eventually.

Keeping in mind that that Oracle Application Express can still run on top of Oracle 9.2, where DBMS_SCHEDULER is not available, but DBMS_JOB is, i wrote a simple package that allows you to monitor the situation at regular intervals.

Of course the usual disclaimers apply, if you run a nuclear plant don't look at me if anything goes wrong with this program.

Note also that if you (or your DBA) revoked the execution privilege on DBMS_JOB package from PUBLIC (or from the schema user you are trying to compile the package into), the package compilation will fail.
If DBMS_JOB is disabled, that is parameter JOB_QUEUE_PROCESSES is set to zero (0), the pager will not be started, informing you via DBMS_OUTPUT that background jobs are disabled.

The procedures communicate messages via DBMS_OUTPUT and, hopefully, are self-explanatory.

I strongly recommend to perform these tasks from within the Apex environment, but you can do the same from sqlplus too, remembering to COMMIT whenever necessary.

The installation is straightforward, just download the SIMPLE_PAGER for Oracle Application Express 3.0 package and compile it in you schema.

Usage notes are provided in the package specification, however, once the packages have been compiled in the schema, the pager can be started within the next minute as follows:

begin
simple_pager.start_pager(
email       => '...',         -- comma separated list of recipients
email_from  => '...',         -- email of the sender
interval    => 4              -- hours between each alert
);
end;

For instance if you prefer to receive the notifications periodically, on the hour, every 2 hours, starting from 12:00:00 (supposing now it's some time between 00:00 and 11:59:59), then you can specify the optional parameter next_alert, as follows:

begin
simple_pager.start_pager(
email       => '...',                    -- comma separated list of recipients
email_from  => '...',                    -- email of sender
interval    => 2,                        -- hours between each alert
next_alert  => trunc(sysdate) + 1/2      -- start at noon
);
end;

The pager can run without problems in a hosted environment, where each different workspace/user can run its own pager without interfering with others.

Hope it helps.

See more articles about Oracle Application Express or download tools and utilities.