[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:
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.
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).