12.3 How do I…Communicate between sessions with DBMS_PIPE?
Problem
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’s ready. How do I communicate between Oracle sessions?
Technique
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.
Table 12.5 Functions/procedures in the DBMS_PIPE package
Module Type Description CREATE_PIPE Function Explicitly creates a new pipe. PACK_MESSAGE Procedure Adds an item to the message buffer. SEND_MESSAGE Function Sends the contents of the message buffer to a pipe. RECEIVE_MESSAGE Function Receives a message from the pipe into the message buffer. NEXT_ITEM_TYPE Function Returns the datatype of the next item in the buffer. UNPACK_MESSAGE Procedure Reads the next item from the message buffer. REMOVE_PIPE Function Removes a pipe. PURGE Procedure Purges contents of a pipe. RESET_BUFFER Procedure Clears the message buffer. UNIQUE_SESSION_NAME Function Returns a unique session name. 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.
Steps
1. Run SQL*Plus and connect as the WAITE user account. Run the CHP12_9.SQL file, as shown in Figure 12.9. 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.
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.
2. 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 Figure 12.10.
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.
3. Run CHP12_11.SQL in SQL*Plus, as shown in Figure 12.11. 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.
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.
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.
4. Because the pipe was created explicitly in Step 3, it has to be removed using the REMOVE_PIPE function.
SQL> VARIABLE STATUS NUMBER
SQL> EXEC :STATUS := DBMS_PIPE.REMOVE_PIPE(‘WAITE_PIPE’)
PL/SQL procedure successfully completed.
SQL> PRINT STATUS
X
---------
0
At the SQL*Plus prompt, a bind variable is created using the VARIABLE command. The REMOVE_PIPE function in the DBMS_PIPE package is executed to remove the pipe specified. The bind variable receives the value returned by the REMOVE_PIPE function. The function returns 0 if the pipe is removed successfully; otherwise, an error occurred. The PRINT command is issued to check the value of the bind variable. To clear the contents of a pipe instead of removing it, use the PURGE procedure.
5. The example presented in this step and the next requires two sessions connected 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 using two computers connected to the same database. Run SQL*Plus and connect as the WAITE user account, and run the CHP12_12.SQL file in this first session. The PL/SQL code in the file uses a loop to read messages from a public pipe. The module appears to hang until the first message is sent to the pipe. Figure 12.12 shows SQL*Plus waiting for the first message.
Line 2 declares the variable used to receive the status from the RECEIVE_MESSAGE function. Line 3 declares the variable that receives each item of the message from the buffer. The variable in line 4 holds the value of the total number of items to be read from the message. Line 6 uses the RECEIVE_MESSAGE function to receive the complete message from the NEW_PIPE pipe. Because the pipe has not been created explicitly, it will be created as a public pipe the first time it is read. The first item in the message is a count of the total items contained in the message, which is read in line 9. Lines 10 through 13 contain a FOR loop to read message items. Line 11 uses the UNPACK_MESSAGE procedure to retrieve the next item from the message. Lines 15 and 16 display the error status code if a problem occurred while reading the message from the pipe.
6. Run SQL*Plus and connect as the WAITE user account to create a second session, and run CHP12_13.SQL, as shown in Figure 12.13. The PL/SQL code in the file sends 10 messages to the public pipe to be displayed by the second session.
Line 4 puts the first message item with the count of items in the message so that the receiving process will know how many items to read from the message. Lines 5 through 7 perform a loop 10 times, using the PACK_MESSAGE procedure to add each message item to the local message buffer. The SEND_MESSAGE function in line 8 is used to send the message to the pipe.
7. Now watch the first session. When the process in the second session is executed, the ten message items are displayed in the first session before it terminates. Figure 12.14 show the results in the first session after the process in the second session has executed.
Alternatively, you can pack and send each item as a complete message by calling the PACK_MESSAGE procedure followed by calling SEND_MESSAGE function in a loop that executes ten times. On the other end, you can execute a block that issues READ_MESSAGE followed by UNPACK_MESSAGE in a loop that runs ten times.
How It Works
Named pipes can be either public or private. Message items are packed into a message with the PACK_MESSAGE procedure and the message is sent to a pipe with the SEND_MESSAGE function. Messages are received with the RECEIVE_MESSAGE function and items in the message are unpacked with the UNPACK_MESSAGE procedure.
Step 1 packs and sends a message via the named pipe using the PACK_MESSAGE and SEND_MESSAGE procedures. Step 2 the message in the pipe is read using the RECEIVE_MESSAGE and UNPACK_MESSAGE procedures. Step 3 creates a private pipe called WAITE_PIPE by executing the CREATE_PIPE function. Step 4 removes the pipe created in Step 3. Step 5 employs a session to run a PL/SQL module using a loop to continuously check a public pipe for messages.
When a message is received, the module runs a loop depending on the item count, which is the first item in the message. Step 6 executes a PL/SQL module to create a message in which the first item is the item count followed by the message items. After the message is built in the buffer, it is sent to the pipe. The PACK_MESSAGE and UNPACK_MESSAGE procedures are overloaded to accept VARCHAR2, NUMBER, and DATE variables. Use a conversion function for any other datatypes. To recognize the datatype of the next item in the buffer, use the NEXT_ITEM_TYPE function. An ORA-06558 is raised by the PACK_MESSAGE procedure if the buffer size exceeds 4096 bytes. An ORA-06656 is generated by the NEXT_ITEM_TYPE function if the buffer contains no more items, and ORA-06659 if the item requested is of the wrong datatype (see Table 12.6).
Table 12.6 Return values of the NEXT_ITEM_TYPE function
Return Value Datatype 0 No more items 6 NUMBER 9 VARCHAR2 11 ROWID 12 DATE 23 RAW With multiple sessions using the same pipe, a session can pack its unique session name returned by the UNIQUE_SESSION_NAME function as a message item to identify the sender of the message. A user-defined protocol can be further worked upon by two sessions that wish to communicate with each other, for example, using the unique session name as the pipe name.
Comments
Messages sent over pipes are buffered in the system global area (SGA). There can be multiple readers and writers of the same pipe, but the communicating sessions must be in the same instance. Messages in pipes are lost when the instance is shut down. Pipes currently being used in the system can be queried from the V$DB_PIPES data dictionary view.
Pipes are an effective way to communicate asynchronously between processes. Unlike the DBMS_ALERT package presented in How-To 12.4, a COMMIT statement does not need to be executed in order to send the message. When two processes need to communicate, the DBMP_PIPE package provides benefits over the DBMS_ALERT package. When many processes are interested in messages sent by another process, the DBMS_ALERT package is preferable.
The Privileges are that users with the EXECUTE ANY PROCEDURE can access the DBMS_PIPE package. Other users must be granted EXECUTE privilege on the DBMS_PIPE package by the DBA and security can be further tightened by using private pipes.