DBMS_ALERT - is it what it's cracked up to be?

Has anyone had success implementing alerts?

I have created a table that will have oracle errors written to it by an application. I created a trigger that will signal an alert when this error table is inserted.

Problem is, I'm not sure how to monitor for these alerts. If I open another SQL*Plus session, I run a procedure that waits and listens for alerts. Problem is, it seems you have to specify a length of time for it to listen to alerts. (the timeout)

I certainly don't know *when* or even if the alerts will be triggered, so how can I know beforehand what to set the time to? Besides, it doesn't print the alerts to the screen until AFTER the specified time period is elapsed. How useless is this? If I set the time period to one hour, the procedure will listen for alerts for one hour only. I need it to listen continuously.

Another way I've seen is to wait for a specified number of alerts to occur, then after that threshold is reached, you can print the alerts to the screen. This method assumes you can wait that long to see your alerts. Not very helpful when you want to see your alerts immediately when they happen, not after "x" alerts occur.

Also, I'd like for the alerts to email me - that would be useful. I suppose this would involve DBMS_JOB as well?

Does anyone have some sample code that would demonstrate this?

(I've read up on ALERTS in two books so far, but the examples are limited to what I've described above) Either I am missing something critical about how alerts work or else alerts aren't very useful for what I'm trying to do. I hope it is the former case.

Thanks

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Apr 25 2000 - 00:00:00 CDT