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

<kal121_at_my-deja.com> skrev i en
nyhedsmeddelelse:8e56uc$6dt$1_at_nnrp1.deja.com...
>Has anyone had success implementing alerts?
Yes.

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

Probably a bad idea. If you have a lot of writes to the error table, you might experience severe locking contention problems, particularly in a parallel server environment. This is due to the primitive way alerts are implemented (single schema sys table)

[...]

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

You can set the timeout quite high as far as I remember. Ther wait/waitany returns as soon as the alert has been signalled AND COMMITTED by the signalling transaction. To listen continuously, you have to do it in a loop.

[...]

>(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.
The latter case IMO... :-(

A thought - why not simply poll your error table? Another idea would be to use advanced queueing...

Cheers,
Johan Received on Wed Apr 26 2000 - 00:00:00 CDT