DBMS_PIPE Package (Persistent Messaging 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 Persistent Messaging 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 persistent messaging 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.

Persistent Messages in DBMS_PIPE:

• Support the ability to send and retrieve very large messages.

• Support a large number of pipe messages.

• Support sharing of messages within a single database, across multiple databases, and across databases in different regions.

• Support multiple pipes using the same Cloud Object Store location URI.

  Persistent messaging functionality allows two or more database sessions to communicate with messages that are stored in Cloud Object Store. Using this functionality messages in a pipe can be made available to only the current database or they can be made available to multiple databases in the same region or across different regions.

  A Persistent Messaging 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. Note: When send and receive messages across different databases using persistent messages, Oracle recommends you call DBMS_PIPE.CREATE_PIPE before sending or receiving messages. Creating an explicit pipe with DBMS_PIPE.CREATE_PIPE ensures that a pipe is created with the access permissions you want, either public or private (by setting the PRIVATE parameter to FALSE or using the default value TRUE).

DBMS_PIPE Limitation

The DBMS_PIPE package does not support sending messages between databases that use different character sets. For example, if you have one Autonomous AI Database instances that uses AL32UTF8 and another instance that uses WE8MSWIN1252, you cannot send messages with DBMS_PIPE between these two databases. In this case, you the system will raise the error ORA-12704 if you attempt to send messages with DBMS_PIPE between these two databases.

Summary of DBMS_PIPE Subprograms for Persistent Messaging

This table lists the DBMS_PIPE subprograms and briefly describes them.

Subprogram	Description
CREATE_PIPE Function	Creates a pipe (necessary for private pipes).
GET_CREDENTIAL_NAME Function	Returns the global credential_name variable value.
GET_LOCATION_URI Function	Returns the global location_uri variable value that is used as a default location URI for use when a message is stored in Cloud Object Store.
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.
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 a named pipe: This implicitly creates a public pipe if the named pipe does not exist.
SET_CREDENTIAL_NAME Procedure	Sets the credential_name variable that is used as a default credential for messages that are stored in Cloud Object Store.
SET_LOCATION_URI Procedure	Sets the global location_uri variable that is used as a default location URI for messages that are stored in Cloud Object Store.
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.

Syntax

DBMS_PIPE.CREATE_PIPE (
   pipename     IN VARCHAR2,
   maxpipesize  IN INTEGER DEFAULT 66536,
   private      IN BOOLEAN DEFAULT TRUE)
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.

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.
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 an explicit private named MY_PIPE1

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

GET_CREDENTIAL_NAME Function

This function returns the global credential_name variable value for use when messages are stored to Cloud Object Store.

Syntax

DBMS_PIPE.GET_CREDENTIAL_NAME
         RETURN VARCHAR2;

Return Values

Return Value	Description
credential_name	The name of the credential to access the Cloud Object Storage.

Example

DECLARE
  credential_name     VARCHAR2(400)
BEGIN
  credential_name := DBMS_PIPE.GET_CREDENTIAL_NAME;
END;
/

GET_LOCATION_URI Function

This function returns the global location_uri variable value that can be used as a default location URI when pipe messages are stored to Cloud Object Store.

Syntax

DBMS_PIPE.GET_LOCATION_URI
        RETURN VARCHAR2;

Return Value

Return Value	Description
location_uri	The object URI.

Example

DECLARE
  location_uri     VARCHAR2(400)
BEGIN
  location_uri := DBMS_PIPE.GET_LOCATION_URI;
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,
   credential_name   IN VARCHAR2 DEFAULT null,
   location_uri      IN VARCHAR2)
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 running the cache function specified with the cache_func parameter. Default value: is the constant MAXWAIT, which is defined as 86400000 (1000 days).
credential_name	The credential name for the cloud store used to store messages. The credential_name is a package argument that is by default initialized as NULL. You can set this value before calling DBMS_PIPE.RECEIVE_MESSAGE. The passed parameter value takes precedence over the global variable's value. The credential object must have EXECUTE and READ/WRITE privileges by the user running DBMS_PIPE.RECEIVE_MESSAGE.
location_uri	The location URI for the cloud store used to store messages. The location_uri is a global variable that by default is initialized as NULL. You can set this value before calling DBMS_PIPE.RECEIVE_MESSAGE. The passed parameter value takes precedence over the global variable's value.

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

• Persistent messages are guaranteed to either be written or read by exactly one process. This prevents message content inconsistency due to concurrent writes and reads. Using a persistent messaging pipe, DBMS_PIPE allows only one operation, sending a message or a receiving message to be active at a given time. However, if an operation is not possible due to an ongoing operation, the process retries periodically until the timeout value is reached.

• If you use Oracle Cloud Infrastructure Object Storage to store messages, you can use Oracle Cloud Infrastructure Native URIs or Swift URIs. However, the location URI and the credential must match in type as follows:

  • If you use a native URI format to access Oracle Cloud Infrastructure Object Storage, you must use Native Oracle Cloud Infrastructure Signing Keys authentication in the credential object.

  • If you use Swift URI format to access Oracle Cloud Infrastructure Object Storage, you must use an auth token authentication in the credential object.

Exceptions

Table - 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.

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.

Syntax

DBMS_PIPE.SEND_MESSAGE (
    pipename          IN VARCHAR2,
    timeout           IN INTEGER DEFAULT MAXWAIT,
    credential_name   IN VARCHAR2 DEFAULT null,
    location_uri      IN VARCHAR2 )
RETURN INTEGER;

Parameters

Table - SEND_MESSAGE Function Parameters

Parameter	Description
credential_name	The credential name for the cloud store used to store messages. The credential_name is a package argument that is by default initialized as NULL. You can set this value before calling DBMS_PIPE.SEND_MESSAGE. The passed parameter value takes precedence over the global variable's value. The credential object must have EXECUTE and READ/WRITE privileges by the user running DBMS_PIPE.SEND_MESSAGE.
location_uri	The location URI for the cloud store used to store messages. The location_uri is a global variable that by default is initialized as NULL. You can set this value before calling DBMS_PIPE.SEND_MESSAGE. The passed parameter value takes precedence over the global variable's value.
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.
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).

Return Values

Table - 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.
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.

Usage Notes

• Persistent messages are guaranteed to either be written or read by exactly one process. This prevents message content inconsistency due to concurrent writes and reads. Using a persistent messaging pipe, DBMS_PIPE allows only one operation, sending a message or a receiving message to be active at a given time. However, if an operation is not possible due to an ongoing operation, the process retries periodically until the timeout value is reached.

• If you use Oracle Cloud Infrastructure Object Storage to store messages, you can use Oracle Cloud Infrastructure Native URIs or Swift URIs. However, the location URI and the credential must match in type as follows:

  • If you use a native URI format to access Oracle Cloud Infrastructure Object Storage, you must use Native Oracle Cloud Infrastructure Signing Keys authentication in the credential object.

  • If you use Swift URI format to access Oracle Cloud Infrastructure Object Storage, you must use an auth token authentication in the credential object.

Exceptions

Table - 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.

SET_CREDENTIAL_NAME Procedure

This procedure sets the credential_name variable that is used as a default credential when pipe messages are stored in Cloud Object Store.

Syntax

DBMS_PIPE.SET_CREDENTIAL_NAME (
   credential_name   IN VARCHAR2 );

Parameters

Parameter	Description
credential_name	The name of the credential to access the Cloud Object Storage.

Usage Note

If you use Oracle Cloud Infrastructure Object Storage to store messages, you can use Oracle Cloud Infrastructure Native URIs or Swift URIs. However, the location URI and the credential must match in type as follows:

• If you use a native URI format to access Oracle Cloud Infrastructure Object Storage, you must use Native Oracle Cloud Infrastructure Signing Keys authentication in the credential object.

• If you use Swift URI format to access Oracle Cloud Infrastructure Object Storage, you must use an auth token authentication in the credential object.

Example

BEGIN
     DBMS_PIPE.SET_CREDENTIAL_NAME(
       credential_name =>  'my_cred1');
END;
/

SET_LOCATION_URI Procedure

This procedure sets the global location_uri variable.

Syntax

DBMS_PIPE.SET_LOCATION_URI (
   location_uri   IN VARCHAR2 );

Parameter

Parameter	Description
location_uri	Object or file URI. The format of the URI depends on the Cloud Object Storage service you are using, for details see Cloud Object Storage URI Formats.

Usage Note

If you use Oracle Cloud Infrastructure Object Storage to store messages, you can use Oracle Cloud Infrastructure Native URIs or Swift URIs. However, the location URI and the credential must match in type as follows:

• If you use a native URI format to access Oracle Cloud Infrastructure Object Storage, you must use Native Oracle Cloud Infrastructure Signing Keys authentication in the credential object.

• If you use Swift URI format to access Oracle Cloud Infrastructure Object Storage, you must use an auth token authentication in the credential object.

Example

BEGIN
  DBMS_PIPE.GET_LOCATION_URI(
      location_uri  => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname1/');
END;
/

Related Content

• Use Persistent Messaging with Messages Stored in Cloud Object Store