Tuesday, December 12, 2006

Do-it-yourself Database Change Notification

I am currently involved in a project which needs some database change notification mechanism. The environment is Oracle 9i + .NET 1.1. Although ADO.NET has this feature, but it's not available in .NET 1.1 and ODP.NET won't support it until version 10.2. So under the current environment, I have to rely on other solutions. Belows are several apporaches I found and I list here for my reference.
  1. Use UTL_TCP. With this package, we can write PL/SQL code to communicate with programs out of Oracle database. We write a PL/SQL client using UTL_TCP to send notifications and a socket server, dedicated to listening to the events sent out by the PL/SQL client. If the frontend is an ASP.NET program, UTL_HTTP can be considered as well.
  2. Use DBMS_PIPE. This is a package, similar to pipes in Unix, which allow programs to exchange messages. To send and receive notifications, we will write two PL/SQL programs, Publisher and Receiver, using DBMS_PIPE. Change events are sent by calling Publisher and events are received by calling Receiver through ADO.NET. Events are delivered via the pipes Oracle provides.
  3. Use Advanced Queuing. This is Oracle's implementation of message queue. I haven't tried it yet, and will post some experience after I have time to give it a try.

Update: If more than 2 sessions need to exchange messages, for DBMS_PIPE to work well, a mechanism is needed to locate the pipes where each communicating session is using. A table might be needed to store the pipe name for each session. It seems DBMS_PIPE is not suitable for a publish/subscribe scenario.

No comments: