Oh Danny Boy, The DBMS_PIPES Are Calling

July 10, 2003

Oracle pipes have been around for a long time but I venture to say that not many use this powerful mechanism. Let's explore this feature and realize that it is truly not dead yet.

The DBMS_PIPE package is really quite simple. Its main purpose in life is to provide a communication mechanism between two different sessions running in the same instance. While we may think of different sessions as two or more completely different connections, at the core these sessions can also be the same connection. In this article, I will take you through a simple code sample that will give you a basic introduction of how to use the DBMS_PIPE package. The complete code listing can be found at the end of this article in Listing E.

Loading a Pipe

The process of putting information into a pipe is really quite easy. There are two procedures called PACK_MESSAGE and SEND_MESSAGE that are used. PACK_MESSAGE puts information onto the pipe one variable at a time. You can load as many variables as you want until you issue the SEND MESSAGE. Each variable issued through the PACK_MESSAGE, until the SEND_MESSAGE, is considered as one row of information. Therefore, if you had a row of information that consisted of two columns, you would issue two PACK_MESSAGES and put the row on the pipe with one SEND_MESSAGE. The information is not available within the pipe until you issue the SEND_MESSAGE. In the sample code Listing A, I am loading a row of information that contains two columns of data, the name of the pipe and a timestamp. While there is a procedure within the DBMS_PIPE package to explicitly create a pipe, this simple procedure will implicitly create a pipe just by sending a message to a named pipe. If the pipe exists, it uses it. If the pipe does not exist, it creates one. Also to note is the load_timeout and load_size variables. The load_timeout is the number of seconds that the SEND_MESSAGE will wait trying to put this information on the pipe. The load_size is the size of the pipe to create or expand the pipe to if the pipe does not exist or does exist in the case of a re-size. The pipe_returncode can be checked for success (if zero) or timeout (if one), an interrupt (if three), or other Oracle permission and pipe type errors. While I do not do it here, it is highly recommended to check for success. One of the few problems with using pipes is that if after you load the pipe and the instance goes down, everything in the pipe is lost.

LISTING A:
Sample of how to load a pipe with a row of two columns

PROCEDURE load_pipe (thetime    IN DATE) IS
BEGIN
 DBMS_PIPE.PACK_MESSAGE(namedpipe);
 DBMS_PIPE.PACK_MESSAGE(thetime);
pipe_returncode := DBMS_PIPE.SEND_MESSAGE(namedpipe,load_timeout,load_size);
END load_pipe;

Rading a Pipe

In order to get information from the pipe, there are two procedures that do the direct opposite of the PACK_MESSAGE and SEND_MESSAGE. These two procedures are RECEIVE_MESSAGE and UNPACK_MESSAGE. The RECEIVE_MESSAGE first will check to see if a pipe by the name supplied exists, if it does not exist, Oracle will implicitly create a pipe by the name supplied and then wait to receive messages on that pipe for the designated timeout (timeonpipe in our example). After checking if the pipe name exists, the RECEIVE_MESSAGE call will then check the pipe to see if there are rows of information on the pipe, again for the designated timeout. Again, you should check the pipe_returncode for possible un-success and error conditions. I am really only checking to see if success happens and if not I assume and end of pipe condition. The UNPACK_MESSAGE will take off the individual columns from the row one at a time. Therefore, as in our previous code sample, to get the information off the pipe, you would issue one RECEIVE_MESSAGE and two UNPACK_MESSAGE(s). Look at Listing B to continue with the example. A couple of notes on the code sample. The PUT_LINE is added just to display the information from the queue when called from the main procedure later in this article. Remember to issue the SET SERVEROUTPUT ON statement to get the display to your SQL*PLUS session. If the pipe_returncode is zero, you can assume that there is nothing on the pipe and it is basically an end of pipe condition.

LISTING B:
Sample of how to read a pipe for one row of two columns

PROCEDURE read_pipe (timeonpipe IN NUMBER,
                                             endofpipe OUT NUMBER) IS
BEGIN
 endofpipe := 0;
 pipe_returncode := DBMS_PIPE.RECEIVE_MESSAGE(namedpipe, timeonpipe);
 IF pipe_returncode = 0 THEN
  DBMS_PIPE.UNPACK_MESSAGE(namedpipe);
  DBMS_PIPE.UNPACK_MESSAGE(p_thetime);
  DBMS_OUTPUT.PUT_LINE(namedpipe||' : '||to_char(p_thetime,'MM/DD/YYYY:HH24:MI:SS'));
 ELSE
  endofpipe := 1;
 END IF;
END read_pipe;

A Simple Driver

Here, in Listing C, I have just given a very simple driver to the loading of the pipe and reading from the pipe. The first line after the BEGIN statement I assign a name to the pipe that is the current user of the session. By doing this, it allows me to have a unique pipe for each of the users that may execute this package and pass information between two distinct connections by the same user. You could expand upon this by concatenating a pipe name with the SESSION_USER if you needed multiple distinct pipes for your users. The next statements load a pipe with the call to LOAD_PIPE passing the system date. I do this three different times just to get multiple rows into the pipe. After the loading of the pipe, I invoke a LOOP to read the pipe continually until I get an end of pipe condition.

LISTING C:
Main procedure to load and read from pipe

PROCEDURE da_main IS
BEGIN
  SELECT SYS_CONTEXT('USERENV', 'SESSION_USER',30) 
    INTO namedpipe FROM DUAL;
  load_pipe(sysdate);
  load_pipe(sysdate);
  load_pipe(sysdate);
  timeonpipe := 15;
  LOOP
    read_pipe(timeonpipe, endofpipe);
    IF endofpipe = 1 THEN
      EXIT;
    END IF;
  END LOOP;
END da_main;

Types of Pipes

When discussing pipes you must be aware of four definitions of pipe types. There are two pipe types, public and private. In addition, there are two methods of creating pipes, implicit and explicit. Listing D puts these pipe definitions into focus.

LISTING D:
Types of Pipes

Public Pipes

All information written to a public pipe is done just as a FIFO list would be and may only be accessed by the schema which created it.

Private Pipes

Private pipes must be created explicitly by issuing the CREATE_PIPE function and are removed explicitly by the REMOVE_PIPE function or a shutdown of the instance. Access to a private pipe can only be accomplished by a session running under the same userid privilege as the creator of the private pipe or a user connected as SYSDBA.

Implicit Pipes

These are created when referenced and are removed when they are empty.

Explicit Pipes

Created by the CREATE_PIPE function and must be explicitly removed by the REMOVE_PIPE function. These pipes may be accessed by any schema that is given access to them

Other Uses for Pipes

Do not overlook the power of the pipes. They have many uses from creating dynamic queues, passing information from external programs into the database instance, to passing information to RMAN for backup and recovery procedures. While this article may seem simple in nature, it is only because pipes are simple to administer and use. However, the benefits are endless. Go ahead and add pipes as part of your arsenal, you won't be disappointed.


LISTING E:
The complete code listing for using the DBMS_PIPE package

CREATE OR REPLACE PACKAGE da_pipe AS
 PROCEDURE read_pipe  (timeonpipe          IN NUMBER,
                          endofpipe          OUT NUMBER);
 PROCEDURE load_pipe  (thetime             IN DATE);
 PROCEDURE da_main;
END da_pipe;
/
CREATE OR REPLACE PACKAGE BODY da_pipe AS
namedpipe             VARCHAR2(30);
endofpipe             NUMBER;
timeonpipe            NUMBER;
pipe_returncode       NUMBER;
p_thetime             DATE;
load_timeout          NUMBER := 30;
load_size             NUMBER := 4096;
PROCEDURE load_pipe (thetime    IN DATE) IS
BEGIN
 DBMS_PIPE.PACK_MESSAGE(namedpipe);
 DBMS_PIPE.PACK_MESSAGE(thetime);
 pipe_returncode := DBMS_PIPE.SEND_MESSAGE(namedpipe,load_timeout,load_size);
END load_pipe;
PROCEDURE read_pipe (timeonpipe IN NUMBER,
                     endofpipe OUT NUMBER) IS
BEGIN
 endofpipe := 0;
 pipe_returncode := DBMS_PIPE.RECEIVE_MESSAGE(namedpipe, timeonpipe);
 IF pipe_returncode = 0 THEN
  DBMS_PIPE.UNPACK_MESSAGE(namedpipe);
  DBMS_PIPE.UNPACK_MESSAGE(p_thetime);
  DBMS_OUTPUT.PUT_LINE(namedpipe||' : '||to_char(p_thetime,'MM/DD/YYYY:HH24:MI:SS'));
 ELSE
  endofpipe := 1;
 END IF;
END read_pipe;
PROCEDURE da_main IS
BEGIN
  SELECT SYS_CONTEXT('USERENV', 'SESSION_USER',30) 
    INTO namedpipe FROM DUAL;
  load_pipe(sysdate);
  load_pipe(sysdate);
  load_pipe(sysdate);
  timeonpipe := 15;
  LOOP
    read_pipe(timeonpipe, endofpipe);
    IF endofpipe = 1 THEN
      EXIT;
    END IF;
  END LOOP;
END da_main;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
END da_pipe;
/

» See All Articles by Columnist James Koopmann








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers