Singleton Pipe is an addition to the DBMS_PIPE package
that allows you to cache and retrieve a custom message and share the message across multiple
database sessions with concurrent reads.
Create an Explicit Singleton Pipe Describes the steps to create a Singleton Pipe with a specified pipe name (an Explicit Singleton Pipe).
Create an Explicit Singleton Pipe with a Cache Function Describes the steps to create a Singleton Pipe with a specified pipe name, an Explicit Singleton Pipe, and provide a cache function. A cache function allows you to automatically populate the message in a singleton pipe.
The
DBMS_PIPE package has extended functionality on Autonomous Database to support Singleton
Pipes.
A Singleton Pipe in DBMS_PIPE:
Provides in-memory caching of custom data using Singleton Pipe
messages.
Supports the ability to cache and retrieve a custom message of up to
32,767 bytes.
Supports sharing a cached message across multiple database sessions
with concurrent reads. This provides high throughput and supports concurrent
reads of messages across database sessions.
Supports Read-Only and Read-Write databases.
Supports several cache invalidation methods:
Explicit cache invalidation controlled by user.
Cache invalidation after a user specified time interval (in
seconds). This invalidation method is controlled by the message sender,
using the shelflife parameter, instead of by message
readers. This avoids the common pitfalls due to incorrect use of cache by
readers.
About Standard Pipes and Singleton Pipes
The DBMS_PIPE Package allows two or more database sessions to communicate using in-memory messages. Pipe functionality has several applications such as external service interface, debugging, independent transactions, and alerts.
A Singleton Pipe can cache one message in the pipe, hence the name
"singleton".
The message in a Singleton Pipe can be comprised of multiple fields, up
to a total message size of 32,767 bytes.
DBMS_PIPE supports the ability to pack
multiple attributes in a message using
DBMS_PIPE.PACK_MESSAGE procedure.
For a Public Singleton Pipe, the message can be received by any database
session with execute privilege on DBMS_PIPE
package.
For Private Singleton Pipe, the message can be received by sessions with
the same user as the creator of the Singleton Pipe.
High Message Throughput for Reads
Singleton Pipes cache the message in the pipe until it is
invalidated or purged. Database sessions can concurrently read a message
from the Singleton Pipe.
Receiving a message from a Singleton Pipe is a non-blocking
operation.
Message Caching
A message is cached in a Singleton Pipe using
DBMS_PIPE.SEND_MESSAGE.
If there is an existing cached message in the Singleton Pipe, then
DBMS_PIPE.SEND_MESSAGE overwrites the previous
message to maintain only one message in the Singleton Pipe.
Message Invalidation
Explicit Invalidation: purges the pipe with the
procedure DBMS_PIPE.PURGE or by overwriting the message
using DBMS_PIPE.SEND_MESSAGE.
Automatic Invalidation: a message can be invalidated
automatically after the specified shelflife time has
elapsed.
No Eviction from Database Memory
Singleton Pipes do not get evicted from Oracle Database memory.
An Explicit Singleton Pipe continues to reside in database
memory until it is removed using DBMS_PIPE.REMOVE_PIPE
or until the database restarts.
An Implicit Singleton Pipe stays in database memory until there is one
cached message in the pipe.
Automatic Refresh of Cached
Message with a Cache Function 🔗
The
DBMS_PIPE package allows you to automatically populate a Singleton Pipe
message using a user-defined cache function.
By default, after a message is invalidated with either Singleton Pipe
explicit or implicit invalidation, a subsequent
DBMS_PIPE.RECEIVE_MESSAGE results in no message being received. To
add a new message to the pipe, the message must be explicitly cached by calling
DBMS_PIPE.SEND_MESSAGE. To avoid this case, where no message is
available when you read from a Singleton Pipe, you can define a cache function. With a
cache function defined, the cache function is automatically invoked when you receive a
message in following scenarios:
When the Singleton Pipe is empty.
When the message in a Singleton Pipe is invalid due to the
shelflife time elapsed.
To use a cache function define the cache function and include the
cache_func parameter with
DBMS_PIPE.RECEIVE_MESSAGE. A user-defined cache function provides
the following:
The cache function can be specified when reading a message from a
Singleton Pipe using DBMS_PIPE.RECEIVE_MESSAGE.
When there is no message in the Singleton Pipe,
DBMS_PIPE.RECEIVE_MESSAGE calls the cache function.
When the message shelflife time has elapsed, the
database automatically populates a new message in the Singleton Pipe.
Using a cache function simplifies working with Singleton Pipes. You do not
need to handle failure cases for receiving a message from an empty pipe. In addition, a
cache function ensures there is no cache-miss when you read messages from a Singleton
Pipe, providing maximum use of the cached message.
Describes the steps to create a Singleton Pipe with a specified pipe name
(an Explicit Singleton Pipe).
First, for this example create the receive_message
helper function to repeatedly call DBMS_PIPE.RECEIVE_MESSAGE. This
allows you to test singleton pipe functionality.
CREATE OR REPLACE FUNCTION msg_types AS
TYPE t_rcv_row IS RECORD (c1 VARCHAR2(32767), c2 NUMBER);
TYPE t_rcv_tab IS TABLE OF t_rcv_row;
END;
CREATE OR REPLACE FUNCTION receive_message(
pipename IN VARCHAR2,
rcv_count IN NUMBER DEFAULT 1,
cache_func IN VARCHAR2 DEFAULT NULL)
RETURN msg_types.t_rcv_tab pipelined
AS
l_msg VARCHAR2(32767);
l_status NUMBER;
BEGIN
FOR i IN 1..rcv_count LOOP
l_status := DBMS_PIPE.RECEIVE_MESSAGE(
pipename => pipename,
cache_func => cache_func,
timeout => 1);
IF l_status != 0 THEN
raise_application_error(-20000,
'Message not received for attempt: ' || to_char(i) || ' status: ' ||
l_status);
END IF;
DBMS_PIPE.UNPACK_MESSAGE(l_msg);
pipe row(msg_types.t_rcv_row(l_msg));
END LOOP;
RETURN;
END;
Create an explicit singleton pipe named PIPE_TEST with
shelflife parameter set to 3600 (seconds).
SELECT name, singleton, type
FROM v$db_pipes WHERE name= '&pipename' ORDER BY 1;
NAME SINGLETON TYPE
-------------------- ---------- -------
PIPE_TEST YES PRIVATE
Pack and send a message on the singleton pipe.
EXEC DBMS_PIPE.PACK_MESSAGE('This is a real message that you can get multiple times');
SELECT DBMS_PIPE.SEND_MESSAGE(pipename => '&pipename') status FROM DUAL;
STATUS
----------
0
SELECT * FROM receive_message(
pipename => '&pipename',
rcv_count => 2);
MESSAGE
--------------------------------------------------------------------------------
This is a real message that you can get multiple times
This is a real message that you can get multiple times
The receive_message function is a helper
function that calls DBMS_PIPE.RECEIVE_MESSAGE.
Purge the message and remove the pipe.
EXEC DBMS_PIPE.PURGE('&pipename');
SELECT DBMS_PIPE.REMOVE_PIPE('&pipename') status FROM DUAL;
Create an Explicit Singleton Pipe with a Cache
Function 🔗
Describes the steps to create a Singleton Pipe with a specified pipe name,
an Explicit Singleton Pipe, and provide a cache function. A cache function allows you to
automatically populate the message in a singleton pipe.
Create a cache function, test_cache_message for a singleton
pipe.
CREATE OR REPLACE FUNCTION test_cache_message(
pipename IN VARCHAR2) return NUMBER
AS
l_status NUMBER;
l_data VARCHAR2(4000);
BEGIN
l_status := DBMS_PIPE.CREATE_PIPE(
pipename => pipename,
private => TRUE,
singleton => true,
shelflife => 600);
IF l_status != 0 THEN RETURN l_status;
END IF;
DBMS_PIPE.PACK_MESSAGE('This is a placeholder cache message for an empty pipe');
l_status := DBMS_PIPE.SEND_MESSAGE(pipename => pipename);
RETURN l_status;
END;
/
Note
The current session
user invoking DBMS_PIPE.RECEIVE_MESSAGE must have
required privilege to execute the cache function.
Receive with a cache function and confirm the message populates in pipe. The
pipe must exist as a private pipe created in the cache function.
SELECT * FROM receive_message(
pipename => '&pipename',
rcv_count => 1,
cache_func => 'TEST_CACHE_MESSAGE');
MESSAGE
---------------
This is a placeholder cache message for an empty pipe
The receive_message function is a helper
function that calls DBMS_PIPE.RECEIVE_MESSAGE. See Create an Explicit Singleton Pipe for the receive_message definition.
Receive without the cache function to confirm the message persists in the
pipe.
SELECT * FROM receive_message(
pipename => '&pipename',
rcv_count => 2);
MESSAGE
---------------
This is a placeholder cache message for an empty pipe
This is a placeholder cache message for an empty pipe
The receive_message function is a helper
function that calls DBMS_PIPE.RECEIVE_MESSAGE. See Create an Explicit Singleton Pipe for the receive_message definition.