Re: Detecting events in oracle. Transmitting them to java client

From: Jim Kennedy <kennedy-family_at_attbi.com>
Date: Sat, 26 Oct 2002 21:09:22 GMT
Message-ID: <6wDu9.88985$Hj7.46329@rwcrnsc53>

You can leave the clients coded in Java. Nothing wrong with Java. If this is some event in that occurs in the database then a trigger to notify the clients is the best way. (writing the trigger has nothing to do with what language the clients are written in. Again Java is fine for the clients.) How that trigger does the notification gives you a bunch of different choices. I am going to go with a scalable and somewhat simple (not the simplest) method.

In Oracle you can have the concept of an alert. (see documentation on the dbms_alert package) When something (eg the database via a trigger or another client) raises an alert then others (eg clients) all get notified that about that particular alert. So:

1. Alerts have names.
2. You assign the name of the alert.
3. You can have multiple alerts.
4. Multiple clients can register interest in an alert.(They must specify

which alerts they are interested in. They can be interested in more than one.)
5. Clients that have registered interest in an alert will be notified when that alert is raised.
6. The alert can send a message when it is raised and this message will go to each client when the alert is raised or triggered.

(http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.92 0/a96612/d_alert.htm#1001154)
(DBMS_ALERT supports asynchronous notification of database events (alerts). By appropriate use of this package and database triggers, an application can notify itself whenever values of interest in the database are changed.

For example, suppose a graphics tool is displaying a graph of some data from a database table. The graphics tool can, after reading and graphing the data, wait on a database alert (WAITONE) covering the data just read. The tool automatically wakes up when the data is changed by any other user. All that is required is that a trigger be placed on the database table, which performs a signal (SIGNAL) whenever the trigger is fired.

Alerts are transaction-based. This means that the waiting session is not alerted until the transaction signalling the alert commits. There can be any number of concurrent signalers of a given alert, and there can be any number of concurrent waiters on a given alert.)

So in your Java clients I would spawn a thread that would do the alert registration and waiting for the alert. Once an alert is raised it could tell its parent and the parent could do whatever you wanted it to do.

Here is an example of a trigger (from the docs) CREATE TRIGGER emptrig AFTER INSERT OR UPDATE OR DELETE ON emp

    BEGIN
      DBMS_ALERT.SIGNAL('emp_table_alert', 'message_text');    END;
They have an alert called emp_table_alert and the text of the message is message_text.
The clients have a thread that does
DBMS_ALERT.REGISTER('emp_table_alert');
then
 DBMS_ALERT.WAITONE('emp_table_alert', :message, :status);

      if status = 0 then goto <<readagain>>; else

The client piece is in not Java code, but you could translate it into Java code. The alert method is much better than polling the database. Jim

"Simon Harvey" <ReplyToGroup_at_thanks.com> wrote in message news:hcBu9.273$jp2.41140_at_wards...
> Hi all,
>
> I was hoping that someone might be able to give me a bit of advice about
how
> to achieve something in oracle.
>
> I have a number of clients that need to be notified of key events
happening
> with the database. In other words, I need a server event handler to pus
info
> to clients rather than have the clients constantly polling for information
> that might not be there.
>
> My question is what are my options as to the:
>
> Detection of events by the server: My immediate thought was that i should
> learn plsql and how to make some triggers. Someone suggested rather
> cryptically that i have another choice.? Does anyone know the best way?
>
> Tranmission of events to the clients: The clients will be written in java
> because thats the language that I'm most familiar with. Does anyone have
> suggestionsas to how oracle could communicate with a simple lightweight
java
> client? I'm looking into sockets just now and i can "pull" info by using
> jdbc, but that sort of client pull situation is exactly what i am trying
to
> avoid.
>
>
> Any advice at all on this would be gratefully received. Thanks in advance
> for sharing!!
>
> Take care
>
> Simon
>
>
Received on Sat Oct 26 2002 - 16:09:22 CDT