chap12_4.html

来自「Oracle资料大集合」· HTML 代码 · 共 355 行 · 第 1/2 页

<td><a href="http://www.itlibrary.com/reference/dir.networkservices1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Network Services</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=12 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.middleware.html"><font face="verdana,helvetica" size="1" color="000000"><b>Middleware</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=12 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.components.html"><font face="verdana,helvetica" size="1" color="000000"><b>Components</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.operatingsystems.html"><font face="verdana,helvetica" size="1" color="000000"><b>Operating Systems</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.userinterfaces.html"><font face="verdana,helvetica" size="1" color="000000"><b>User Interfaces</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.groupwareandcollaboration1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Groupware &amp; Collaboration</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.contentmanagement.html"><font face="verdana,helvetica" size="1" color="000000"><b>Content Management</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.productivityapplications1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Productivity Applications</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.hardware1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Hardware</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.funandgames1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Fun &amp; Games</b></font></td>
</tr>
</table>


<!-- <IMG SRC="/images/leftnav.gif" WIDTH=111 HEIGHT=404 ALT="nav" border="0"  ISMAP USEMAP="#leftnav"> -->
<p>
<a href="http://www.earthweb.com/jobs/" target=new><IMG SRC="../../../images/dice-105x30.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.earthwebdirect.com/" target=new><IMG SRC="../../../images/earthwebdirect.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.fatbrain.com/home.html?from=UUX592" target=new><IMG SRC="../../../images/fatbrain.gif" WIDTH=105 HEIGHT=30 ALT="Fatbrain" border="0"></a>
<a href="http://auctions.earthweb.com" target=new><IMG SRC="../../../images/auctions_105x30.gif" WIDTH=105 HEIGHT=30 ALT="Auctions" border="0"></a>
<a href="http://www.supportsource.com" target=new><IMG SRC="../../../images/ss2000sneak2.gif" WIDTH=105 HEIGHT=30 ALT="Support Source Answers" border="0"></a>
<P>

<b><font face="verdana,helvetica" size="2">EarthWeb sites</FONT></b><br>
  <FONT SIZE="1">
  <a href="http://www.crossnodes.com"><font face="verdana,helvetica" size="1" color="000000">Crossnodes</font></a><br>
  <a href="http://www.datamation.com"><font face="verdana,helvetica" size="1" color="000000">Datamation</font></a><br>
  <a href="http://www.developer.com"><font face="verdana,helvetica" size="1" color="000000">Developer.com</FONT></A><br>
  <a href="http://www.dice.com"><font face="verdana,helvetica" size="1" color="000000">DICE</font></a><br>
  <a href="http://www.earthweb.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb.com</font></a><br>
  <a href="http://www.earthwebdirect.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb Direct</font></a><br>
  <a href="http://www.erphub.com"><font face="verdana,helvetica" size="1" color="000000">ERP Hub</font></a><br>
  <a href="http://www.gamelan.com"><font face="verdana,helvetica" size="1" color="000000">Gamelan</font></a><br>
  <a href="http://www.gocertify.com"><font face="verdana,helvetica" size="1" color="000000">GoCertify.com</font></a><br>
  <a href="http://www.htmlgoodies.com"><font face="verdana,helvetica" size="1" color="000000">HTMLGoodies</font></a><br>
  <a href="http://www.intranetjournal.com"><font face="verdana,helvetica" size="1" color="000000">Intranet Journal</font></a><br>
  <a href="http://www.itknowledge.com"><font face="verdana,helvetica" size="1" color="000000">IT Knowledge</font></a><br>
  <a href="http://www.itlibrary.com"><font face="verdana,helvetica" size="1" color="000000">IT Library</font></a><br>
  <a href="http://www.javagoodies.com"><font face="verdana,helvetica" size="1" color="000000">JavaGoodies</font></a><br>
  <a href="http://www.jars.com"><font face="verdana,helvetica" size="1" color="000000">JARS</font></a><br>
  <a href="http://www.javascripts.com"><font face="verdana,helvetica" size="1" color="000000">JavaScripts.com</font></a><br>
  <a href="http://www.opensourceit.com"><font face="verdana,helvetica" size="1" color="000000">open source IT</font></a><br>
  <a href="http://www.roadcoders.com"><font face="verdana,helvetica" size="1" color="000000">RoadCoders</font></a><br>
  <a href="http://www.Y2Kinfo.com"><font face="verdana,helvetica" size="1" color="000000">Y2K Info</font></a>
</FONT>

</td>



<!--End Left Navigation column -->
<!-- spacer column  -->

<TD width="40">
<img src="../../../images/dotclear.gif" WIDTH="40" HEIGHT="1" border=0>
</TD>

<!--Begin Content Column -->

<TD VALIGN="TOP" width="500">

<P>




<blockquote>
<p>
<b><font size="+1">12.4 How do I匨onitor the database asynchronously with DBMS_ALERT?</font></b>
<p>
<B>Problem</B>
<p>
I want my application to be alerted when certain events take place in the database. I do not want to continuously query an object to determine if the event has occurred; I want to be notified by the database. How do I execute an action based on an event in the database?
<p>
<B>Technique</B>
<p>
The DBMS_ALERT package is used to alert a session when an event occurs in the database. The receiving session registers for notifications from an alert, and the alert signals the session when the event occurs. Table 12.7 shows the procedures in the DBMS_ALERT package.
<p>
Table 12.7 The DBMS_ALERT package
<p>
<table bgcolor="cdcdcd" cellpadding="3" cellspacing="3"> 
<TR><td>Name</td>
<td>Type</td>
<td>Description</td></TR>

<TR><td>REGISTER</td>
<td>Procedure</td>
<td>Registers the session to receive messages from an alert.</td></TR>

<TR><td>WAITONE</td>
<td>Procedure</td>
<td>Waits for a signal from a specific alert.</td></TR>

<TR><td>WAITANY</td>
<td>Procedure</td>
<td>Waits for any of the alerts registered for the session.</td></TR>

<TR><td>SIGNAL</td>
<td>Procedure</td>
<td>Signals an alert causing messages to be sent to registered sessions.</td></TR>

<TR><td>SET_DEFAULTS</td>
<td>Procedure</td>
<td>Sets the polling interval.</td></TR>

<TR><td>REMOVE</td>
<td>Procedure</td>
<td>Removes the session from receiving notifications from an alert.</td></TR>

<TR><td>REMOVEALL</td>
<td>Procedure</td>
<td>Removes the session from all alert registrations.</td></TR>
</table>
<p>

The REGISTER procedure is used by applications to register interest in an alert. The name of the alert is passed as an IN parameter. A session may register interest in more than one alerts.

<P>

Once an alert has been registered, the WAITONE procedure can be used to wait for a signal from a specific alert or the WAITANY procedure can be used to wait for a signal from any of the alerts for which the session is registered.
<p>
The SIGNAL procedure notifies all sessions that have registered on the alert and optionally sends a message to each session. A COMMIT statement must be issued after calling the SIGNAL procedure for the call to the SIGNAL procedure to become effective.
<p>
If the transaction rolls back, the SIGNAL call has no effect. If the alerted session has blocked in a WAITONE/WAITANY call, the call returns and execution resumes with the next statement. If the alert was signaled before issuing a call to the WAITONE/WAITANY procedure, the call returns immediately. Multiple sessions can concurrently signal on the same alert. If the SIGNAL procedure was called more than once for an alert before the receiving end issued a WAITONE/WAITANY call, then the message received will be from the last SIGNAL call.
<p>
A polling loop is not required in most cases, except by the WAITANY call when a SIGNAL call remains uncommitted longer than a second, and to check for alerts from another instance when the database is running in parallel mode. The polling loop interval defaults to one second but can be changed by calling the SET_DEFAULTS procedure.
<p>
<B>Steps</B>
<p>
<B>1.</B>	Run SQL*Plus and connect as the WAITE user account. This How-To requires two connections to the same database. This can be performed with two SQL*Plus sessions in a 32-bit environment, such as Windows NT or Windows 95, or can be performed using two computers connected to the same database. The first connection will register its interest in an alert and wait for the alert to fire. Run the CHP12_14.SQL file in SQL*Plus. <A HREF="image/12oht15.gif" TARGET="_blank">Figure 12.15</A> shows the operation in SQL*Plus before the alert is fired.
<p>
Set the SERVEROUTPUT system variable to ON to display the output using the DBMS_OUTPUT.PUT_LINE procedure. The STATUS variable declared in line 2 returns the status of the WAITONE procedure call to the module. The MESSAGE variable declared in line 3 returns the message sent by the session signaling the alert. Line 5 executes the REGISTER procedure to register interest in the WAITE_ALERT alert. Line 6 executes the WAITONE procedure, making the module wait for the alert to fire.
<p>
The first IN parameter is the alert name on which the procedure is waiting. The second parameter is an OUT parameter returning the message sent when the alert is signaled. The third parameter is also an OUT parameter returning the status of the procedure call. A value of 0 identifies that an alert was fired; a value of 1 means that the timeout occurred. The last IN parameter is the timeout in seconds.
<p>
If timeout is not specified, it defaults to DBMS_ALERT.MAXWAIT, which is 1,000 days. Line 7 checks the status variable and displays the timeout message if it occurred or the alert message if fired. When the PL/SQL block is executed, it hangs until another session fires the alert.
<p>
<B>2.</B>	Establish another SQL*Plus session to the same database and connect as the WAITE user account. This session will be used to fire alerts that are received by the first session. The SIGNAL procedure in the DBMS_ALERT package is used to fire an alert.
<p>
<code><b>
SQL> EXEC DBMS_ALERT.SIGNAL(慦AITE_ALERT