Monday, May 28, 2007

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:

email => '...', -- comma separated list of recipients
email_from => '...', -- email of the sender
interval => 4 -- hours between each alert
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:
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

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.


Patrick Wolf said...

Very useful job to utilize the data which is stored in the APEX dictionary views!


Byte64 said...

Hello Patrick,
indeed i think it is ;-)
It helped me to discover that for some unknown reason there is a public page of an application that occasionally returns an ORA-00060 Deadlock error and being so lazy, i'd never know about it because i am not used to look at that error report at all.


Anthony Rayner said...


Is this package still available for download as I would very much like to make use of it. I tried the download link and it gave me the following error:

Errore ERR-1412 Impossibile risolvere l'alias di pagina (apex_resources).


Byte64 said...

Hello Anthony,
thanks for reporting this broken link, i must be definitely reviewing old postings from time to time!

I fixed the link, so you should be able to access the page of resources on the site now.


yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles