How to use DBMS_PIPE to halt and continue a PLSQL database session

I posted a message on twitter saying that DBMS_PIPE is an excellent mechanism to make a session run and halt in PLSQL. One response I gotten was asking for an example of that. That is what this post is about.

DBMS_PIPE is an implementation of a pipe inside the Oracle database. A pipe is a mechanism that is not limited to the Oracle database, in fact I assume the implementation is inspired by an operating system level pipe, which can be created using the ‘mknod /path/pipename p’ on unix and unix-like systems. A quick search shows windows has got them too, but not really, or does it? Another implementation is the pipe to redirect output from one command to the next using ‘|’. The essence of a pipe that input to and output from the pipe are separated from each other, and that information is flowing from the inputting process of the pipe to the outputting process.

For the specific purpose this blogpost is about, DBMS_PIPE, I am not using the information that is sent through the pipe, I am mainly using the fact that a receiving session waits for information to be sent, and therefore halts execution.

When the Oracle database executes in PL/SQL mode, there is no simple way to make it pause and request for user input, like you would do with the ‘pause’ or ‘accept’ commands in sqlplus in SQL mode. PL/SQL runs and provides its output when the session quits PL/SQL mode, in other words: there is no interaction with the terminal that executes the PL/SQL procedure or anonymous block. So if you want to make it stop and continue, you must use something else, for which I use DBMS_PIPE. Of course there are other ways, I would encourage people to come up with other mechanisms as a comment.

The use the technique of using DBMS_PIPE, you need two sessions, one which runs the PL/SQL block that you want to pause and continue (using a database pipe), and one to make it continue by sending a message to that pipe. Another thing that you might notice is the lack of explicit setup of the pipe; I just declare waiting for the pipe, and it’ll get created implicitly.

This is how it’s done:

1. Simple example anonymous PL/SQL block:

set serverout on;
declare
 v_result number;
 v_time1 date;
 v_time2 date;
begin
  select sysdate into v_time1 from dual;
  v_result := dbms_pipe.receive_message ( 'test' );
  select sysdate into v_time2 from dual;
  dbms_output.put_line('you waited: '||(24*60*60)*(v_time2-v_time1)||' seconds');
end;
/

If you execute this, it will provide no output, and in fact be waiting for the pipe that I named ‘test’.

In order to make anonymous PL/SQL block continue, I need another session to send a message to the pipe:
2. Send empty message to the pipe:

var v_result number;
exec v_result := dbms_pipe.send_message ( 'test' );

Once you executed that, the PL/SQL block will continue, and then finish and therefore display the output:

you waited: 3 seconds

PL/SQL procedure successfully completed.

There are a couple of other caveats you need to be aware of:
– a pipe is visible to the instance, in the case of a cluster database it’s NOT visible on the other instances in the cluster.
– there can be multiple sessions waiting on the same pipe, but a single message is consumed/received by a single session, based on time.

Advertisement

Compressing sqlplus output using a pipe

Recently I am involved in a project which requires a lot of data to be extracted from Oracle. The size of the data was so huge that the filesystems filled up. Compressing the output (using tar j (bzip2) or z (gzip)) is an obvious solution, but this can only be done after the files are created. This is why I proposed compressing the output without ever existing in uncompressed form.

This solution works with a so called ‘named pipe’, which is something for which I know for sure it can be done on Linux and unix. A named pipe has the ability to let two processes transfer data between each other. This solution will look familiar to “older” Oracle DBA’s: this was how exports where compressed from the “original” export utility (exp).

I’ve created a small script which calls sqlplus embedded in it, and executes sqlplus commands using a “here command”:

mknod /tmp/oracle.pipe p

sqlplus / as sysdba << _EOF
set escape on

host nohup gzip -c < /tmp/oracle.pipe > /tmp/out1.gz \&
spool /tmp/oracle.pipe
select * from dual;
spool off

host nohup gzip -c < /tmp/oracle.pipe > /tmp/out2.gz \&
spool /tmp/oracle.pipe
select * from dual;
spool off

_EOF

rm /tmp/oracle.pipe

First a pipe is created (mknod filename p). As far as I know, this command is the same on Linux and the unixes. This pipe is removed as the last step of the script.

Inside the sqlplus script, I issue the gzip operating system command using the ‘host’ command. The line with the host command starts the gzip command with the pipe as input, and output to a .gz file in /tmp. The process is put in the background using ‘&’.
Next, the sqlplus spool command starts output to the pipe, and I execute a dummy sql (select * from dual).
With ‘spool off’, the output to the pipe is stopped. This makes the gzip process in the background to stop.
Because the gzip process is not compressing anymore to the first file, it can be used for a second time, and more times of course.

The result is two gzipped files:

zcat -v /tmp/out*.gz
/tmp/out1.gz:	SQL> select * from dual;

D                                                                               
-                                                                               
X                                                                               

SQL> spool off
 84.2%
/tmp/out2.gz:	SQL> select * from dual;

D                                                                               
-                                                                               
X                                                                               

SQL> spool off
 84.2%