DBMS_PIPE sends messages between sessions of the same database instance through named pipes in memory. Unlike queues, pipes are not transactional and not stored: a message is sent at once, whether or not the sender commits, and is lost if the instance restarts. That makes pipes light and fast for signals and debugging, and for a simple shared cache.
Code for This Guide
The main examples are in the examples/pkg-messaging folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_pipe.pack_message(item); -- repeat for each item status := dbms_pipe.send_message(pipe_name, timeout); status := dbms_pipe.receive_message(pipe_name, timeout); dbms_pipe.unpack_message(variable_out); -- in the same order
Status 0 means success and 1 a timeout.
Send and Receive
For the example one session plays both roles; normally the receiver is another session.
Example:
declare
v_status integer;
v_flight varchar2(6);
v_delay number;
v_when date;
begin
-- the sender: pack items into the message buffer, then send it
dbms_pipe.pack_message('NM150');
dbms_pipe.pack_message(45);
dbms_pipe.pack_message(date '2026-03-15');
v_status := dbms_pipe.send_message('NIMBUS$DELAYS', timeout => 5);
dbms_output.put_line('sent: ' || v_status);
-- the receiver (usually another session): receive, then unpack in the same order
v_status := dbms_pipe.receive_message('NIMBUS$DELAYS', timeout => 5);
dbms_output.put_line('received: ' || v_status || ', next item type '
|| dbms_pipe.next_item_type); -- 9 = VARCHAR2
dbms_pipe.unpack_message(v_flight);
dbms_pipe.unpack_message(v_delay);
dbms_pipe.unpack_message(v_when);
dbms_output.put_line(v_flight || ' delayed ' || v_delay || ' minutes on ' || v_when);
v_status := dbms_pipe.receive_message('NIMBUS$DELAYS', timeout => 0);
dbms_output.put_line('again: ' || v_status || ' (1 = timed out, nothing there)');
end;
/Output:
sent: 0 received: 0, next item type 9 NM150 delayed 45 minutes on 15-MAR-2026 again: 1 (1 = timed out, nothing there) PL/SQL procedure successfully completed.
Three items of different types are packed into one message and unpacked in the same order. NEXT_ITEM_TYPE tells the receiver the type of the next item, 9 for VARCHAR2. A second receive with timeout 0 returns 1: the pipe is empty.
A Singleton Pipe as a Cache
A singleton pipe holds at most one message, which receivers read without removing it. With a cache function, an empty or expired pipe fills itself.
Example:
-- the cache function fills the pipe when it is empty or its message is too old
create or replace function fx_rate_cache (pipename in varchar2) return integer is
begin
dbms_output.put_line(' (cache function called)');
dbms_pipe.pack_message(3.6725); -- in real life: read the rate somewhere
return dbms_pipe.send_message(pipename, singleton => true, shelflife => 600);
end;
/
declare
v_status integer;
v_rate number;
begin
v_status := dbms_pipe.create_pipe('FX_USD_AED', singleton => true, shelflife => 600);
for i in 1 .. 3 loop
v_status := dbms_pipe.receive_message('FX_USD_AED', timeout => 0,
cache_func => 'NIMBUS.FX_RATE_CACHE');
dbms_pipe.unpack_message(v_rate);
dbms_output.put_line('read ' || i || ': status ' || v_status || ', rate ' || v_rate);
end loop;
v_status := dbms_pipe.remove_pipe('FX_USD_AED');
end;
/Output:
Function FX_RATE_CACHE compiled (cache function called) read 1: status 0, rate 3.6725 read 2: status 0, rate 3.6725 read 3: status 0, rate 3.6725 PL/SQL procedure successfully completed.
The first read finds the pipe empty and calls FX_RATE_CACHE, which sends the rate with a shelf life of 600 seconds. The next two reads get the cached message without calling the function. The example removes the pipe and drops the function afterward.
Things to Know
- Pipes work within one instance only; in Oracle RAC, sessions on other instances cannot see them.
- Public pipes can be read by anyone who knows the name; use names with a prefix and remove pipes when done.
- Use queues instead when messages must survive a restart or follow transactions.
Related Guides
- How to Enqueue Messages with DBMS_AQ.ENQUEUE
- How to Cache Function Results with RESULT_CACHE in PL/SQL
Conclusion
DBMS_PIPE passes packed messages between sessions in memory, immediately and without transactions. Use it for signals, debugging output, and singleton caches, and Advanced Queuing when messages must be durable.
