DBMS_PIPE Package (Singleton Pipes)

The DBMS_PIPE package lets two or more sessions in the same instance communicate.

Oracle Autonomous AI Database on Dedicated Exadata Infrastructure supports core DBMS_PIPE functionality as available in Oracle Database 19c, plus extensions.

See DBMS_PIPE in Oracle Database 19c PL/SQL Packages and Types Reference or Oracle Database 26ai PL/SQL Packages and Types Reference for details about the core DBMS_PIPE functionality provided in Oracle Database.

DBMS_PIPE Overview for Singleton Pipes

Pipe functionality has several potential applications: external service interface, debugging, independent transactions, and alerts.

On Autonomous AI Database the DBMS_PIPE package has extended functionality to support singleton pipes. See DBMS_PIPE in Oracle Database 19c PL/SQL Packages and Types Reference or Oracle Database 26ai PL/SQL Packages and Types Reference for more information.

Singleton pipe features in DBMS_PIPE provide the following:

• Ability to cache and retrieve a custom message, of up to 32,767 bytes, in Oracle database memory. The message size maximum of 32,767 bytes is applicable to all Pipes, including Singleton Pipes. Previous versions of DBMS_PIPE had a smaller maximum message size.

• Share the cached message across multiple database sessions with concurrent reads.

• Cache Invalidation methods:

  • Explicit cache invalidation controlled by user.

  • Cache invalidation after a user specified parameter (shelflife) time interval (in seconds).

• Declarative and easy to use PL/SQL APIs for caching.

• Supports both Read-Only and Read-Write databases.

A Singleton Pipe can be any one of the supported DBMS_PIPE types:

• Implicit Pipe: Automatically created when a message is sent with an unknown pipe name using the DBMS_PIPE.SEND_MESSAGE function.

• Explicit Pipe: Created using the DBMS_PIPE.CREATE_PIPE function with a user specified pipe name.

• Public Pipe: Accessible by any user with EXECUTE permission on DBMS_PIPE package

• Private Pipe: Accessible by sessions with the same user as the pipe creator.

Summary of DBMS_PIPE Subprograms for Singleton Pipes

This table lists the DBMS_PIPE subprograms and briefly describes them.

Subprogram	Description
CREATE_PIPE Function	Creates a pipe (necessary for private pipes)
NEXT_ITEM_TYPE Function Oracle Database 19c PL/SQL Packages and Types Reference Oracle Database 26ai PL/SQL Packages and Types Reference	Returns datatype of next item in buffer
PACK_MESSAGE Procedures Oracle Database 19c PL/SQL Packages and Types Reference Oracle Database 26ai PL/SQL Packages and Types Reference	Builds message in local buffer
PURGE Procedure Oracle Database 19c PL/SQL Packages and Types Reference Oracle Database 26ai PL/SQL Packages and Types Reference	Purges contents of named pipe
RECEIVE_MESSAGE Function	Copies message from named pipe into local buffer
RESET_BUFFER Procedure Oracle Database 19c PL/SQL Packages and Types Reference Oracle Database 26ai PL/SQL Packages and Types Reference	Purges contents of local buffer
REMOVE_PIPE Function Oracle Database 19c PL/SQL Packages and Types Reference Oracle Database 26ai PL/SQL Packages and Types Reference	Removes the named pipe
SEND_MESSAGE Function	Sends message on named pipe: This implicitly creates a public pipe if the named pipe does not exist
UNIQUE_SESSION_NAME Function Oracle Database 19c PL/SQL Packages and Types Reference Oracle Database 26ai PL/SQL Packages and Types Reference	Returns unique session name
UNPACK_MESSAGE Procedures Oracle Database 19c PL/SQL Packages and Types Reference Oracle Database 26ai PL/SQL Packages and Types Reference	Accesses next item in buffer

CREATE_PIPE Function

This function explicitly creates a public or private pipe. If the private flag is TRUE, then the pipe creator is assigned as the owner of the private pipe.

Explicitly-created pipes can only be removed by calling REMOVE_PIPE, or by shutting down the instance.

In order to create a Singleton Pipe, set the singleton parameter to TRUE. The following arguments are applicable to Singleton Pipes:

• singleton: Indicates that the pipe should be created as a Singleton Pipe (default value: FALSE).

• shelflife: Optionally specify a shelflife expiration (in seconds) of cached message in the Singleton Pipe. It can be used for implicit invalidation of message in Singleton Pipe.

  The message shelflife in Singleton Pipe can also be specified when you send a message message (see SEND_MESSAGE Function).

Syntax

DBMS_PIPE.CREATE_PIPE (
   pipename     IN VARCHAR2,
   maxpipesize  IN INTEGER DEFAULT 66536,
   private      IN BOOLEAN DEFAULT TRUE,
   singleton    IN BOOLEAN DEFAULT FALSE,
   shelflife    IN INTEGER DEFAULT 0)
RETURN INTEGER;

Parameters

Parameter	Description
pipename	Name of the pipe you are creating. You must use this name when you call SEND_MESSAGE and RECEIVE_MESSAGE. This name must be unique across the instance. Caution: Do not use pipe names beginning with ORA$. These are reserved for use by procedures provided by Oracle. Pipename should not be longer than 128 bytes, and is case insensitive. At this time, the name cannot contain Globalization Support characters.
maxpipesize	The maximum size allowed for the pipe, in bytes. The total size of all of the messages on the pipe cannot exceed this amount. The message is blocked if it exceeds this maximum. The default maxpipesize is 66536 bytes. The maxpipesize for a pipe becomes a part of the characteristics of the pipe and persists for the life of the pipe. Callers of SEND_MESSAGE with larger values cause the maxpipesize to be increased. Callers with a smaller value use the existing, larger value. The default maxpipesize of 65536 is applicable for all Pipes.
private	Uses the default, TRUE, to create a private pipe. Public pipes can be implicitly created when you call SEND_MESSAGE.
singleton	Use TRUE to create a Singleton Pipe. Default value: FALSE
shelflife	Expiration time in seconds of a message cached in Singleton Pipe. After the specified shelflife time is exceeded, the message is no longer accessible from the Pipe. The parameter shelflife is only applicable to a Singleton Pipe. Default value is 0, implying that the message never expires.

Return Values

Return	Description
0	Successful. If the pipe already exists and the user attempting to create it is authorized to use it, then Oracle returns 0, indicating success, and any data already in the pipe remains.
6	Failed to convert existing pipe to singleton pipe. Implicit pipe with more than one existing message cannot be converted to a Singleton Pipe. For an explicit pipe that is not Singleton, DBMS_PIPE.SEND_MESSAGE cannot send a message with singleton argument set to TRUE.
7	A non-zero value is given for the shelflife parameter and the pipe is not a singleton pipe.
ORA-23322	Failure due to naming conflict. If a pipe with the same name exists and was created by a different user, then Oracle signals error ORA-23322, indicating the naming conflict.

Exceptions

Exception	Description
Null pipe name	Permission error: Pipe with the same name already exists, and you are not allowed to use it.

Example

Create a Singleton Pipe with shelflife of 1 hour.

DECLARE
  l_status INTEGER;
BEGIN
  l_status := DBMS_PIPE.create_pipe(pipename  => 'MY_PIPE1',
                                    private   => TRUE,
                                    singleton => TRUE,
                                    shelflife => 3600);
END;
/

RECEIVE_MESSAGE Function

This function copies the message into the local message buffer.

Syntax

DBMS_PIPE.RECEIVE_MESSAGE (
   pipename      IN VARCHAR2,
   timeout       IN INTEGER      DEFAULT maxwait,
   cache_func    IN VARCHAR2     DEFAULT NULL)
RETURN INTEGER;

Parameters

Table - RECEIVE_MESSAGE Function Parameters

Parameter	Description
pipename	Name of the pipe on which you want to receive a message. Names beginning with ORA$ are reserved for use by Oracle.
timeout	Time to wait for a message, in seconds. A timeout of 0 lets you read without blocking. The timeout does not include the time spent in execution cache function specified in the cache_func parameter. Default value: is the constant MAXWAIT, which is defined as 86400000 (1000 days).
cache_func	Cache function name to automatically cache a message in a Singleton Pipe. The name of the function should be fully qualified with the owner schema: OWNER.FUNCTION_NAME OWNER.PACKAGE.FUNCTION_NAME Default value: NULL

Return Values

Table - RECEIVE_MESSAGE Function Return Values

Return	Description
0	Success
1	Timed out. If the pipe was implicitly-created and is empty, then it is removed.
2	Record in the pipe is too large for the buffer.
3	An interrupt occurred.
8	Cache function can only be specified when using a Singleton Pipe.
ORA-23322	User has insufficient privileges to read from the pipe.

Usage Notes

• To receive a message from a pipe, first call RECEIVE_MESSAGE. When you receive a message, it is removed from the pipe; hence, a message can only be received once. For implicitly-created pipes, the pipe is removed after the last record is removed from the pipe.

• If the pipe that you specify when you call RECEIVE_MESSAGE does not already exist, then Oracle implicitly creates the pipe and waits to receive the message. If the message does not arrive within a designated timeout interval, then the call returns and the pipe is removed.

• After receiving the message, you must make one or more calls to UNPACK_MESSAGE to access the individual items in the message. The UNPACK_MESSAGE procedure is overloaded to unpack items of type DATE, NUMBER, VARCHAR2, and there are two additional procedures to unpack RAW and ROWID items. If you do not know the type of data that you are attempting to unpack, then call NEXT_ITEM_TYPE to determine the type of the next item in the buffer.

Cache Function Parameter

Singleton Pipes support cache function to automatically cache a message in the pipe in case of the following two scenarios:

• Singleton Pipe is empty.

• Message in Singleton Pipe is invalid due to shelflife time elapsed.

The name of the function should be fully qualified with the owner schema:

• OWNER.FUNCTION_NAME

• OWNER.PACKAGE.FUNCTION_NAME

To use a cache function the current session user that invokes DBMS_PIPE.RECEIVE_MESSAGE must have required privileges to execute the cache function.

Cache Function Syntax

CREATE OR REPLACE FUNCTION *cache_function_name* (
       pipename  IN VARCHAR2
) RETURN INTEGER;

Parameter	Datatype	Description
pipename	VARCHAR2	Name of the Singleton Pipe.

Return	Description
0	Success
Non-zero	Failure value returned fromDBMS_PIPE.RECEIVE_MESSAGE

Define a cache function to provide encapsulation and abstraction of complexity from the reader sessions of Singleton Pipe. The typical operations within a cache function would be:

• Create a Singleton Pipe, for an Explicit Pipe, using DBMS_PIPE.CREATE_PIPE.

• Create the message to cache in the Singleton Pipe.

• Send Message to Singleton Pipe, optionally specifying a shelflife for the implicit message.

Exceptions

Exception	Description
Null pipe name	Permission error. Insufficient privilege to remove the record from the pipe. The pipe is owned by someone else.

Example

DECLARE
  l_status INTEGER;
BEGIN
  l_status := DBMS_PIPE.receive_message(pipename   => 'MY_PIPE1',
                                        timeout    => 1,
                                        cache_func => 'MY_USER.MY_CACHE_FUNC');
END;
/

SEND_MESSAGE Function

This function sends a message on the named pipe.

The message is contained in the local message buffer, which was filled with calls to PACK_MESSAGE. You can create a pipe explicitly using CREATE_PIPE, otherwise, it is created implicitly.

To create an implicit Singleton pipe, set the singleton parameter to TRUE. The following arguments are applicable to Singleton Pipes:

• singleton: Indicates that the pipe should be created as a Singleton Pipe (default value: FALSE).

• shelflife: Optionally specify a shelflife expiration of cached message in the Singleton Pipe. It can be used for implicit invalidation of message in Singleton Pipe.

  This argument is applicable for implicit as well as explicit Singleton pipes. A shelflife value specified in SEND_MESSAGE Function overwrites the shelflife specified for the Explicit Singleton Pipe in

CREATE_PIPE Function and will be the default for any new messages cached in the Singleton Pipe.

Syntax

DBMS_PIPE.SEND_MESSAGE (
    pipename     IN VARCHAR2,
    timeout      IN INTEGER DEFAULT MAXWAIT,
    maxpipesize  IN INTEGER DEFAULT 65536,
    singleton    IN BOOLEAN DEFAULT FALSE,
    shelflife    IN INTEGER DEFAULT 0)
RETURN INTEGER;

Parameters

Table - SEND_MESSAGE Function Parameters

Parameter	Description
pipename	Name of the pipe on which you want to place the message. If you are using an explicit pipe, then this is the name that you specified when you called CREATE_PIPE. **Caution:** Do not use pipe names beginning with 'ORA$'. These names are reserved for use by procedures provided by Oracle. Pipename should not be longer than 128 bytes, and is case-insensitive. At this time, the name cannot contain Globalization Support characters.
timeout	Time to wait while attempting to place a message on a pipe, in seconds. The default value is the constant MAXWAIT, which is defined as 86400000 (1000 days).
maxpipesize	Maximum size allowed for the pipe, in bytes. The total size of all the messages on the pipe cannot exceed this amount. The message is blocked if it exceeds this maximum. The default is 65536 bytes. The maxpipesize for a pipe becomes a part of the characteristics of the pipe and persists for the life of the pipe. Callers of SEND_MESSAGE with larger values cause the maxpipesize to be increased. Callers with a smaller value simply use the existing, larger value. Specifying maxpipesize as part of the SEND_MESSAGE procedure eliminates the need for a separate call to open the pipe. If you created the pipe explicitly, then you can use the optional maxpipesize parameter to override the creation pipe size specifications. The default maxpipesize of 65536 is applicable for all Pipes.
singleton	Use TRUE to create a Singleton Pipe. Default value: FALSE
shelflife	Expiration time in seconds of a message cached in Singleton Pipe. After the specified shelflife time is exceeded, the message is no longer accessible from the Pipe. The parameter shelflife is only applicable to a Singleton Pipe. Default value is 0, implying that the message never expires.

Return Values

Return	Description
0	Success. If the pipe already exists and the user attempting to create it is authorized to use it, then Oracle returns 0, indicating success, and any data already in the pipe remains. If a user connected as SYSDBS/SYSOPER re-creates a pipe, then Oracle returns status 0, but the ownership of the pipe remains unchanged.
1	Timed out. This procedure can timeout either because it cannot get a lock on the pipe, or because the pipe remains too full to be used. If the pipe was implicitly-created and is empty, then it is removed.
3	An interrupt occurred. If the pipe was implicitly created and is empty, then it is removed.
6	Failed to convert existing pipe to singleton pipe. Implicit pipe with more than one existing message cannot be converted to a Singleton Pipe. For an explicit pipe that is not Singleton, DBMS_PIPE.SEND_MESSAGE cannot send a message with singleton argument set to TRUE.
7	A non-zero value is given for the shelflife parameter and the pipe is not a singleton pipe.
ORA-23322	Insufficient privileges. If a pipe with the same name exists and was created by a different user, then Oracle signals error ORA-23322, indicating the naming conflict.

Exceptions

Exception	Description
Null pipe name	Permission error. Insufficient privilege to write to the pipe. The pipe is private and owned by someone else.

Related Content

Cache Messages with Singleton Pipes