chap12_3.html

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

<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.3 How do I匔ommunicate between sessions with DBMS_PIPE?</font></b>

<p>

<B>Problem</B>

<p>

I need a method to allow Oracle processes to communicate. I want to be able to send a message and have the other process read it when it抯 ready. How do I communicate between Oracle sessions?

<p>

<B>Technique</B>

<p>

The DBMS_PIPE built-in package allows communication between processes using named pipes. One or more sessions connected to the same database instance can write to and read from a pipe asynchronously. Table 12.5 shows the functions and procedures in the DBMS_PIPE package.

<p>

Table 12.5 Functions/procedures in the DBMS_PIPE package

<p>

<TABLE bgcolor="cdcdcd" cellpadding=3 cellspacing=3>

<td>Module</td>
<td>Type</td>
<td>Description</td>

<TR><td>CREATE_PIPE</td>
<td>Function</td>
<td>Explicitly creates a new pipe.</td></TR>

<TR><td>PACK_MESSAGE</td>
<td>Procedure</td>
<td>Adds an item to the message buffer.</td></TR>

<TR><td>SEND_MESSAGE</td>
<td>Function</td>
<td>Sends the contents of the message buffer to a pipe.</td></TR>

<TR><td>RECEIVE_MESSAGE</td>
<td>Function</td>
<td>Receives a message from the pipe into the message buffer.</td></TR>

<TR><td>NEXT_ITEM_TYPE</td>
<td>Function</td>
<td>Returns the datatype of the next item in the buffer.</td></TR>

<TR><td>UNPACK_MESSAGE</td>
<td>Procedure</td>
<td>Reads the next item from the message buffer.</td></TR>

<TR><td>REMOVE_PIPE</td>
<td>Function</td>
<td>Removes a pipe.</td></TR>

<TR><td>PURGE</td>
<td>Procedure</td>
<td>Purges contents of a pipe.</td></TR>

<TR><td>RESET_BUFFER</td>
<td>Procedure</td>
<td>Clears the message buffer.</td></TR>

<TR><td>UNIQUE_SESSION_NAME</td>
<td>Function</td>
<td>Returns a unique session name.</td></TR>

</TABLE>

<p>

In PL/SQL version 2.2 and higher, a pipe can be public or private. A public pipe can be accessed by all users and does not need to be created explicitly. However, you can create a public pipe explicitly using the CREATE_PIPE function by setting the private_flag parameter to FALSE. A private pipe must be created explicitly using the CREATE_PIPE function. Access to a private pipe is restricted to sessions connected with the same user account as the pipe creator, or a user connected as SYSDBA or INTERNAL. An explicitly created public or private pipe should be removed with the REMOVE_PIPE function, or else the pipe remains in memory until the database instance is shutdown. The CREATE_PIPE and REMOVE_PIPE procedures are supported in PL/SQL 2.2 and higher. The first time a pipe is used with the SEND_MESSAGE or RECEIVE_MESSAGE functions, it will be created automatically as a public pipe. To send a message through the pipe, add data items to the local message buffer using the PACK_MESSAGE procedure and then send the message to the pipe using the SEND_MESSAGE function. To read a message from a pipe, use the RECEIVE_MESSAGE procedure to receive the message and then the UNPACK_MESSAGE procedure to read each message item.

<p>

<B>Steps</B>

<p>

<B>1.</B>	Run SQL*Plus and connect as the WAITE user account. Run the CHP12_9.SQL file, as shown in <A HREF="image/12oht09.gif" TARGET="_blank">Figure 12.9</A>. The statement in the file packs a message and puts it into a pipe. A new public pipe is implicitly created as it is referenced for the first time.

<p>

The variable declared in line 2 receives the value returned by the SEND_MESSAGE function. Line 4 uses the PACK_MESSAGE procedure to pack the message passed as a parameter, preparing it for delivery. Line 5 uses the SEND_MESSAGE function to send the message to the specified pipe, which is created implicitly. If the value returned by SEND_MESSAGE is not 0, an error occurred and the corresponding status code is displayed; otherwise, a happy message is written to the screen.
<p>
<B>2.</B>	Run SQL*Plus, creating a second session to the database with the WAITE user account. Load CHP12_10.SQL into the SQL buffer. The PL/SQL code within the file reads the message from the pipe created in the prior step and displays it onscreen. The results of the operation are shown in <A HREF="image/12oht10.gif" TARGET="_blank">Figure 12.10</A>.
<p>
Line 2 declares a variable containing the status of the pipe when the RECEIVE_MESSAGE function is executed. Line 3 declares the variable receiving the message from the pipe. Line 5 executes the RECEIVE_MESSAGE function to receive the next message from the specified pipe. If the value returned is 0, the pipe contains a message. Line 7 uses the UNPACK_MESSAGE procedure to retrieve the message from the buffer and put it in a local variable. Line 8 displays the message using the PUT_LINE procedure of the DBMS_OUTPUT package. If a message was not successfully received from the pipe, lines 10 and 11 generate a message onscreen. The message sent to the pipe by the first process is read, unpacked, and displayed by the second process.
<p>
<B>3.</B>	Run CHP12_11.SQL in SQL*Plus, as shown in <A HREF="image/12oht11.gif" TARGET="_blank">Figure 12.11</A>. The PL/SQL block in the file explicitly creates a new private pipe. This Step primarily demonstrates the method used to create a private pipe. If you want to simply use a public pipe created implicitly by the system, jump to Step 5.
<p>
Line 2 declares a variable to handle the results of the CREATE_PIPE function. Line 3 declares an exception variable for handling the error that occurs when the pipe already exists. Line 4 declares a PRAGMA to link the exceptions declared in line 3 with the Oracle error occurring when the pipe exists. Line 6 executes the CREATE_PIPE function to create a private pipe with the name WAITE_PIPE. Line 7 sets the PRIVATE_FLAG to TRUE in order to create a private pipe.
<p>
No two pipes in the database can have the same name. An ORA-23322 error will occur if another user has created a private pipe (or a public pipe with pending messages) and has the same name specified in the CREATE_PIPE function. If an ORA-23322 is raised, the exception handler in lines 12 and 13 displays a message. As the WAITE_PIPE pipe does not contain any pending messages in the pipe after Step 2, the public pipe is automatically removed and re-created as a private pipe.
<p>
<B>4.</B>	Because the pipe was created explicitly in Step 3, it has to be removed using the REMOVE_PIPE function.
<p>
<code><b>
SQL> VARIABLE STATUS NUMBER
<p>
SQL> EXEC :STATUS := DBMS_PIPE.REMOVE_PIPE(慦AITE_PIPE