Oracle – DBMS_PIPE example

[adsense id=”0514458240″ width=”468″ height=”60″]

This post demonstrates how pipes can be used to communicate information between processes in an Oracle database. In this example we will have one session that will write content to the database pipe and another process that will read the contents from the pipe. Each process must have a database connection in order to place data into a pipe and another to read from the pipe.

Useful examples of this may be a database process that needs to talk with a database server in order to issues specific OS commands. One process can be written in PL/SQL the other in C++.

A pipe is accessed via a name.

It is possible to add many items to a pipe before the receiving process reads the contents.

In our example, we have created a write process that writes a message to the pipe. We will call this same routine several times to make sure contents are added.

Then we will start a receiving process which will read the contents from the named pipe and display on screen.

The sending PL/SQL code is as follows:

DECLARE
 pipe_name VARCHAR2(100) := 'mypipe';
 res INTEGER;
BEGIN
	DBMS_PIPE.PACK_MESSAGE('This is a test of DBMS_PIPE');
	res := DBMS_PIPE.SEND_MESSAGE(pipe_name);
END;
/

The receiving code is:

DECLARE
 pipe_name VARCHAR2(100) := 'mypipe';
 data VARCHAR2(100);
 res INTEGER;
BEGIN
	res := DBMS_PIPE.RECEIVE_MESSAGE(pipe_name, 2);
	WHILE res = 0 LOOP
		DBMS_PIPE.UNPACK_MESSAGE(data);
		DBMS_OUTPUT.PUT_LINE(data);
		res := DBMS_PIPE.RECEIVE_MESSAGE(pipe_name, 2);
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('DBMS_PIPE terminated with ' || res);
END;
/

Now let’s have a look at this in action:

SQL> r
  1  DECLARE
  2   pipe_name VARCHAR2(100) := 'mypipe';
  3   res INTEGER;
  4  BEGIN
  5  	DBMS_PIPE.PACK_MESSAGE('This is a test of DBMS_PIPE');
  6  	res := DBMS_PIPE.SEND_MESSAGE(pipe_name);
  7* END;

PL/SQL procedure successfully completed.

At this point there is something in the named pipe ‘mypipe’. We need to use the same name for the receiving process in order to be able to pick up the contents in the pipe.

SQL> r
  1  DECLARE
  2   pipe_name VARCHAR2(100) := 'mypipe';
  3   data VARCHAR2(100);
  4   res INTEGER;
  5  BEGIN
  6  	res := DBMS_PIPE.RECEIVE_MESSAGE(pipe_name, 2);
  7  	WHILE res = 0 LOOP
  8  		DBMS_PIPE.UNPACK_MESSAGE(data);
  9  		DBMS_OUTPUT.PUT_LINE(data);
 10  		res := DBMS_PIPE.RECEIVE_MESSAGE(pipe_name, 2);
 11  	END LOOP;
 12  	DBMS_OUTPUT.PUT_LINE('DBMS_PIPE terminated with ' || res);
 13* END;
This is a test of DBMS_PIPE
DBMS_PIPE terminated with 1

PL/SQL procedure successfully completed.

In the example above we have sent the message ‘This is a test of DBMS_PIPE’ and upon receiving all the contents we terminated with a timeout (res = 1).