Database Events

Overview

Oracle database server supports detection and run-time publication of database events.

The database event publication feature allows applications to subscribe to database events just as they subscribe to messages from other applications.

Users can enable the publication of the following events.

· DML events (DELETE, INSERT, UPDATE)

· DDL Events (CREATE, ALTER, DROP)

· Database events (SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN)

The database event publication subsystem tightly integrates with the AQ publish/subscribe engine. For a complete description of triggers for data and system events, see Oracle9i SQL Reference.

Oracle Objects for OLE provides functionality to enable COM users to subscribe to Oracle Database events.

This feature supports asynchronous notification of database events to interested subscribers. Under this model, the client can subscribe to be notified of a database or system event, with each such request stored as a subscription. When the database event of interest fires, the subscriber is notified via the database event handler that was registered at the time of subscribing to the event.

OO4O provides the OraSubscription object which represents the subscription to a database event and OraSubscriptions collection which maintains a list of OraSubscription objects.

To subscribe to a dbevent, you must:

· create a subscription, based on the database event of interest

· provide a dbevent handler. The dbevent handler should be an automation object that implements the method NotifyDBEvents. The NotifyDBEvents method is invoked by OO4O when the subscribed database events are fired.

· register the subscription, using the Register method

For more detailed information about Oracle Database Events, refer to the section on Triggers on System Events and User Events in Oracle9i Database Concepts.

Example: Registering an application for notification of database events

In the following example, an application subscribes for notification of database logon events (such as, all logons to the database). When a user logons to the database, the NotifyDBEvents method of the DBEventsHdlr that was passed in at the time of subscription, is invoked. The context-senstive information and the event-specific information is passed into NotifyDBEvents.

The DBEventsHdlr in this example is DBEventCls, which is defined later.

Example :

In the main application:

' First instantiate the dbevent handler. The dbevent notification

' will fire the NotifyDBEvents on the callback handler.

Public DBEventsHdlr As New DBEventCls

Private Sub Form_Load()

Dim gOraSession As Object

Dim gOraSubscriptions As OraSubscriptions

Dim gOraDatabase As OraDatabase

'Create the OraSession Object

Set gOraSession = CreateObject("OracleInProcServer.XOraSession")

'Create the OraDatabase Object by opening a connection to Oracle.

Set gOraDatabase = gOraSession.DbOpenDatabase
("ora90.us.oracle.com", "pubsub/pubsub",
ORADB_ENLIST_FOR_CALLBACK)

Set gOraSubscriptions = gOraDatabase.Subscriptions

gOraSubscriptions.Add "PUBSUB.LOGON:ADMIN", DBEventsHdlr,
gOraDatabase

gOraSubscriptions(0).Register

MsgBox "OK"

End Sub

The database event handler class that defines the NotifyDBEvents method.

Ctx represents the application-defined context sensitive object that is passed in when the subscription is created.

Payload is the message that is retrieved when the database event fires.

Public countofMsgs as integer

Public Function NotifyDBEvents(Ctx As Variant, Payload As Variant )

On error goto NotifyMeErr

MsgBox "Retrieved payload " + Payload

' do something - here the subscription is unregistered after
' receiving 3 notifications

countofMsgs = countofMsgs + 1

If countofMsgs > 3 Then

Ctx.Subscriptions(0).UnRegister

End If

Exit Sub

NotifyMeErr:

Call RaiseError(MyUnhandledError, "newcallback:NotifyMe Method")

End Sub