My solution to notifying when a table has changed.

From: <roeber_at_vxcrna.cern.ch>
Date: 30 Mar 92 19:09:03 GMT
Message-ID: <1992Mar30.200903.1_at_vxcrna.cern.ch>


Awhile ago I asked about how to arrange for Oracle to notify a user program when the contents of a table were changed. This functionality is supposedly in Version Seven (RSN), but I wondered if it was possible in version 6.

I received a couple of responses, but none of the ideas really provided what we need. So I solved it in a slightly different way, which I'll summarize below for anyone else interested in this.

I wrote a server which we will run "in parallel" to the oracle kernel. It will log into the database as the dba; and then sit on a socket and wait for clients. Clients connect and send messages like
"INTERESTED object_name UPDATE"; the server will acknowledge, turn on
auditing on the object/action, and start periodically polling the audit trail ("SELECT COUNT(*) etc"). When the audit count changes, a message is sent over the socket to the client. When the last client interested in something says it is no longer interested, or when it quits or disappears, the auditing is turned off and the audit records are cleaned out.

Unfortunately, I haven't eradicated the polling altogether, which is what I had hoped to accomplish when I first asked. But at least it is only one program polling, and this program is known and controllable. It was written to be on the same machine as the Oracle kernel, so these polls are not going across the network. We'll see how it goes..

-- 
Frederick G. M. Roeber | CERN -- European Center for Nuclear Research
e-mail: roeber_at_cern.ch or roeber_at_caltech.edu | work: +41 22 767 31 80
r-mail: CERN/PPE, 1211 Geneva 23, Switzerland | home: +33 50 42 19 44
--  

"Sorry, baby, I can't take you to the pizza joint tonight, I've got to go
back to the lab and split the atom." -- Ayn Rand, "What is Romanticism?"
Received on Mon Mar 30 1992 - 21:09:03 CEST

Original text of this message