DBMS_PIPE Package

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

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

See DBMS_PIPE 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.
Summary of DBMS_PIPE Subprograms for Singleton Pipes
This table lists the DBMS_PIPE subprograms and briefly describes them.
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.
RECEIVE_MESSAGE Function
This function copies the message into the local message buffer.
SEND_MESSAGE Function
This function sends a message on the named pipe.

Parent topic: Autonomous Database Supplied Package Reference

DBMS_PIPE Overview for Singleton Pipes

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

On Autonomous Database the DBMS_PIPE package has extended functionality to support singleton pipes.

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.

Parent topic: DBMS_PIPE Package

Summary of DBMS_PIPE Subprograms for Singleton Pipes

This table lists the DBMS_PIPE subprograms and briefly describes them.

Table 7-1 DBMS_PIPE Package Subprograms

Subprogram	Description
CREATE_PIPE Function	Creates a pipe (necessary for private pipes)
NEXT_ITEM_TYPE Function	Returns datatype of next item in buffer
PACK_MESSAGE Procedures	Builds message in local buffer
PURGE Procedure	Purges contents of named pipe
RECEIVE_MESSAGE Function	Copies message from named pipe into local buffer
RESET_BUFFER Procedure	Purges contents of local buffer
REMOVE_PIPE Function	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	Returns unique session name
UNPACK_MESSAGE Procedures	Accesses next item in buffer

Parent topic: DBMS_PIPE Package

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

Table 7-2 CREATE_PIPE Function 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

Table 7-3 CREATE_PIPE Function 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

Table 7-4 CREATE_PIPE Function Exception

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;
/

Parent topic: DBMS_PIPE Package

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 7-5 RECEIVE_MESSAGE Function Parameters

Parameter Description

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`

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 7-6 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 from`DBMS_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

Table 7-7 RECEIVE_MESSAGE Function 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;
/

Parent topic: DBMS_PIPE Package

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 7-8 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

Table 7-9 SEND_MESSAGE Function 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

Table 7-10 SEND_MESSAGE Function Exception

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

Parent topic: DBMS_PIPE Package

Oracle Cloud Infrastructure Documentation

DBMS_PIPE Overview for Singleton Pipes

Summary of DBMS_PIPE Subprograms for Singleton Pipes

CREATE_PIPE Function

RECEIVE_MESSAGE Function

SEND_MESSAGE Function