Oh Danny Boy, The DBMS_PIPES Are Calling

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

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles