receiving events or DBMS_OUTPUT

A

andrisi

Member
Is there a way for a passively waiting client appliaction to receive some kind of notification that is triggered in the databas using a trigger, stored procedure, dbms_output.put_line, or anything suitable.

A want to avoid 100-200 client apps connectiig to one database coninously polling for changes. Insted I'd like to add a trigger that sends an event to all instances of the application connected.

I imagine this woult be an event on my TOracleSession just like OnClick on a button.

Any help would be apreciated. The event waiting mechanism is not accaptable (an oracle feature) because I must do other queries while wainting for this thing, and than it wouyld require a new session just for this.
 
A solution I just deployed this past weekend did the following:

I created an application that listened on a socket. This application put it's own name and port number into a table in Oracle. In a "before insert" trigger on a certain table, I used the utl_tcp package to open a socket connection to each row in the IP table. If the connection failed, I delete the row from the IP table. If connected, I send the new ID of the inserted record so the applications can do any processing they need to because of the insert.

Be aware that the utl_tcp package requires the JServer and PL/SQL J packages to be installed (javavm\install.sql and rdbms\admin\initplsj.sql) to function properly. Make sure you allocate about 8 to 12 MB of memory for the java server process in the init.ora file before you run the scripts as they will fail without enough memory.
 
A solution we use for this is DBMS_ALERT. The alert is fired from inside database triggers and the alert contains info about the record.

DOA provides a handy component for waiting for alerts using a separate thread (TOracleEvent).

One excellent benefit of this approach is that the alerts only get sent when the transaction is commited, this means that clients don't go looking for changes in the database when they haven't been commited yet. Another benefit is that because it runs over Net8 you don't need to reconfigure routers, etc to let custom ports through.
 
You must log in or register to reply here.
Back
Top