DBMS_PIPE - PolarDB - Alibaba Cloud Documentation Center

The built-in DBMS_PIPE package is used to transfer messages within or between sessions that are connected to the same cluster.

Usage notes

To prevent the occurrence of long-running transactions, we recommend that you do not use the DBMS_PIPE package for cluster endpoints.

Subprograms

Subprogram	Description
CREATE_PIPE Function	Creates a named pipeline.
NEXT_ITER_TYPE Function	Returns the data type of the next data item in the buffer.
PACK_MESSAGE Procedures	Places a data item in the local message buffer of a session.
UNPACK_MESSAGE Procedures	Accesses the next data item in the buffer.
PURGE Procedure	Cleans the contents of a named pipeline.
RECEIVE_MESSAGE Function	Copies a message from a named pipeline to the local buffer.
REMOVE_PIPE Function	Removes a named pipeline.
RESET_BUFFER Procedure	Cleans the contents of the local buffer.
SEND_MESSAGE Function	Sends a message to a named pipeline.
UNIQUE_SESSION_NAME Function	Returns the unique name of the current session.

CREATE_PIPE Function

This function is used to create a named pipeline.

Syntax

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

Parameters

Parameter	Description
pipename	The name of the pipeline.
maxpipesize	(Optional) The maximum capacity of the pipeline in bytes. Default value: 8192.
private	(Optional) Specifies whether to create a private pipeline. Valid values: TRUE (default): The created pipeline is a private pipeline. FALSE: The created pipeline is not a private pipeline.

Return values

Return value	Description
INTEGER	The status code returned for the pipeline creation. The value 0 indicates that the pipeline is successfully created.

Example

The following example shows how to create a named pipeline:

DECLARE
    status        INTEGER;
BEGIN
    status := DBMS_PIPE.CREATE_PIPE('messages');
    DBMS_OUTPUT.PUT_LINE('CREATE_PIPE status: ' || status);
END;

NEXT_ITER_TYPE Function

This function is used to return the data type of the next data item in the buffer.

Syntax

DBMS_PIPE.NEXT_ITEM_TYPE() RETURN INTEGER;

Return values

Return value	Description
INTEGER	The data type of the next data item in the buffer.

Example

The following example shows how to retrieve the type of the next data item in the buffer. In the following example, two sessions are created. One session is used to send messages to a pipeline and another session is used to receive messages.

SESSION A:

DECLARE
    str       VARCHAR2 := 'Character data';
    status    INTEGER;
BEGIN
    DBMS_PIPE.PACK_MESSAGE(str);

    -- SEND_MESSAGE status is: 0
    status := DBMS_PIPE.SEND_MESSAGE('messages');
    DBMS_OUTPUT.PUT_LINE('SEND_MESSAGE status is: ' || status);
END;

SESSION B:

DECLARE
    str      VARCHAR2;
    status   INTEGER;
BEGIN
    status := DBMS_PIPE.RECEIVE_MESSAGE('messages');
    
    -- RECEIVE_MESSAGE status is: 0
    DBMS_OUTPUT.PUT_LINE('RECEIVE_MESSAGE status is: ' || status);
    
    -- NEXT_ITEM_TYPE is: 9
    status := DBMS_PIPE.NEXT_ITEM_TYPE;
    DBMS_OUTPUT.PUT_LINE('NEXT_ITEM_TYPE is: ' || status);

    -- VARCHAR2 Item is: Character data
    DBMS_PIPE.UNPACK_MESSAGE(str);
    DBMS_OUTPUT.PUT_LINE('VARCHAR2 Item is: ' || str);
END;

PACK_MESSAGE Procedures

This stored procedure is used to pack a data item into the message buffer.

Syntax

DBMS_PIPE.PACK_MESSAGE (
   item  IN  VARCHAR2);

DBMS_PIPE.PACK_MESSAGE (
   item  IN  NUMBER);

DBMS_PIPE.PACK_MESSAGE (
   item  IN  DATE);

DBMS_PIPE.PACK_MESSAGE_RAW (
   item  IN  RAW);

Parameters

Parameter	Description
item	The data item that you want to pack into the local message buffer.

Example

The following example shows how to use this stored procedure to send different types of messages:

Note

The example shows only how to send a message. For more information about how to receive a message, see the example of the UNPACK_MESSAGE stored procedure.

DECLARE
    str      VARCHAR2 := 'Character data';
    num      NUMBER := '1024';
    status   INTEGER;
BEGIN
    DBMS_PIPE.PACK_MESSAGE(str);
    DBMS_PIPE.PACK_MESSAGE(num);
    
    -- send message
    status := DBMS_PIPE.SEND_MESSAGE('messages');
END;

UNPACK_MESSAGE Procedures

This stored procedure is used to access the next data item in the buffer.

Syntax

DBMS_PIPE.UNPACK_MESSAGE (
   item  OUT  VARCHAR2);

DBMS_PIPE.UNPACK_MESSAGE (
   item  OUT  NUMBER);

DBMS_PIPE.UNPACK_MESSAGE (
   item  OUT  DATE);

DBMS_PIPE.UNPACK_MESSAGE_RAW (
   item  OUT  RAW);

Parameters

Parameter	Description
item	The data item that you want to retrieve from the local message buffer.

Example

The following example shows how to receive a message from a pipeline:

DECLARE
    str      VARCHAR2;
    num      NUMBER;
    status   INTEGER;
BEGIN
    -- RECEIVE_MESSAGE status is: 0  
    status := DBMS_PIPE.RECEIVE_MESSAGE('messages');
    DBMS_OUTPUT.PUT_LINE('RECEIVE_MESSAGE status is: ' || status);

    -- VARCHAR2 item is: Character data
    DBMS_PIPE.UNPACK_MESSAGE(str);
    DBMS_OUTPUT.PUT_LINE('VARCHAR2 item is: ' || str);

    -- NUMBER item is: 1024
    DBMS_PIPE.UNPACK_MESSAGE(num);
    DBMS_OUTPUT.PUT_LINE('NUMBER item is: ' || num);
END;

PURGE Procedure

This stored procedure is used to clean the contents of a named pipeline and receive contents from the buffer.

Syntax

DBMS_PIPE.PURGE (
   pipename  IN  VARCHAR2);

Parameters

Parameter	Description
pipename	The name of the pipeline whose contents you want to clean.

Example

This example shows how to clean the contents of a named pipeline.

CALL DBMS_PIPE.PURGE('messages');

RECEIVE_MESSAGE Function

This function is used to copy a message from a named pipeline to the local buffer.

Syntax

DBMS_PIPE.RECEIVE_MESSAGE (
   pipename     IN VARCHAR2,
   timeout      IN INTEGER DEFAULT 86400)
RETURN INTEGER;

Parameters

Parameter	Description
pipename	The name of the pipeline whose message you want to copy.
timeout	(Optional) The timeout period during which you wait for a message. Unit: seconds. Default value: 86400.

Return values

Return value	Description
INTEGER	The returned status code.

Example

The following example shows how to copy a message from a pipeline to the buffer:

DECLARE
    varchar       VARCHAR2 := 'Character data';
    status        INTEGER;
    result        VARCHAR2;
    idx           INTEGER;
BEGIN
    DBMS_PIPE.PACK_MESSAGE(varchar);
    DBMS_PIPE.PACK_MESSAGE(varchar);
    DBMS_PIPE.PACK_MESSAGE(varchar);
    
    -- SEND_MESSAGE status is: 0
    status := DBMS_PIPE.SEND_MESSAGE('messages');
    DBMS_OUTPUT.PUT_LINE('SEND_MESSAGE status is: ' || status);
    
    status := DBMS_PIPE.RECEIVE_MESSAGE('messages');

    -- Character data
    -- Character data
    -- Character data
    FOR idx in 1..3 LOOP
      DBMS_PIPE.UNPACK_MESSAGE(result);
      DBMS_OUTPUT.PUT_LINE(result);
    END LOOP;
END;

REMOVE_PIPE Function

This function is used to remove a named pipeline.

Syntax

DBMS_PIPE.REMOVE_PIPE (
   pipename  IN  VARCHAR2)
RETURN INTEGER;

Parameters

Parameter	Description
pipename	The name of the pipeline that you want to remove.

Return values

Return value	Description
INTEGER	The returned status code.

Example

The following example shows how to remove a named pipeline:

SELECT DBMS_PIPE.REMOVE_PIPE('messages') FROM DUAL;
 remove_pipe 
-------------
           0
(1 row)

RESET_BUFFER Procedure

This stored procedure is used to clean the contents of the buffer in which a message is stored before the message is sent.

Syntax

DBMS_PIPE.RESET_BUFFER;

Example

The following example shows how to clean the contents of the local buffer:

CALL DBMS_PIPE.RESET_BUFFER;

SEND_MESSAGE Function

This function is used to send a message to a named pipeline.

Syntax

DBMS_PIPE.SEND_MESSAGE (
    pipename     IN VARCHAR2,
    timeout      IN INTEGER DEFAULT 86400,
    maxpipesize  IN INTEGER DEFAULT 8192)
  RETURN INTEGER;

Parameters

Parameter	Description
pipename	The pipeline name.
timeout	(Optional) The waiting period. Unit: seconds. Default value: 86400.
maxpipesize	(Optional) The maximum capacity of the pipeline in bytes. Default value: 8192.

Return values

Return value	Description
INTEGER	The returned status code.

Example

This example shows how to send local data to a pipeline:

DECLARE
    varchar       VARCHAR2 := 'Character data';
    status        INTEGER;
    result        VARCHAR2;
    idx           INTEGER;
BEGIN
    DBMS_PIPE.PACK_MESSAGE(varchar);
    DBMS_PIPE.PACK_MESSAGE(varchar);
    DBMS_PIPE.PACK_MESSAGE(varchar);
    
    -- SEND_MESSAGE status is: 0
    status := DBMS_PIPE.SEND_MESSAGE('messages');
    DBMS_OUTPUT.PUT_LINE('SEND_MESSAGE status is: ' || status);
    
    status := DBMS_PIPE.RECEIVE_MESSAGE('messages');

    -- Character data
    -- Character data
    -- Character data
    FOR idx in 1..3 LOOP
      DBMS_PIPE.UNPACK_MESSAGE(result);
      DBMS_OUTPUT.PUT_LINE(result);
    END LOOP;
END;

UNIQUE_SESSION_NAME Function

This function is used to return the unique name of the current session.

Syntax

DBMS_PIPE.UNIQUE_SESSION_NAME 
  RETURN VARCHAR2;

Return values

Return value	Description
VARCHAR2	The unique name of the current session.

Example

The following example shows how to retrieve the unique name of the current session:

SELECT DBMS_PIPE.UNIQUE_SESSION_NAME() FROM dual;
  unique_session_name  
-----------------------
 POLARDB$PIPE$12$57884
(1 row)