dbms_pipe

DBMS_PIPE

DBMS_PIPE包允许同一实例中的两个或多个会话通信。Oracle管道在概念上与UNIX中使用的管道相似，但Oracle管道不是使用操作系统管道机制实现的。

Oracle官方文档:
https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_PIPE.html

管道功能有几个可用的应用领域:
外部服务接口
独立事务
alerts(非事务性)
调试和集中器。

根据您的安全需求，您可以选择使用公共管道或私有管道．

1)、公有管道：所有数据库用户可访问。
2)、私有管道：只能建立管道的数据库用户可访问。

DBMS_PIPE常量

这是尝试发送或接收消息时等待的最长时间。

maxwait   constant integer := 86400000; /* 1000 days */ 

DBMS_PIPE操作说明

通过Oracle管道发送的信息在系统全局区域(SGA)中进行缓冲。当实例关闭时，管道中的所有信息都将丢失。

警告:　管道独立于事务。当事务控制可能受到影响时，要小心使用管道

DBMS_PIPE的操作涉及以下主题:

• 公共管道(Public Pipes)

• 写和读管道(Writing and Reading Pipes)

• 私有管道(Private Pipes)

1. 公共管道(Public Pipes)

您可以隐式或显式地创建公共管道。对于隐式公共管道，管道在第一次被引用时自动创建，当它不再包含数据时消失。因为管道描述符存储在SGA中，所以在空管道从缓存中aged out之前会有一些空间使用开销。

通过调用CREATE_PIPE函数并将private flag设置为FALSE，可以创建显式公共管道。必须通过调用REMOVE_PIPE函数来释放显式创建的管道。

无论是显式创建的还是隐式创建的，公共管道的domain是创建该管道的schema。

2.　写和读管道(Writing and Reading Pipes)

每个公共管道都异步工作。Any number of schema users都可以写入公共管道，只要他们对DBMS_PIPE包有EXECUTE权限，并且知道公共管道的名称。但是，一旦一个用户读取了缓冲的信息，它就会从缓冲区中清空，并且不能用于同一管道的其他读取。

发送会话使用对PACK_MESSAGE过程的一次或多次调用来构建消息。此过程将消息添加到会话的本地消息缓冲区。该缓冲区中的信息通过调用SEND_MESSAGE函数发送，并指定用于发送消息的管道名称。当SEND_MESSAGE被调用时，所有已经在本地缓冲区中堆叠的消息都会被发送。

想要接收消息的进程调用RECEIVE_MESSAGE函数，指定从中接收消息的管道名称。然后，该进程调用UNPACK_MESSAGE过程来访问消息中的每个项。

3. 私有管道(Private Pipes)

通过调用CREATE_PIPE函数显式地创建一个私有管道。私有管道一旦创建，就会一直保存在共享内存中，直到通过调用REMOVE_PIPE函数显式地释放它。当数据库实例关闭时，私有管道也会被释放。

如果内存中存在隐式管道，并且该管道与您试图创建的私有管道具有相同的名称，则无法创建私有管道。在这种情况下，CREATE_PIPE返回一个错误。

对私有管道的访问被限制为:

以与管道创建者相同的用户id运行的会话

与管道创建者在同一用户id特权域中执行的存储子程序

以SYSDBA身份连接的用户

任何其他用户在管道上发送或接收消息或删除管道的尝试都会立即导致错误。其他用户尝试创建具有相同名称的管道也会导致错误。

与公共管道一样，在调用SEND_MESSAGE之前，必须首先使用对PACK_MESSAGE的调用来构建消息。类似地，在通过调用UNPACK_MESSAGE访问消息中的项之前，必须调用RECEIVE_MESSAGE来检索消息。

DBMS_PIPE Exceptions

DBMS_PIPE包子程序可以返回下表中列出的错误。

ORA-23321:

Pipename may not be null. This can be returned by the CREATE_PIPE function, or any subprogram that takes a pipe name as a parameter.

ORA-23322:

Insufficient privilege to access pipe. This can be returned by any subprogram that references a private pipe in its parameter list.

DBMS_PIPE Examples

这个例子展示了PL/SQL程序可以调用的将调试信息放置在管道中的过程。

CREATE OR REPLACE PROCEDURE debug (msg VARCHAR2) AS
    status  NUMBER;
BEGIN
  DBMS_PIPE.PACK_MESSAGE(LENGTH(msg));
  DBMS_PIPE.PACK_MESSAGE(msg);
  status := DBMS_PIPE.SEND_MESSAGE('plsql_debug');
  IF status != 0 THEN
    raise_application_error(-20099, 'Debug error');
  END IF;
END debug;

Summary of DBMS_PIPE Subprograms

该表列出了DBMS_PIPE子程序并简要描述了它们。

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
REMOVE_PIPE Function	Removes the named pipe
RESET_BUFFER Procedure	Purges contents of local buffer
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

1. CREATE_PIPE Function

这个函数显式地创建一个公共或私有管道。如果private标志为TRUE，则管道创建者被指定为私有管道的所有者。

只能通过调用REMOVE_PIPE或关闭实例来删除显式创建的管道。

语法

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

参数

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 8192 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.
private	Uses the default, TRUE, to create a private pipe.Public pipes can be implicitly created when you call SEND_MESSAGE.

返回值

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.If a user connected as SYSDBA/SYSOPER re-creates a pipe, then Oracle returns status 0, but the ownership of the pipe remains unchanged.
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.

异常

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

2. NEXT_ITEM_TYPE Function

此函数确定本地消息缓冲区中下一项的数据类型。

在调用RECEIVE_MESSAGE将管道信息放入本地缓冲区之后，调用NEXT_ITEM_TYPE。

语法

DBMS_PIPE.NEXT_ITEM_TYPE 
  RETURN INTEGER; 

返回值

Return	Description
0	No more items
6	NUMBER
9	VARCHAR2
11	ROWID
12	DATE
23	RAW

3. PACK_MESSAGE Procedures

在本地消息缓冲区中构建消息。

要发送消息，首先对PACK_MESSAGE进行一次或多次调用。然后，调用SEND_MESSAGE在命名管道上的本地缓冲区中发送消息。

该过程被重载以接受VARCHAR2、NCHAR、NUMBER、DATE，RAW和ROWID类型。除了数据字节之外，缓冲区中的每个项还需要一个字节来表示其类型，两个字节来存储其长度。需要一个额外的字节来终止消息。除了VARCHAR以外的所有类型的开销都是4字节。

语法

DBMS_PIPE.PACK_MESSAGE (
   item  IN  VARCHAR2);

DBMS_PIPE.PACK_MESSAGE (
   item  IN  NCHAR);

DBMS_PIPE.PACK_MESSAGE (
   item  IN  NUMBER);

DBMS_PIPE.PACK_MESSAGE (
   item  IN  DATE);

DBMS_PIPE.PACK_MESSAGE_RAW (
   item  IN  RAW);

DBMS_PIPE.PACK_MESSAGE_ROWID (
   item  IN  ROWID);

当您调用SEND_MESSAGE来发送此消息时，必须指出要在其上发送消息的管道的名称。如果这个管道已经存在，那么您必须有足够的权限来访问这个管道。如果管道不存在，则会自动创建它。

异常

如果消息缓冲区溢出(当前为4096字节)，将引发ORA-06558。缓冲区中的每个项都用一个字节表示类型，两个字节表示长度，再加上实际数据。还需要一个字节来终止消息。

4. PURGE Procedure

此过程将清空命名管道的内容。

根据最近最少使用的算法，将隐式创建的空管道从共享全局区域中aged out。因此，调用PURGE可以释放与隐式创建的管道相关的内存。

语法

DBMS_PIPE.PURGE (
   pipename  IN  VARCHAR2);

参数

| Parameter  | Description                                                  |
| :--------- | :----------------------------------------------------------- |
| `pipename` | Name of pipe from which to remove all messages.The local buffer may be overwritten with messages as they are discarded. Pipename should not be longer than 128 bytes, and is case-insensitive. |

因为PURGE调用了RECEIVE_MESSAGE，所以在从管道中清除消息时，本地缓冲区可能会被消息覆盖。此外，如果您试图清除您没有足够访问权限的管道，您可能会收到ORA-23322(特权不足)错误。

异常

如果管道属于其他用户，则权限错误。

5. RECEIVE_MESSAGE Function

这个函数将消息复制到本地消息缓冲区中。

语法

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

参数

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.The default value is the constant MAXWAIT, which is defined as 86400000 (1000 days). A timeout of 0 lets you read without blocking.

返回值

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. (This should not happen.)
3	An interrupt occurred.
ORA-23322	User has insufficient privileges to read from the pipe.

要从管道接收消息，首先调用RECEIVE_MESSAGE。当你收到一条消息时，它被从管道中移除;因此，一条消息只能被接收一次。对于隐式创建的管道，将在从管道中删除最后一条记录后删除管道。

如果调用RECEIVE_MESSAGE时指定的管道不存在，那么Oracle将隐式创建该管道并等待接收消息。如果消息没有在指定的超时间隔内到达，则调用返回并删除管道。

收到消息后，必须对UNPACK_MESSAGE进行一次或多次调用，以访问消息中的各个项目。UNPACK_MESSAGE过程被重载以解包DATE、NUMBER、VARCHAR2类型的项目，另外还有两个过程用于解包RAW和ROWID项目。如果不知道要解包的数据类型，则调用NEXT_ITEM_TYPE来确定缓冲区中下一项的类型。

异常

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

6. RESET_BUFFER Procedure

将PACK_MESSAGE和UNPACK_MESSAGE重置为0。

因为所有管道都共享一个缓冲区，所以会发现在使用新管道之前重置缓冲区非常有用。这确保了第一次尝试向管道发送消息时，不会无意中发送缓冲区中剩余的过期消息。

语法

DBMS_PIPE.RESET_BUFFER; 

7. REMOVE_PIPE Function

这个函数删除显式创建的管道。

由SEND_MESSAGE隐式创建的管道在为空时自动删除。然而，由CREATE_PIPE显式创建的管道只能通过调用REMOVE_PIPE或关闭实例来删除。在删除管道之前，将删除管道中所有未使用的记录。

这类似于在隐式创建的管道上调用PURGE。

语法

DBMS_PIPE.REMOVE_PIPE (
   pipename  IN  VARCHAR2)
RETURN INTEGER;

参数

Parameter	Description
pipename	Name of pipe that you want to remove.

返回值

Return	Description
0	SuccessIf the pipe does not exist, or if the pipe already exists and the user attempting to remove it is authorized to do so, then Oracle returns 0, indicating success, and any data remaining in the pipe is removed.
ORA-23322	Insufficient privileges.If the pipe exists, but the user is not authorized to access the pipe, then Oracle signals error ORA-23322, indicating insufficient privileges.

8. SEND_MESSAGE Function

这个函数在命名管道上发送消息。

消息包含在本地消息缓冲区中，该缓冲区由对PACK_MESSAGE的调用填充。您可以使用CREATE_PIPE显式地创建管道，否则将隐式地创建管道。

语法

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

参数

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

返回值

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.

9. UNIQUE_SESSION_NAME Function

此函数接收一个名称，该名称在当前连接到数据库的所有会话中是唯一的。

同一会话中对该函数的多次调用总是返回相同的值。您可能会发现使用这个函数为SEND_MESSAGE和RECEIVE_MESSAGE调用提供PIPENAME参数是很有用的。

语法

DBMS_PIPE.UNIQUE_SESSION_NAME 
  RETURN VARCHAR2;

返回值

这个函数返回一个唯一的名称。返回的名称最大可达30字节。

10. UNPACK_MESSAGE Procedures

这个过程从缓冲区中检索项。

在调用RECEIVE_MESSAGE将管道信息放入本地缓冲区之后，调用UNPACK_MESSAGE。

UNPACK_MESSAGE过程被重载以返回VARCHAR2、NCHAR、NUMBER或DATE类型的项。还有两个额外的过程来解包RAW和ROWID项目。

DBMS_PIPE.UNPACK_MESSAGE (
   item  OUT VARCHAR2);

DBMS_PIPE.UNPACK_MESSAGE (
   item  OUT NCHAR);

DBMS_PIPE.UNPACK_MESSAGE (
   item  OUT NUMBER);

DBMS_PIPE.UNPACK_MESSAGE (
   item  OUT DATE);

DBMS_PIPE.UNPACK_MESSAGE_RAW (
   item  OUT RAW);

DBMS_PIPE.UNPACK_MESSAGE_ROWID (
   item  OUT ROWID);

异常

ORA-06556或06559是在缓冲区不包含任何项目，或者项目与请求的类型不同的情况下生成的。