Oracle Cloud Infrastructure Documentation

DBMS_PIPE Package

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

Oracle Autonomous Database 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.

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 6-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
  • 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: 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 6-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 6-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 6-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;
/

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

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

DBMS_PIPE Overview for Persistent Messaging 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 persistent messaging pipes.

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

See Choose a Character Set for Autonomous Database for more information.

Parent topic: DBMS_PIPE Package

Summary of DBMS_PIPE Subprograms for Persistent Messaging

This table lists the DBMS_PIPE subprograms and briefly describes them.

Table 6-11 DBMS_PIPE Package Subprograms

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

Returns datatype of next item in buffer.

PACK_MESSAGE Procedures

Builds message in local buffer.

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

Returns unique session name.

UNPACK_MESSAGE Procedures

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.
  • GET_CREDENTIAL_NAME Function
    This function returns the global credential_name variable value for use when messages are stored to Cloud Object Store.
  • 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.
  • 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.
  • 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.
  • SET_LOCATION_URI Procedure
    This procedure sets the global location_uri variable.

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.

Syntax

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

Parameters

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

Return Values

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

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 6-14 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 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 6-15 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.

The credential_name value can be an OCI resource principal, Azure service principal, Amazon Resource Name (ARN), or a Google service account. See Configure Policies and Roles to Access Resources for more information on resource principal based authentication.

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 6-16 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 6-17 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 6-18 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.

The credential_name value can be an OCI resource principal, Azure service principal, Amazon Resource Name (ARN), or a Google service account. See Configure Policies and Roles to Access Resources for more information on resource principal based authentication.

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 6-19 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 6-20 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.

The credential_name value can be an OCI resource principal, Azure service principal, Amazon Resource Name (ARN), or a Google service account. See Configure Policies and Roles to Access Resources for more information on resource principal based authentication.

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