Using OracleAlerter Component

In This Topic

OracleAlerter class is an interface for the DBMS_ALERT package. It is available in Professional and Developer Editions only.

The DBMS_ALERT package supports asynchronous notification of database events (alerts). By appropriate use of this package and database triggers, an application can notify any other applications, connected to this database and registered for alerts receiving, about any changes in the database. You can read more about this package here.
This topic provides information and examples that can help you start with Oracle alerts. It consists of the following sections:

Oracle Alert basics
"Wait alert" mode
"Start" mode

Oracle Alert Basics

DBMS_ALERT provides APIs to send alerts, register for alerts, and wait to receive them. The procedures this Solution uses are SIGNAL, REGISTER, and WAITANY:

The DBMS_ALERT.SIGNAL procedure signals an alert. The SIGNAL call goes into effect only when the transaction in which it is made commits. If the transaction rolls back, SIGNAL has no effect. All sessions that have registered interest in this alert are notified. (If the application does not require transaction-based alerts, the DBMS_PIPE package may provide a useful alternative).
The DBMS_ALERT.REGISTER procedure lets a session register interest in an alert.
The DBMS_ALERT.WAITANY procedure waits for any of the alerts for which the current session is registered to occur.

The main principle of OracleAlerter class is shown on the figure below:

The OracleAlerter class supports two modes of work: "WaitAlert" and "Start". The "WaitAlert" mode waits for the nearest alert and returns it to the application. The "Start" launches once and raizes an event each time when alert is received. To stop this mode, call the "Stop" method of the OracleAlerter class instance.

"WaitAlert" mode

The following sample demonstrates the work with OracleAlerter in "WaitAlert" mode.

A simple table and a trigger are created in the database. The trigger generates an Oracle Alert each time when row is added to the table. Then, the OracleAlerter instance is initialized and set up to wait for the alert mentioned. While OracleAlerter is waiting, in another thread a row is inserted to the table, thus triggering the alert. Then the WaitAlert method returns this alert as an OracleAlert object.

static OracleConnection con = new OracleConnection();

static void Main(string[] args)
{   

  // Initialize and open a connection to the Oracle server. 
  // We connect as Sys to have the privilieges to use the DBMS_Alert package.

  con.Server = "ora";
  con.UserId = "sys";
  con.Password = "pwd";
  con.ConnectMode = OracleConnectMode.SysDba;
  con.Open();

  // Execute a script needed to create the database objects used in our sample.
  // These objects are:
  // 1) table "alert_table" with two fields: an integer identification and a char value;
  // 2) trigger "alert_trigger", which initializes the "my_alert" Oracle Alert after each insert to alert_table.

  OracleScript createAll = new OracleScript();
  createAll.Connection = con;
  createAll.ScriptText = @"
  create table scott.alert_table 
  (""id"" number(38,0), 
   ""value"" varchar2(4000 byte)
  );
	
  create or replace trigger sys.alert_trigger 
    after insert or update on scott.alert_table 
    for each row 
    begin
      dbms_alert.signal('my_alert', 'A row has been added.');
    end;
  ";            

  createAll.Execute();
	
  // Now we create an instance of the OracleAlerter class, which is used to retrieve alerts.
  // This instance is registered for the "my_alert" Oracle Alert.            
  // Timeout stands for the time in seconds during which OracleAlerter will be waiting for alerts.

  OracleAlerter alerter = new OracleAlerter();
  alerter.Connection = con;
  alerter.AlertName = "my_alert";
  alerter.Timeout = 10;
	
  // When waiting for alerts, OracleAlerter expectedly pauses the current thread. 
  // Thus, we need another one to generate the alert while OracleAlerter is listening.
  // In the Insert() function, a row is added to alert_table. 
  // As it is shown in the createAll script, this insert triggers the "my_alert" Oracle Alert.

  Thread insertThread = new Thread(new ThreadStart(Insert));
  insertThread.Start();            

  // Waits until the "my_alert" alert is received, returns the corresponding OracleAlert object. 
  // If it is not during the timeout period, returns null.

  OracleAlert alert = alerter.WaitAlert();
	
  // Simple output operations to show the alert's content.
  Console.WriteLine("Got an alert: " + ((alert == null) ? "null" : alert.Message));
  Console.Read();
	
  // Drop table and trigger.
  OracleScript dropAll = new OracleScript();
  dropAll.Connection = con;
  dropAll.ScriptText = @"
  drop trigger sys.alert_trigger;
  drop table scott.alert_table;
  ";
  dropAll.Execute();
	
  // Close the connection.
  con.Close();
}
	
// A simple insert command used to trigger the "my_alert" alert.
// We take this command out to use multithreading.

public static void Insert()
{               
   OracleCommand insert = new OracleCommand();
   insert.CommandText = "insert into scott.alert_table values ('10', 'Some text')";
   insert.Connection = con;
   insert.ExecuteNonQuery();
	
   Console.WriteLine("Inserted a row");
}

Visual Basic


Private Shared con As New OracleConnection

Shared Sub Main(ByVal args As String())

    ' Initialize and open a connection to the Oracle server. 
    ' We connect as Sys to have the privilieges to use the DBMS_Alert package.
    con.Server = "ora"
    con.UserId = "sys"
    con.Password = "pwd"
    con.ConnectMode = OracleConnectMode.SysDba
    con.Open
    
    ' Execute a script needed to create the database objects used in our sample.
    ' These objects are:
    ' 1) table "alert_table" with two fields: an integer identification and a char value;
    ' 2) trigger "alert_trigger", which initializes the "my_alert" Oracle Alert after each insert to alert_table.
    
    Dim createAll As New OracleScript
    createAll.Connection = con
    createAll.ScriptText = VbCrlf _
	& "        " _
	 & "create table scott.alert_table " & VbCrlf _
	 & "        (""id"" number(38,0), " & VbCrlf _
	& "   ""value"" varchar2(4000 byte)" & VbCrlf & _
	"                );" & VbCrlf  & VbCrlf _
	& "                create or replace trigger sys.alert_trigger " & VbCrlf & _
	"                    after insert or update on scott.alert_table " & VbCrlf & _
	"                    for each row " & VbCrlf & _
	"                    begin" & VbCrlf _
	& "                        dbms_alert.signal('my_alert', 'A row has been added.');" _
	& VbCrlf & "                    end;" & VbCrlf & "        " 
    createAll.Execute
    
    ' Now we create an instance of the OracleAlerter class, which is used to retrieve alerts.
    ' This instance is registered for the "my_alert" Oracle Alert.            
    ' Timeout stands for the time in seconds during which OracleAlerter will be waiting for alerts.
    
    Dim alerter As New OracleAlerter
    alerter.Connection = con
    alerter.AlertName = "my_alert"
    alerter.Timeout = 10
    
    ' When waiting for alerts, OracleAlerter expectedly pauses the current thread. 
    ' Thus, we need another one to generate the alert while OracleAlerter is listening.
    ' In the Insert() function, a row is added to alert_table. 
    ' As it is shown in the createAll script, this insert triggers the "my_alert" Oracle Alert.
    Dim insertThread As Thread = New Thread(New ThreadStart(insert))
    insertThread.Start()    
    
    ' Waits until the "my_alert" alert is received, returns the corresponding OracleAlert object. 
    ' If it is not during the timeout period, returns null.
    Dim alert As OracleAlert = alerter.WaitAlert
    
    ' Simple output operations to show the alert's content.
    Console.WriteLine(("Got an alert: " & IIf((alert Is Nothing), "null", alert.Message)))
    Console.Read
    
    ' Drop table and trigger.
    Dim dropAll As New OracleScript
    dropAll.Connection = con
    dropAll.ScriptText = VbCrlf & _
    "        "  & "drop trigger sys.alert_trigger;" & VbCrlf & _
    "        "  & "drop table scott.alert_table;" & VbCrlf  
    dropAll.Execute
    
    ' Close the connection.
    con.Close
End Sub

' A simple insert command used to trigger the "my_alert" alert.
' We take this command out to use multithreading.
Public Shared Sub Insert()
    Dim createAll As New OracleCommand
    createAll.CommandText = "insert into scott.alert_table values ('10', 'Some text')"
    createAll.Connection = con
    createAll.ExecuteNonQuery
    Console.WriteLine("Inserted a row")
End Sub

"Start" mode

In this sample, we use the "Start" mode of alerts retrieving. The OracleAlerter object "alerter" is initialized and set up to listen for the specific alert. Then, another instance of the OracleAlerter class, "alertGenerator", sends alerts with the same name to the server, thus firing the Alert events. After the waiting time expires, the WaitTimeout event is raised and listening starts again. Then the alerter object is stopped to show the Stopped event.

static void Main(string[] args)
{
    // Initialize and open a connection to the Oracle server. 
    // We connect as Sys to have the privilieges to use the DBMS_Alert package.
    OracleConnection con = new OracleConnection();
    con.Server = "ora";
    con.UserId = "sys";
    con.Password = "pwd";
    con.ConnectMode = OracleConnectMode.SysDba;
    con.Open();

    // Create the OracleAlerter instance and register it for the "my_alert" Oracle Alert.
    // Set Interval to 0 so that there is no delay between two consequent periods of listening.
    OracleAlerter alerter = new OracleAlerter();
    alerter.Connection = con;
    alerter.AlertName = "my_alert";
    alerter.Timeout = 3;
    alerter.Interval = 0;
            
    // Set the event handlers for all possible OracleAlerter events.
    // The Alert event fires when an alert is received.
    // The Error event fires as any error occurs while receiving alerts.
    // The Stopped event fires when alerter becomes inactive, e.g. after the Stop() method.
    // The WaitTimeout event fires when the Timeout period ends without getting an alert.
    alerter.Alert += new OracleAlerterAlertEventHandler(Alerter_OnAlert);
    alerter.Error += new OracleAlerterErrorEventHandler(Alerter_OnError);
    alerter.Stopped += new OracleAlerterFinishEventHandler(Alerter_OnStop);
    alerter.WaitTimeout += new OracleAlerterFinishEventHandler(Alerter_OnTimeOut);
	
    // Start the alerter. It will wait for alerts during the Timeout period.
    // After that, it sleeps during Interval and then starts again.
    // As Interval is zero, there will be no sleeping periods.
    // Unlike the WaitAlert method, Start() does not hold the current thread.
    alerter.Start();
	
    // We need to wait until the alerter begins listening. 
    // Otherwise, the alert may fire before OracleAlerter is initialized.
    // In this case, the Alert event won't be triggered and alerter will just wait 
    // until Timeout, producing the WaitTimeout event.
    Thread.Sleep(2000);
	
    // In this sample, we use another instance of OracleAlerter instead of database triggers to generate the alert.
    // alertGenerator uses the same connection and alert name as the alerter object.
    OracleAlerter alertGenerator = new OracleAlerter();
    alertGenerator.Connection = con;
    alertGenerator.AlertName = "my_alert";

    // Send an alert to the server. At this moment alerter should raise the Alert event.
    alertGenerator.Signal("An alert message.");

    // In contrast to WaitAlert(), the Start() method allows to receive alerts continuously.
    // Thus, we can process all alerts that are available on the server.
    alertGenerator.Signal("One more alert");
	
    // After the alert is received, alerter starts another Timeout period.
    // At its end, the WaitTimeout event will be generated. We pause the thread to get this event.
    // Besides, we need a small pause to let the last alert be sent to the server.
    Thread.Sleep(5000);
	
    // Disable alerter, raising the Stopped event. 
    alerter.Stop();
	
    Console.Read();
	
    // Close the connection.
    con.Close();            
}

// Simple event handlers for alerter's events.
public static void Alerter_OnAlert(object sender, OracleAlerterAlertEventArgs e)
{
	Console.WriteLine("Got an alert: " + e.AlertMessage);
}
public static void Alerter_OnError(object sender, OracleAlerterErrorEventArgs e)
{
	Console.WriteLine("Error: " + e.AlerterException.Message);            
}
public static void Alerter_OnStop(object sender, OracleAlerterFinishEventArgs e)
{
	Console.WriteLine("Stopped: " + e.ToString());
}
public static void Alerter_OnTimeOut(object sender, OracleAlerterFinishEventArgs e)
{
	Console.WriteLine("Time's up: " + e.ToString());
}

Visual Basic


Shared Sub Main(ByVal args As String())

    ' Initialize and open a connection to the Oracle server. 
    ' We connect as Sys to have the privilieges to use the DBMS_Alert package.
    Dim con As New OracleConnection
    con.Server = "ora"
    con.UserId = "sys"
    con.Password = "pwd"
    con.ConnectMode = OracleConnectMode.SysDba
    con.Open
    
    ' Create the OracleAlerter instance and register it for the "my_alert" Oracle Alert.
    ' Set Interval to 0 so that there is no delay between two consequent periods of listening.
    Dim alerter As New OracleAlerter
    alerter.Connection = con
    alerter.AlertName = "my_alert"
    alerter.Timeout = 3
    alerter.Interval = 0
    
    ' Set the event handlers for all possible OracleAlerter events.
    ' The Alert event fires when an alert is received.
    ' The Error event fires as any error occurs while receiving alerts.
    ' The Stopped event fires when alerter becomes inactive, e.g. after the Stop() method.
    ' The WaitTimeout event fires when the Timeout period ends without getting an alert.
    AddHandler alerter.Alert, New OracleAlerterAlertEventHandler(AddressOf Alerter_OnAlert)
    AddHandler alerter.Error, New OracleAlerterErrorEventHandler(AddressOf Alerter_OnError)
    AddHandler alerter.Stopped, New OracleAlerterFinishEventHandler(AddressOf Alerter_OnStop)
    AddHandler alerter.WaitTimeout, New OracleAlerterFinishEventHandler(AddressOf Alerter_OnTimeOut)
    
    ' Start the alerter. It will wait for alerts during the Timeout period.
    ' After that, it sleeps during Interval and then starts again.
    ' As Interval is zero, there will be no sleeping periods.
    ' Unlike the WaitAlert method, Start() does not hold the current thread.
    alerter.Start
    
    ' We need to wait until the alerter begins listening. 
    ' Otherwise, the alert may fire before OracleAlerter is initialized.
    ' In this case, the Alert event won't be triggered and alerter will just wait 
    ' until Timeout, producing the WaitTimeout event.
    Thread.Sleep(2000)
    
    ' In this sample, we use another instance of OracleAlerter instead of database triggers to generate the alert.
    ' alertGenerator uses the same connection and alert name as the alerter object.
    Dim alertGenerator As New OracleAlerter
    alertGenerator.Connection = con
    alertGenerator.AlertName = "my_alert"
    
    ' Send an alert to the server. At this moment alerter should raise the Alert event.
    alertGenerator.Signal("An alert message.")
    
    ' In contrast to WaitAlert(), the Start() method allows to receive alerts continuously.
    ' Thus, we can process all alerts that are available on the server.
    alertGenerator.Signal("One more alert")
    
    ' After the alert is received, alerter starts another Timeout period.
    ' At its end, the WaitTimeout event will be generated. We pause the thread to get this event.
    ' Besides, we need a small pause to let the last alert be sent to the server.
    Thread.Sleep(5000)
    
    ' Disable alerter, raising the Stopped event. 
    alerter.Stop
    Console.Read
    
    ' Close the connection.
    con.Close
End Sub

Public Shared Sub Alerter_OnAlert(ByVal sender As Object, ByVal e As OracleAlerterAlertEventArgs)
    Console.WriteLine(("Got an alert: " & e.AlertMessage))
End Sub

Public Shared Sub Alerter_OnError(ByVal sender As Object, ByVal e As OracleAlerterErrorEventArgs)
    Console.WriteLine(("Error: " & e.AlerterException.Message))
End Sub

Public Shared Sub Alerter_OnStop(ByVal sender As Object, ByVal e As OracleAlerterFinishEventArgs)
    Console.WriteLine(("Stopped: " & e.ToString))
End Sub

Public Shared Sub Alerter_OnTimeOut(ByVal sender As Object, ByVal e As OracleAlerterFinishEventArgs)
    Console.WriteLine(("Time's up: " & e.ToString))
End Sub

Oracle Alert Basics

"WaitAlert" mode

"Start" mode

See Also