Direct，DOA4.0.7.1，TOracleEvent.Stop挂起，如何阻止它？

Question

跟进(2014年4月9日)：在用DOA 4.1.1取代DOA 4.0.7.1之后，我不能重复这个问题。我的问题被否决了！然而，我仍然对在生产软件中实现TOracleEvent的任何人的反馈感兴趣。

原题：

如何使用DOA4.0.7在Delphi6中可靠地实现TOracleEvent for DBMS_ALERTs？

或

如何在不侵入DOA源代码的情况下绕过TOracleEvent.Stop方法锁定？

如果可能的话，我更喜欢修复或者不需要修改原始AllRoundAutomations DOA源代码的工作。

我正在使用传统的Delphi6和AllRoundAutomations DirectOracleAccessDOA4.0.7。我碰到了一个显示停止，其中DOA TOracleEvent实例无法停止和释放，因此应用程序的进程不能自行终止。

我从DOA文档中了解到，我应该调用myOracleEvent.Stop()来清理TOracleEvent实例。但是，如果我执行Stop()方法，我的应用程序将在该方法调用时“挂起”。如果我不调用Stop()，我的应用程序就会关闭，但是它的进程将无限期地保持活跃，直到我用taskmanager或其他方法杀死它为止。

TOracleEvent可以管理两种类型的信号: dbms_alert和管道。我正在使用dbms_alert信号来启用多个接收器。

对这个问题的搜索发现，在AllRoundAutomations论坛上，其他一些人也经历过这个问题，但我没有找到任何答案。

我不认为这是Oracle服务器端的问题，因为应用程序的TOracleEvent进程导致了阻塞。也就是说，TOracleEvent实例不是“等待”任何东西，它只是挂起。如果我杀死“挂起”的Oracle会话服务器端，则不会发布它。当然，问题可能是我的程序没有正确配置TOracleEvent属性，也没有正确地清理它以供处理。

下面是用于测试用例状态机的Delphi-6代码，它隔离了DOA.TOracleEvent.Stop()方法冻结的问题。如果TOracleEvent.Stop()是固定的，那么状态机应该显示类似于下面代码后面的结果。如果不是固定的，Stop()方法将在调用时冻结。在没有修复(黑客) DOA源代码的情况下，我无法让任何小型测试应用程序可靠地工作。

implementation
{$R *.dfm}

const
  ALERT_NAME__STACKOVERFLOW = 'STACKOVERFLOW';

/// This is the TOracleEvent.OnEvent handler that listens for DBMS_ALERT Signals.
procedure TForm1.OracleEvent1Event(Sender: TOracleEvent; const ObjectName: String; const Info: Variant);
var
  ii: integer;
begin
  Memo1.Lines.Add('*> Oracle Event Received!');
  Memo1.Lines.Add('   DBMS_ALERT.SIGNAL name = ' + ObjectName);
  if VarIsArray(Info) then begin
    for ii := 0 to VarArrayHighBound(Info, 1) do begin
      Memo1.Lines.Add('     Message= ' + Info[ii]);
    end;
  end;
  Memo1.Refresh();
end;

procedure TForm1.btnRunStateMachineClick(Sender: TObject);
begin
  // Button clicked to clear the display and start the state machine timer.
  AppState := 0;
  Memo1.Lines.Clear();
  Timer1.Enabled := true;
end;

procedure TForm1.Timer1Timer(Sender: TObject);
begin
  // Time Interval State Machine that steps through the test sequence. 

  // Stop the Timer for Failsafe
  Timer1.Enabled := false;

  if (AppState = 0) then begin
      try
        Memo1.Lines.Add('==========================');
        Memo1.Lines.Add('Connecting OracleSession2 to DB ...');
        // Check for, Close, and Destroy previous Sessions
        if (Assigned(OracleSession2) and OracleSession2.Connected) then begin
          // Close the Connection
          OracleSession2.LogOff();
          OracleSession2.Connected := false;
          OracleSession2.Free();
          OracleSession2 := nil;
        end;
        if (not Assigned(OracleSession2)) then begin
          OracleSession2 := TOracleSession.Create(self);
        end;
        // Configure Session #2 for general purpose routines
        OracleSession2.LogonDatabase := 'STACKOVERFLOW_DB';
        OracleSession2.LogonPassword := 'drowssap';
        OracleSession2.LogonUserName := 'answer';
        OracleSession2.Pooling := spInternal;
        OracleSession2.ThreadSafe := true;
        OracleSession2.Connected := true;
        Memo1.Lines[Memo1.Lines.Count - 1] := 'DB OracleSession2 Connected!';
        Inc(AppState);
      except
        on ex0: Exception do begin
          AppState := -1;
          Memo1.Lines.Add('!! ERROR !! ' + ex0.Message);
        end;
      end;
    end

  else if (AppState = 1) then begin
      try
        Memo1.Lines.Add('Connecting OracleSession1 to DB ...');
        if (Assigned(OracleSession1) and OracleSession1.Connected) then begin
          OracleSession1.LogOff();
          OracleSession1.Connected := false;
          OracleSession1.Free();
          OracleSession1 := nil;
        end;
        if (not Assigned(OracleSession1)) then begin
          OracleSession1 := TOracleSession.Create(self);
        end;

        // Configure Session #1 for the OracleEvent Handler
        OracleSession1.LogonDatabase := 'STACKOVERFLOW_DB';
        OracleSession1.LogonPassword := 'drowssap';
        OracleSession1.LogonUserName := 'answer';
        OracleSession1.Pooling := spInternal;
        OracleSession1.ThreadSafe := true;
        OracleSession1.Connected := true;
        Memo1.Lines[Memo1.Lines.Count - 1] := 'DB OracleSession1 Connected!';
        Inc(AppState);
      except
        on ex1: Exception do begin
          AppState := -1;
          Memo1.Lines.Add('!! ERROR !! ' + ex1.Message);
        end;
      end;
    end

  else if (AppState = 2) then begin
      try
        Memo1.Lines.Add('Configuring the Oracle Event Handler ...');
        if (not OracleEvent1.Started) then begin
          if (OracleEvent1.Session = nil) then begin
            OracleEvent1.Session := OracleSession1;
          end;
          OracleEvent1.KeepConnection := false;
          OracleEvent1.Synchronized := true;
          OracleEvent1.TimeOut := 1;
          OracleEvent1.ObjectNames := ALERT_NAME__STACKOVERFLOW;
          OracleEvent1.Start();
          Memo1.Lines[Memo1.Lines.Count -1] := 'Oracle Event Handler Started!';
        end;
        Inc(AppState);
      except
        on ex2: Exception do begin
          AppState := -1;
          Memo1.Lines.Add('!! ERROR !! ' + ex2.Message);
        end;
      end;
    end

  else if ((AppState >= 3) and (AppState <= 7)) then begin
      try
        Memo1.Lines.Add('Sending a DBMS_ALERT Signal to StackOverflow ...');
        try
          OracleSession1.DBMS_Alert.Signal(ALERT_NAME__STACKOVERFLOW, 'Hello StackOverflow! ' + FormatDateTime('HH:nn:ss', now));
          OracleSession1.Commit();
        except
          on ex: Exception do begin
            Memo1.Lines.Add('*> Oracle Event Signal ERRORT!');
            Memo1.Lines.Add(ex.Message);
          end;
        end;
        Inc(AppState);
      except
        on ex3: Exception do begin
          AppState := -1;
          Memo1.Lines.Add('!! ERROR !! ' + ex3.Message);
        end;
      end;
    end

  else if (AppState = 8) then begin
      try
        Memo1.Lines.Add('Disconnecting OracleSession2 from DB ...');
        if (Assigned(OracleSession2) and OracleSession2.Connected) then begin
          OracleSession2.LogOff();
          OracleSession2.Connected := false;
          OracleSession2.Free();
          OracleSession2 := nil;
        end;
        Memo1.Lines[Memo1.Lines.Count -1] := 'DB OracleSession2 Disconnected!';
        Inc(AppState);
      except
        on ex4: Exception do begin
          AppState := -1;
          Memo1.Lines.Add('!! ERROR !! ' + ex4.Message);
        end;
      end;
    end

  else if (AppState = 9) then begin
      try
        Memo1.Lines.Add('Stopping OracleEvent handler.');
        Memo1.Lines.Add(' * THIS IS WHERE THE HANGUP PROBLEM IS CALLED!');
        OracleEvent1.Stop();  // <<<< Freezes here if TOracleEvent.Stop() is not fixed!
        ////
        Memo1.Lines.Add('OracleEvent Handler Stopped OK!');
        Memo1.Lines.Add(' * If we got here, either there were no events...');
        Memo1.Lines.Add('   or the Bug is Fixed B-)');
        Inc(AppState);
      except
        on ex5: Exception do begin
          AppState := -1;
          Memo1.Lines.Add('!! ERROR !! ' + ex5.Message);
        end;
      end;
    end

  else if (AppState = 10) then begin
      try
        Memo1.Lines.Add('Disconnecting OracleSession1 from DB ...');
        if (Assigned(OracleSession1) and OracleSession1.Connected) then begin
          OracleSession1.LogOff();
          OracleSession1.Connected := false;
          OracleSession1.Free();
          OracleSession1 := nil;
        end;
        Memo1.Lines[Memo1.Lines.Count -1] := 'DB OracleSession1 Disconnected!';
        Inc(AppState);
      except
        on ex6: Exception do begin
          AppState := -1;
          Memo1.Lines.Add('!! ERROR !! ' + ex6.Message);
        end;
      end;
    end

  else begin
    AppState := 0;
  end;

  Memo1.Refresh();
  Timer1.Enabled := (AppState >= 0);
end;

procedure TForm1.btnStopClick(Sender: TObject);
begin
  // Stop Button Clicked to halt the State Machine
  Timer1.Enabled := false;
  Memo1.Lines.Add('State Machine Stopped.');
  Memo1.Refresh();
end;

/// Other handlers on the TOracleEvent instance
procedure TForm1.OracleEvent1Start(Sender: TOracleEvent);
begin
  Memo1.Lines.Add('*> Oracle Event START!');
end;

procedure TForm1.OracleEvent1Stop(Sender: TOracleEvent);
begin
  Memo1.Lines.Add('*> Oracle Event STOP!');
end;

procedure TForm1.OracleEvent1Error(Sender: TOracleEvent; const Error: Exception);
begin
  Memo1.Lines.Add('*> Oracle Event ERROR!');
  Memo1.Lines.Add('=> ' + Error.Message);
end;

procedure TForm1.OracleEvent1TimeOut(Sender: TOracleEvent; var Continue: Boolean);
begin
  Memo1.Lines.Add('*> Oracle Event TIMEOUT!');
end;

以下是状态机的输出：

==========================
The following are the results for successfully running 
the state machine with a DOA.TOracleEvent.Stop() "fixed".
Without fixing DOA.TOracleEvent.Stop(), the state machine will "freeze"
==========================

DB OracleSession2 Connected!
DB OracleSession1 Connected!
Configuring the Oracle Event Handler ...
Oracle Event Handler Started!
Sending a DBMS_ALERT Signal to StackOverflow ...
*> Oracle Event Received!
   DBMS_ALERT.SIGNAL name = STACKOVERFLOW
     Message= Hello StackOverflow! 21:55:41
*> Oracle Event TIMEOUT!
Sending a DBMS_ALERT Signal to StackOverflow ...
*> Oracle Event Received!
   DBMS_ALERT.SIGNAL name = STACKOVERFLOW
     Message= Hello StackOverflow! 21:55:42
*> Oracle Event TIMEOUT!
Sending a DBMS_ALERT Signal to StackOverflow ...
*> Oracle Event Received!
   DBMS_ALERT.SIGNAL name = STACKOVERFLOW
     Message= Hello StackOverflow! 21:55:43
*> Oracle Event TIMEOUT!
Sending a DBMS_ALERT Signal to StackOverflow ...
*> Oracle Event Received!
   DBMS_ALERT.SIGNAL name = STACKOVERFLOW
     Message= Hello StackOverflow! 21:55:44
*> Oracle Event TIMEOUT!
Sending a DBMS_ALERT Signal to StackOverflow ...
*> Oracle Event Received!
   DBMS_ALERT.SIGNAL name = STACKOVERFLOW
     Message= Hello StackOverflow! 21:55:45
DB OracleSession2 Disconnected!
*> Oracle Event TIMEOUT!
Stopping OracleEvent handler.
 * This is where the Hangup problem gets called!
*> Oracle Event STOP!
OracleEvent Handler Stopped OK!
 * If we got here, either there were no events...
   or the Bug is Fixed B-)
DB OracleSession1 Disconnected!
==========================

Answer 1

下面是DOA4.1.1源代码中TOracleEvent.Stop例程的修订。此例程中未更改的大容量DOA代码被替换为‘。。‘

这个修改的过程是(到目前为止) TOracleEvent.Stop()调用的一个功能工作，它间歇性地导致我的应用程序挂起。

procedure TOracleEvent.Stop;
var
  bLoggedOff: boolean;
begin
  . . .
  . . .
  // Skip the CriticalSection Enter/Leave toggle.
  // This is causing my application to get stuck here!
  //CriticalSection.Enter;
  //CriticalSection.Leave;
  . . .
  . . .
  // LogOff the duplicate sessions
  if (not KeepConnection) then begin
    bLoggedOff := false;
    // Keep trying the logoff until we actually get Logged Off or disconnected.
    while (not bLoggedOff) do begin
      try
        if ((InternalSession <> nil) and (InternalSession.Connected)) then begin
          InternalSession.LogOff;
        end;
        if ((StopSession <> nil) and (StopSession.Connected)) then begin
          StopSession.LogOff;
        end;
        bLoggedOff := true;
      except
        on exLogoff: Exception do begin
          // This error is typically ORA-24909: Call In Progress, Current Operation Cancelled.
          // Sink this hangover error by sleeping it off
          Sleep(1000);
        end;
      end;
    end;
  end;
end;