半岛外围网上直营

    OracleAlerter类是用于DBMS_ALERT包的接口，仅仅在专业版和开发版中有。DBMS_ALERT包支持异步通知数据库事件（警报），通过恰当地使用这个包和数据库触发器，一个应用程序可以通知任何其他的应用程序，连接数据库、登记接受到的报警以及数据库的任何的改变等。

     今天主要的内容就是详解OracleAlerter组件的使用，主要是由以下的部分组成：Oracle警报基础、等待警报模式、开始模式。

Oracle警报基础

    DBMS_ALERT提供API接口来发送警报、警报注册、并等待接收警报。这个解决方案主要采用了信号、寄存器和WAITANY。

• dbms_alert.signal程序发出警报信号，当提交事务的时候，这个警报就会提交生效。如果说事务被返回，这个信号就没有起作用。也会提醒所有的感兴趣的会议通知。
• DBMS_ALERT.REGISTER程序将会允许在警报中注册。
• The DBMS_ALERT.WAITANY程序将会等待任何的会话前发生的警报。

    OracleAlerter 类主要原则如下图所示：

    OracleAlerter类支持两种工作模式，等待警报和开始模式。“等待警报”模式将会等待最近的警报，并将其返回给应用程序。“开始”模式就是启动一次，当接收到一个警报的时候就会提出一个事件，为了制止这种模式，叫做OracleAlerter 类实例的“停止”方法。

等待模式

     下面的示例就是演示的是OracleAlerter在等待模式的情况：

[C#]

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

开始模式

    在这个示例中讲会使用警报检索模式中的开始模式，OracleAlerter对象alerter将被初始化，并被设置为特定警报侦听。然后OracleAlerter类的另一个实例alertGenerator，将会发送带有名字的警报到服务器上，从而触发警报的事件。等待时间到期后，将会提出WaitTimeout事件，监听重新开始。然后WaitTimeout对象停下来显示停止事件。

[C#]

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