#

| Chapter 3 |

# 3. Intersession Communication

Contents:
DBMS_PIPE: Communicating Between Sessions
DBMS_ALERT: Broadcasting Alerts to Users

It seems that everything these days has something to do with communication. Messaging technologies are consequently receiving lots of attention, but they are nothing new for Oracle. Through the packages described in this chapter, Oracle has already provided mechanisms for communication between database sessions:

DBMS_PIPE

  Using database pipes with DBMS_PIPE, an application can communicate with a service routine external to the database. Or, debuggers that capture PL/SQL errors can utilize the fact that DBMS_PIPE is asynchronous with database transactions, getting the errors logged whether the transaction issued a COMMIT or a ROLLBACK.

DBMS_ALERT

  This package is a little different, in that it allows synchronous notification to multiple users that specific database events have occurred.

## 3.1 DBMS_PIPE: Communicating Between Sessions

The DBMS_PIPE package provides services that allow Oracle sessions connected to the same instance to communicate messages with each other without the need for a COMMIT. Sessions use DBMS_PIPE programs to pack data into a message buffer and then send the message to a memory area in the Oracle shared pool (the pipe), where another session can receive it and unpack the message data into local variables. The database pipes implemented by DBMS_PIPE are roughly modeled after UNIX pipes. Pipes may be private to the user or public, and can be written to, or read from, independent of database transactions.

The basic functionality that DBMS_PIPE introduces is the ability for Oracle sessions to communicate with each other. Before the existence of database pipes, users connected to an Oracle database could communicate or interact with each other only through the database. If users needed to somehow exchange information with each other, this had to be done by reading, writing, and committing data to tables (i.e., the database was itself the communications medium). This communications model suffers from the following problems:

- It is transactional; it relies on COMMIT for users to see messages.

- Communications are slow; they involve physical writing to disk.

- There is limited capacity due to locking issues in message tables.

- There are space management issues.

The database pipes introduced through the DBMS_PIPE package establish a fast, lightweight, memory-based, nontransactional mechanism of intersession communications.

The DBMS_PIPE package is most often used to provide an interface between sessions connected to an Oracle instance and service routines external to Oracle in the host operating environment. In this kind of application, the service routine connects to Oracle and listens for service requests on a specific database pipe. Sessions in the database request services by placing messages on the request pipe, and they receive data from the service routine on session-specific response pipes.

Other applications you might consider developing with DBMS_PIPE include:

- Debuggers that place error messages into pipes

- Auditing of security violations independent of transaction success

- Transaction concentrators to multiplex many user transactions through a single session

- Complex calculation servers to offload long or memory-intensive computations

- Alerters that notify sessions of important events

An example of the last application has actually been built by Oracle as the DBMS_ALERT package (described later in this chapter), which makes extensive use of DBMS_PIPE programs to implement database alerts.

### 3.1.1 Getting Started with DBMS_PIPE

The DBMS_PIPE package is created when the Oracle database is installed. The dbmspipe.sql script (found in the built-in packages source code directory, as described in Chapter 1, Introduction) contain the source code for this package's specification. This script is called by catproc.sql, which is normally run immediately after database creation. The script creates the public synonym DBMS_PIPE for the package. Under Oracle7, no privileges are automatically granted on DBMS_PIPE. Under Oracle8, the EXECUTE_CATALOG_ROLE role is granted EXECUTE privilege on DBMS_PIPE. Thus, the DBMS_PIPE programs are not generally available to users.

#### 3.1.1.1 DBMS_PIPE programs

Table 3.1 lists the programs included in the DBMS_PIPE package.

Table 3.1: DBMS_PIPE Programs
Name | Description | Use in SQL?
--- | --- | ---
CREATE_PIPE | Creates a public or private pipe | Yes
NEXT_ITEM_TYPE | Returns datatype of next item in message buffer | Yes
PACK_MESSAGE | Packs item into message buffer | No
PACK_MESSAGE_RAW | Packs RAW item into message buffer | No
PACK_MESSAGE_ROWID | Packs ROWID item into message buffer | No
PURGE | Empties named pipe of all messages | No
RECEIVE_MESSAGE | Receives message from pipe into local buffer | Yes
REMOVE_PIPE | Removes the named pipe | Yes
RESET_BUFFER | Resets buffer message pointers | No
SEND_MESSAGE | Sends local message buffer out on pipe | Yes
UNIQUE_SESSION_NAME | Returns string unique to the session | Yes
UNPACK_MESSAGE | Unpacks item from message buffer | No
UNPACK_MESSAGE_RAW | Unpacks RAW item from message buffer | No
UNPACK_MESSAGE_ROWID | Unpacks ROWID item from message buffer | No

DBMS_PIPE does not declare any package exceptions of its own. Many of the individual programs raise Oracle exceptions under certain circumstances, as described in the following sections.

#### 3.1.1.2 DBMS_PIPE nonprogram elements

The DBMS_PIPE package contains one nonprogram element, maxwait. It is defined as follows:

>     maxwait CONSTANT INTEGER := 86400000;

The maxwait constant is used as the default maximum time to wait for calls to the SEND_MESSAGE or RECEIVE_MESSAGE functions to complete. The units are in seconds, so the value of 86400000 equates to 1000 days.

### 3.1.2 How Database Pipes Work

It is important to understand how DBMS_PIPE implements the concept of a communications pipe between Oracle sessions -- and this implementation is not necessarily obvious.

#### 3.1.2.1 Memory structures

The pipes themselves are named memory areas in the Oracle SGA's shared pool where communications messages can be written or read. DBMS_PIPE works through the interaction of these memory areas with a private memory buffer in each user's session. There is only one private memory buffer per user that is used to send and receive messages on database pipes. Private buffers can be thought of as "mailboxes" -- one for each user -- and the database pipes are like the "post office." The difference is that users are responsible for delivering and retrieving messages to and from the post office.[1]

> [1] Dan Clamage (technical reviewer extraordinaire) points out that the single-session message buffer is actually implemented as a private global variable in the DBMS_PIPE package body. He laments, and I join him, that Oracle did not implement the ability to declare several message buffers of your own.

#### 3.1.2.2 Nontransactional communications

One very important property of the DBMS_PIPE programs is that they are nontransactional. This means that they are not bound to the current database transaction, and they will succeed or fail independently of any COMMIT or ROLLBACK processing. Transaction independence is one reason why DBMS_PIPE is often used to implement debugging software, since problems in uncommitted transactions can still be logged into database pipes.

#### 3.1.2.3 Pipe communications logic

The basic sequence of events for DBMS_PIPE-based communications, follows:

- The sending user loads his private buffer with a "message," which can be composed of multiple items of various datatypes. This is done via successive calls to the PACK_MESSAGE procedure.

- The sending user moves the message from the private buffer into the pipe with the SEND_MESSAGE function.

- The receiving user pulls the message off the pipe into his private buffer using the RECEIVE_MESSAGE function.

- The receiving user "unpacks" the message items into local variables using the UNPACK_MESSAGE procedure.

Figure 3.1 illustrates the architecture and basic logic of pipe-based communications.

#### Figure 3.1: . Sending messages between sessions through a database pipe

The post office analogy helps me keep in mind how DBMS_PIPE works. Think of the message as something physical (such as a postcard or letter) that moves from one user buffer (mailbox) into the pipe (post office), and then out of the pipe and into another session buffer (mailbox). The pipe itself acts like a first-in-first-out (FIFO) queue -- that is, messages are extracted in the order in which they are put in the queue. This understanding helps clear up the following common points of confusion:

Q: Is the message still in my buffer after calling up SEND_MESSAGE?

A: No, it physically left your session and went into the pipe.

Q: Can two users pull the same message from a pipe using RECEIVE_MESSAGE?

A: No, the first user to make the call has physically removed the message from the pipe.

Q: Can a user pull a specific message from a pipe using RECEIVE_MESSAGE?

A: No, the pipe will always return the next message in the queue.

> NOTE: Note that Oracle8's Advanced Queuing features, covered in Chapter 5, Oracle Advanced Queuing, offer more sophisticated and robust messaging capabilities, which may be used instead of database pipes for more complex applications.

### 3.1.3 Managing Pipes and the Message Buffer

Use DBMS_PIPE's CREATE_PIPE, REMOVE_PIPE, RESET_PIPE, RESET_BUFFER, PURGE, and UNIQUE_SESSION_NAME programs to create and remove pipes and to perform some additional pipe management operations.

#### 3.1.3.1 The DBMS_PIPE.CREATE_PIPE function

The CREATE_PIPE function is used to create a new public or private named database pipe. Note that database pipes can also be created implicitly by the SEND_MESSAGE function. Here's the header for this function:

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

Parameters are summarized in the following table.

Name | Description
--- | ---
pipename | Name of the database pipe
maxpipesize | Maximum size in bytes of the pipe
private | TRUE means pipe is private to user

#### 3.1.3.1.1 Return values

The CREATE_PIPE procedure has a single return value of 0, indicating success. This value is returned even if the pipe already existed and can be used by the user.

#### 3.1.3.1.2 Exceptions

The program does not raise any package exceptions. The following Oracle exceptions are raised if the user attempts to create a pipe that already exists and is private to another user or uses a NULL pipename:

Number | Description
--- | ---
ORA-23322 | Insufficient privileges to access pipe
ORA-23321 | Pipename may not be NULL

#### 3.1.3.1.3 Restrictions

Note the following restrictions on calling CREATE_PIPE:

- Pipenames are limited to 128 byes in length, are case-insensitive, and cannot contain NLS characters.

- Pipenames must not begin with "ORA$", as these names are reserved for use by Oracle Corporation.

#### 3.1.3.1.4 Example

This example is a function that encapsulates CREATE_PIPE and returns either the Boolean value TRUE, indicating that the pipe can be used by the caller, or FALSE otherwise. The function traps the ORA-23322 exception using PRAGMA EXCEPTION_INIT and returns FALSE if this exception is raised.

The makepipe function can be found in the dbpipe package discussed in the "Section 3.1.7, "DBMS_PIPE Examples"" section. It is created by the dbpipe.sql script.

>     /* Filename on companion disk:  dbpipe.sql */*
>     PACKAGE BODY dbpipe
>     IS
>        cannot_use_pipe   EXCEPTION;
>        PRAGMA EXCEPTION_INIT(cannot_use_pipe,-23322);
>        null_pipename   EXCEPTION;
>        PRAGMA EXCEPTION_INIT(null_pipename,-23321);

>        /*
>        || encapsulates DBMS_PIPE.CREATE_PIPE and returns
>        || FALSE if ORA-23322 is raised indicating
>        || the pipename is already used and not accessible
>        || to the caller
>        */
>        FUNCTION makepipe
>           (pipename_IN IN VARCHAR2
>           ,maxsize_bytes_IN IN INTEGER DEFAULT 8192
>           ,private_IN IN BOOLEAN DEFAULT TRUE)
>        RETURN BOOLEAN
>        IS
>           call_status  INTEGER;

>        BEGIN
>           call_status := DBMS_PIPE.CREATE_PIPE
>                             (pipename_IN
>                             ,maxsize_bytes_IN
>                             ,private_IN);
>           RETURN call_status = 0;
>        EXCEPTION
>           WHEN cannot_use_pipe OR null_pipename
>           THEN
>              RETURN FALSE;
>        END makepipe;

>     END dbpipe;

The CREATE_PIPE function creates a private pipe by default. Private pipes may be used only by sessions connected to the same username (schema) as the pipe's creator or executing stored PL/SQL programs owned by that schema. Public pipes may be accessed by all sessions with execute privileges on DBMS_PIPE. Note that CREATE_PIPE is the only way to create a private database pipe. Pipes created implicitly by the SEND_MESSAGE function are always public.

Pipes created using CREATE_PIPE should be explicitly removed using the REMOVE_PIPE function.

Database pipes are empty upon creation. However, if the named database pipe already exists and is available to the user calling CREATE_PIPE, the function will return 0, but the pipe is not emptied. Avoid writing code that assumes that a successful call to CREATE_PIPE results in an empty pipe.

The maxpipesize parameter of CREATE_PIPE determines the maximum size in memory of the database pipe. This places a limit both on the amount of Oracle shared pool memory and on the maximum size of all messages the pipe can hold at any time. When designing applications that use database pipes, it is important to estimate the number and the size of the messages that the pipe will need to contain, so that maxpipesize can be determined. Basically, the objective is to size the pipe as small as possible while making sure that there is plenty of room to handle anticipated message traffic. Note that after creation, a pipe's maximum size can be increased using the SEND_MESSAGE function or by destroying and recreating the pipe.

#### 3.1.3.2 The DBMS_PIPE.REMOVE_PIPE function

The REMOVE_PIPE function is used to destroy a database pipe and free the memory used by the pipe back to the Oracle shared pool. The header for this program is:

>     FUNCTION DBMS_PIPE.REMOVE_PIPE
>         (pipename IN VARCHAR2)
>     RETURN INTEGER;

where the pipename is the name of the database pipe to be removed.

#### 3.1.3.2.1 Return values

The REMOVE_PIPE procedure has a single return value of 0, indicating success. This value is returned even if the pipe did not exist.

#### 3.1.3.2.2 Exceptions

The program does not raise any package exceptions. The following Oracle exceptions are raised if the user attempts to remove a pipe belonging to another user or passes a NULL pipename:

Number | Description
--- | ---
ORA-23322 | Insufficient privileges to access pipe
ORA-23321 | Pipename may not be NULL

#### 3.1.3.2.3 Restrictions

Pipenames must not begin with "ORA$" as these names are reserved for use by Oracle Corporation.

#### 3.1.3.2.4 Example

This example is a function that encapsulates REMOVE_PIPE and returns the Boolean value TRUE indicating that the pipe was successfully removed (or did not exist) or FALSE indicating that the pipe exists but cannot be removed by the caller. The function traps the ORA-23322 error using PRAGMA EXCEPTION_INIT and returns FALSE if this exception is raised.

The closepipe function can be found in the dbpipe package discussed in the "Section 3.1.7" section and defined in the dbpipe.sql script.

>     /* Filename on companion disk:  dbpipe.sql */*
>     PACKAGE BODY dbpipe
>     IS
>        cannot_use_pipe   EXCEPTION;
>        PRAGMA EXCEPTION_INIT(cannot_use_pipe,-23322);
>        null_pipename   EXCEPTION;
>        PRAGMA EXCEPTION_INIT(null_pipename,-23321);
>        /*
>        || encapsulates DBMS_PIPE.REMOVE_PIPE and returns
>        || FALSE if ORA-23322 is raised indicating
>        || the pipename exists and is not removable
>        || by the caller
>        */
>        FUNCTION closepipe
>           (pipename_IN IN VARCHAR2)
>        RETURN BOOLEAN
>        IS
>           call_status  INTEGER;

>        BEGIN
>           call_status := DBMS_PIPE.REMOVE_PIPE(pipename_IN);
>           RETURN (call_status = 0);

>        EXCEPTION
>           WHEN cannot_use_pipe OR null_pipename
>           THEN
>              RETURN FALSE;
>        END closepipe;

>     END dbpipe;

> NOTE: It is good practice to remove pipes explicitly when they are no longer needed. This can sometimes be difficult, since database pipes are typically shared by multiple sessions, so it is hard to know when they can be removed. Empty pipes that have not been removed will eventually be aged out of the shared pool by Oracle.

#### 3.1.3.3 The DBMS_PIPE.RESET_BUFFER procedure

The DBMS_PIPE.RESET_BUFFER procedure resets the session message buffer's internal pack and unpack indicators, effectively discarding the contents of the buffer. The header for this procedure follows:

>     PROCEDURE DBMS_PIPE.RESET_BUFFER;

The program does not raise any package exceptions.

#### 3.1.3.3.1 Example

This example shows the use of RESET_BUFFER at the beginning of a program that packs a PL/SQL record into a message.

The pack_send_request procedure can be found in the pipesvr package discussed in the "Section 3.1.7" section. The following code has been excerpted from that example package:

>     /* Filename on companion disk: pipesvr.sql */*
>     PROCEDURE pack_send_request
>        (request_rec_IN IN request_rectype
>        ,return_code_OUT OUT NUMBER)
>     IS
>     BEGIN
>        /* discard any previous unsent message items */
>        DBMS_PIPE.RESET_BUFFER;

>        /* pack message in standard order */
>        DBMS_PIPE.PACK_MESSAGE(request_protocol);
>        DBMS_PIPE.PACK_MESSAGE(request_rec_IN.response_pipe);
>        DBMS_PIPE.PACK_MESSAGE(request_rec_IN.service);

>        /*
>        || send message to request pipe nowait
>        */
>        return_code_OUT := DBMS_PIPE.SEND_MESSAGE
>                             (pipename => request_pipe
>                             ,timeout  => 0);
>     END pack_send_request;

Oracle advises that the RESET_BUFFER procedure should not generally be needed. I make sure, however, to use it in the following places:

- Exception handlers of programs using UNPACK_MESSAGE procedures

- At the beginning of programs designed to pack specific messages into the buffer using PACK_MESSAGE procedures

In handling unpack exceptions, it is safe practice to initialize the message buffer after an unexpected item type is encountered. When packing messages, it is important to be sure that only the intended message items are packed into the buffer. By resetting the message buffer, programs can protect themselves from sending any previously packed but unsent message items.

#### 3.1.3.4 The DBMS_PIPE.PURGE procedure

The PURGE procedure empties the named pipe of all messages. The header for this procedure is,

>     PROCEDURE DBMS_PIPE.PURGE
>        (pipename IN VARCHAR2);

where the pipename parameter is the name of the database pipe to be emptied.

#### 3.1.3.4.1 Exceptions

The program does not raise any package exceptions. The following Oracle exceptions are raised if the user attempts to purge a pipe belonging to another user or passes a NULL pipename:

Number | Description
--- | ---
ORA-23322 | Insufficient privileges to access pipe
ORA-23321 | Pipename may not be NULL

#### 3.1.3.4.2 Restrictions

Note the following restrictions on calling PURGE:

- Pipenames are limited to 128 bytes in length, are case-insensitive, and cannot contain NLS characters.

- Pipenames must not begin with "ORA$", as these names are reserved for use by Oracle Corporation.

#### 3.1.3.4.3 Example

This example shows a procedure that will purge all pipes to which the calling user has access. The purge_all_pipes procedure can be found in the dbpipe package discussed in "Section 3.1.7." You will need SELECT privilege on SYS.V_$DB_PIPES to create the package.

>     /* Filename on companion disk: dbpipe.sql */*
>     PACKAGE BODY dbpipe
>     IS
>        cannot_use_pipe   EXCEPTION;
>        PRAGMA EXCEPTION_INIT(cannot_use_pipe,-23322);

>        PROCEDURE purge_all_pipes
>        IS
>           /* gets names of all pipes */
>           CURSOR all_pipes_cur
>           IS
>           SELECT name
>             FROM sys.v_$db_pipes;

>        BEGIN
>           FOR all_pipes_rec IN all_pipes_cur
>           LOOP
>              BEGIN
>                 DBMS_PIPE.PURGE(all_pipes_rec.name);

>              /* ignore cannot_use_pipe exception */
>              EXCEPTION
>                 WHEN cannot_use_pipe
>                 THEN
>                    null;
>                 WHEN OTHERS
>                 THEN
>                    RAISE;
>              END;
>           END LOOP;
>        END purge_all_pipes;

>     END dbpipe;

Emptying a pipe using PURGE releases the SGA memory associated with the pipe. Oracle can then reclaim this memory for other uses based on normal least-recently-used (LRU) aging of the shared pool.

> WARNING: Pipes that are not in use, but still have unpurged messages in them, waste SGA memory because they cannot be aged out of the shared pool. Very large pipes in this condition can lead to serious database performance problems.

Note also that calling PURGE may cause the user session's message buffer to be overwritten by messages discarded from the pipe. Be sure to send any message packed in the session buffer prior to calling PURGE. Expert technical reviewer Dan Clamage points out that this is because the implementation of PURGE is "to simply do RECEIVE_MESSAGE with timeout = 0 until the pipe is empty."

The PURGE procedure will implicitly create a public pipe of the given name if one does not already exist. This is somewhat nonintuitive, in that emptying a nonexistent pipe causes a new empty pipe to exist.

#### 3.1.3.5 The DBMS_PIPE.UNIQUE_SESSION_NAME function

The UNIQUE_SESSION_NAME function returns a string value that is constant for a given session and unique among all sessions currently connected to the database. Here's the header for this program:

>     FUNCTION DBMS_PIPE.UNIQUE_SESSION_NAME
>     RETURN VARCHAR2;

The program does not raise any package exceptions.

#### 3.1.3.5.1 Example

This example creates a pipe with a name unique to the session for receiving messages intended specifically for this session.

>     DECLARE
>        /*
>        || declare and initialize my_pipename variable with
>        || string unique to session
>        */
>        my_pipename VARCHAR2(128) := DBMS_PIPE.UNIQUE_SESSION_NAME;

>        call_status INTEGER;

>     BEGIN
>        /*
>        || create pipe as public so anyone can send message to it
>        */
>        call_status := DBMS_PIPE.CREATE_PIPE
>                          (pipename => my_pipename
>                          ,private => FALSE);
>     END;

Applications in which user sessions receive messages on database pipes usually create a unique pipe for each session to use. This helps ensure that sessions get only messages intended for them. As illustrated in the previous example, UNIQUE_SESSION_NAME is often used to generate pipe names that are unique to the session.

### 3.1.4 Packing and Unpacking Messages

The PACK_MESSAGE, PACK_MESSAGE_RAW, PACK_MESSAGE_ROWID, UNPACK_MESSAGE, UNPACK_MESSAGE_RAW, UNPACK_MESSAGE_ROWID, and NEXT_ITEM_TYPE programs are used to pack messages into your session's local message buffer and unpack them from this buffer.

#### 3.1.4.1 The DBMS_PIPE.PACK_MESSAGE procedure

The PACK_MESSAGE procedure is used to pack items of datatypes VARCHAR2, NUMBER, or DATE into the user's session's local message buffer. The header for this program follows:

>     PROCEDURE DBMS_PIPE.PACK_MESSAGE
>         (item IN VARCHAR2 | NUMBER | DATE);

Note that you must specify VARCHAR2 or NUMBERor DATE.

In Oracle8, the VARCHAR2 version of PACK_MESSAGE is somewhat different, as follows:

>     PROCEDURE DBMS_PIPE.PACK_MESSAGE
>         (item IN VARCHAR2 CHARACTER SET ANY_CS);

The item parameter is the message of the particular type that is packed into the message buffer.

#### 3.1.4.1.1 Exceptions

The program does not raise any package exceptions. The Oracle.06558 Oracle exception is raised if the message buffer becomes full and no more items can be packed.

#### 3.1.4.1.2 Restrictions

Note that the user session message buffer is limited to 4096 bytes.

#### 3.1.4.1.3 Example

The following example creates a procedure to pack and send a PL/SQL record to a database pipe. Notice the overloading of PACK_MESSAGE, which packs items of different datatypes into the local message buffer using the same procedure call.

>     /* Filename on companion disk: pipex1.sql */*
>     DECLARE
>        /*
>        || PL/SQL block illustrating use of
>        || DBMS_PIPE.PACK_MESSAGE to pack and send
>        || a PL/SQL record to a pipe
>        */
>        TYPE friend_rectype IS RECORD
>           (name       VARCHAR2(60)
>           ,birthdate  DATE
>           ,weight_lbs NUMBER
>           );

>        friend_rec  friend_rectype;

>        PROCEDURE pack_send_friend
>           (friend_rec_IN IN friend_rectype
>           ,pipename_IN IN VARCHAR2)
>        IS
>           call_status   INTEGER;
>        BEGIN
>           /*
>           ||notice the PACK_MESSAGE overloading
>           */
>           DBMS_PIPE.PACK_MESSAGE(friend_rec_IN.name);
>           DBMS_PIPE.PACK_MESSAGE(friend_rec_IN.birthdate);
>           DBMS_PIPE.PACK_MESSAGE(friend_rec_IN.weight_lbs);

>           call_status := DBMS_PIPE.SEND_MESSAGE
>                             (pipename=>pipename_IN,timeout=>0);

>           IF call_status != 0
>           THEN
>              DBMS_OUTPUT.PUT_LINE('Send message failed');
>           END IF;

>        END pack_send_friend;

>     BEGIN

>        /*
>        || OK, now use the procedure to send a friend_rec
>        */
>        friend_rec.name := 'John Smith';
>        friend_rec.birthdate := TO_DATE('01/14/55','MM/DD/YY');
>        friend_rec.weight_lbs := 175;

>        pack_send_friend(friend_rec,'OPBIP_TEST_PIPE');
>     END;

As in the example, it is good practice to encapsulate the packing and sending of an entire message as part of a logical program unit. Otherwise, additional calls to PACK_MESSAGE could add unexpected items to a message before sending, perhaps causing confusion on the receiving end.

#### 3.1.4.2 The DBMS_PIPE.PACK_MESSAGE_RAW procedure

The PACK_MESSAGE_RAW procedure packs an item of datatype RAW into the user session's local message buffer. Here's the header for this program:

>     PROCEDURE DBMS_PIPE.PACK_MESSAGE_RAW
>         (item IN RAW);

The parameter is the item to be packed into the message buffer.

#### 3.1.4.2.1 Exceptions

The program does not raise any package exceptions. The ORA-06558 Oracle exception is raised if the message buffer becomes full and no more items can be packed.

#### 3.1.4.2.2 Restrictions

Note that the user session's local message buffer is limited to 4096 bytes.

#### 3.1.4.2.3 Example

In this example, a hex string is converted to RAW, packed into the session buffer using the PACK_MESSAGE_RAW procedure, and sent to a pipe.

>     /* Filename on companion disk: pipex1.sql */*
>     DECLARE
>        hex_data    VARCHAR2(12):='FFEEDDCCBBAA';
>        raw_data    RAW(6);
>        call_status INTEGER;
>     BEGIN
>        /* create some raw data */
>        raw_data := HEXTORAW(hex_data);

>        /*
>        || pack and send raw data on pipe
>        */
>        DBMS_PIPE.PACK_MESSAGE_RAW(raw_data);
>        call_status := DBMS_PIPE.SEND_MESSAGE('OPBIP_TEST_PIPE');

>        IF call_status != 0
>        THEN
>           DBMS_OUTPUT.PUT_LINE('Send message failed');
>        END IF;
>     END;

Applications that need to send large or sensitive data items through database pipes may benefit from using compression and/or encryption on the data and packing the results using PACK_MESSAGE_RAW.

#### 3.1.4.3 The DBMS_PIPE.PACK_MESSAGE_ROWID procedure

The PACK_MESSAGE_ROWID procedure packs an item of datatype ROWID into the user session's local message buffer. The header for this program follows:

>     PROCEDURE DBMS_PIPE.PACK_MESSAGE_ROWID
>        (item IN ROWID);

The item parameter is the ROWID to pack into the message buffer.

#### 3.1.4.3.1 Exceptions

The program does not raise any package exceptions. The ORA-06558 Oracle exception is raised if the message buffer becomes full and no more items can be packed.

#### 3.1.4.3.2 Restrictions

Note that the user session's local message buffer is limited to 4096 bytes.

#### 3.1.4.3.3 Example

This (somewhat contrived) example shows a trigger that places the ROWIDs of new or modified rows of the emp table into a database pipe.

>     /* Filename on companion disk: pipex1.sql */*
>     CREATE OR REPLACE TRIGGER emp_AIU
>        AFTER INSERT OR UPDATE ON emp
>        FOR EACH ROW
>     DECLARE
>        rowid_pipename  VARCHAR2(20) := 'ROWID_PIPE';
>        call_status   INTEGER;
>     BEGIN
>        /*
>        || pack and send the rowid to a pipe
>        */
>        DBMS_PIPE.PACK_MESSAGE_ROWID(:NEW.rowid);
>        call_status := DBMS_PIPE.SEND_MESSAGE(rowid_pipename);

>        IF call_status != 0
>        THEN
>           RAISE_APPLICATION_ERROR(-20001, 'Trigger emp_AIU failed');
>       END IF;
>     END;

#### 3.1.4.4 The DBMS_PIPE.UNPACK_MESSAGE procedure

The UNPACK_MESSAGE procedure unpacks the next item from the local message buffer when the item is of datatype VARCHAR2, NUMBER, or DATE. The header for this procedure follows:

>     PROCEDURE DBMS_PIPE.UNPACK_MESSAGE
>         (item OUT VARCHAR2 | NUMBER | DATE);

In Oracle8, the VARCHAR2 version of UNPACK_MESSAGE is somewhat different, as follows:

>     PROCEDURE DBMS_PIPE.UNPACK_MESSAGE
>         (item OUT VARCHAR2 CHARACTER SET ANY_CS);

The item parameter is the variable into which the next buffer item is unpacked.

#### 3.1.4.4.1 Exceptions

The program does not raise any package exceptions. The following Oracle exceptions are raised if the message buffer contains no more items, or if the item parameter does not match the datatype of the next item in the buffer:

Number | Description
--- | ---
ORA-06556 | The pipe is empty; cannot fulfill the UNPACK_MESSAGE request
ORA-06559 | Wrong datatype requested, 2; actual datatype is 1

#### 3.1.4.4.2 Example

This example shows a procedure using UNPACK_MESSAGE to receive and unpack the PL/SQL record type used in the example for PACK_MESSAGE. Note that even though UNPACK_MESSAGE is overloaded on datatype, the correct version needs to be called for the next item in the buffer, or an ORA-06559 error will be raised. This example is a companion to the PACK_MESSAGE example. Together these two examples illustrate a complete pack, send, receive, and unpack DBMS_PIPE communications cycle.

>     /* Filename on companion disk: pipex1.sql */*
>     set serveroutput on size 100000

>     DECLARE
>        /*
>        || PL/SQL block illustrating use of
>        || DBMS_PIPE.UNPACK_MESSAGE to receive and
>        || unpack a PL/SQL record from a pipe
>        */
>        TYPE friend_rectype IS RECORD
>           (name       VARCHAR2(60)
>           ,birthdate  DATE
>           ,weight_lbs NUMBER
>           );

>        friend_rec  friend_rectype;

>        PROCEDURE receive_unpack_friend
>           (friend_rec_OUT OUT friend_rectype
>           ,pipename_IN IN VARCHAR2)
>        IS
>           call_status   INTEGER;
>        BEGIN

>           call_status := DBMS_PIPE.RECEIVE_MESSAGE
>                             (pipename=>pipename_IN,timeout=>0);
>           /*
>           ||NOTE: UNPACK_MESSAGE overloaded but we must
>           ||      call the correct version
>           */
>           DBMS_PIPE.UNPACK_MESSAGE(friend_rec_OUT.name);
>           DBMS_PIPE.UNPACK_MESSAGE(friend_rec_OUT.birthdate);
>           DBMS_PIPE.UNPACK_MESSAGE(friend_rec_OUT.weight_lbs);

>        END receive_unpack_friend;

>     BEGIN
>        /*
>        || OK test the procedure, get rec from other example
>        */
>        receive_unpack_friend(friend_rec,'OPBIP_TEST_PIPE');

>        /* display results */
>        DBMS_OUTPUT.PUT_LINE('Friend name: '||friend_rec.name);
>        DBMS_OUTPUT.PUT_LINE('Friend birthdate: '||
>                          TO_CHAR(friend_rec.birthdate));
>        DBMS_OUTPUT.PUT_LINE('Friend weight: '||
>                          TO_CHAR(friend_rec.weight_lbs));
>     END;

Here is output from running the previous script after running the PACK_MESSAGE example script:

>     Friend name: John Smith
>     Friend birthdate: 14-JAN-55
>     Friend weight: 175

>     PL/SQL procedure successfully completed.

As illustrated in the example, it is good practice to encapsulate the receipt and unpacking of messages into a single logical program unit. Message items must be unpacked in the same order in which they were packed.

> NOTE: The numeric datatype identifiers in the ORA-06559 message do not match the item type values returned by the NEXT_ITEM_TYPE function.

#### 3.1.4.5 The DBMS_PIPE.UNPACK_MESSAGE_RAW procedure

The UNPACK_MESSAGE_RAW procedure unpacks the next item from the local message buffer when the item is of datatype RAW. The header for this program follows:

>     PROCEDURE DBMS_PIPE.UNPACK_MESSAGE_RAW
>         (item OUT RAW);

The item parameter is the RAW variable into which the next buffer item is unpacked.

#### 3.1.4.5.1 Exceptions

The program does not raise any package exceptions. The following Oracle exceptions are raised if the message buffer contains no more items or the item parameter does not match the datatype of the next item in the buffer:

Number | Description
--- | ---
ORA-06556 | The pipe is empty; cannot fulfill the UNPACK_MESSAGE request
ORA-06559 | Wrong datatype requested, 2; actual datatype is 1

#### 3.1.4.5.2 Example

In this example, a message with a raw data item is received, unpacked using the UNPACK_MESSAGE_RAW procedure, and displayed.

>     /* Filename on companion disk: pipex1.sql */*
>     set serveroutput on size 100000

>     DECLARE
>        hex_data    VARCHAR2(12);
>        raw_data    RAW(6);
>        call_status INTEGER;
>     BEGIN
>        /*
>        || receive and unpack the raw message
>        */
>        call_status := DBMS_PIPE.RECEIVE_MESSAGE('OPBIP_TEST_PIPE');
>        DBMS_PIPE.UNPACK_MESSAGE_RAW(raw_data);

>        /* display results */
>        hex_data := RAWTOHEX(raw_data);
>        DBMS_OUTPUT.PUT_LINE('hex of raw: '||hex_data);
>     END;

Output from running this script immediately after the PACK_MESSAGE_RAW example script:

>     hex of raw: FFEEDDCCBBAA

>     PL/SQL procedure successfully completed.

Note that the numeric datatype identifiers in the ORA-06559 message do not match the item type values returned by the NEXT_ITEM_TYPE function.

#### 3.1.4.6 The DBMS_PIPE.UNPACK_MESSAGE_ROWID procedure

The UNPACK_MESSAGE_ROWID procedure unpacks the next item from the local message buffer when the item is of datatype ROWID. Here's the header for this program:

>     PROCEDURE DBMS_PIPE.UNPACK_MESSAGE_ROWID
>         (item OUT ROWID);

The item parameter is the ROWID variable into which the next message buffer item is unpacked.

#### 3.1.4.6.1 Exceptions

The program does not raise any declared exceptions. The following Oracle errors are raised if the message buffer contains no more items or the item parameter does not match the datatype of the next item in the buffer:

Number | Description
--- | ---
ORA-06556 | The pipe is empty; cannot fulfill the UNPACK_MESSAGE request
ORA-06559 | Wrong datatype requested, 2; actual datatype is 1

#### 3.1.4.6.2 Example

In this example, the database pipe that is filled by the EMP_AIU trigger from the example for PACK_MESSAGE_ROWID is emptied using UNPACK_MESSAGE_ROWID.

>     /* Filename on companion disk: pipex1.sql */*
>     set serveroutput on size 100000

>     DECLARE
>        rowid_pipename  VARCHAR2(20) := 'ROWID_PIPE';
>        temp_rowid   ROWID;
>        call_status  INTEGER:=0;
>     BEGIN
>        /*
>        || receive and unpack all rowids from pipe
>        */
>        WHILE call_status = 0
>        LOOP
>           call_status := DBMS_PIPE.RECEIVE_MESSAGE
>              (pipename=>rowid_pipename, timeout=>0);

>           IF call_status = 0
>           THEN
>              DBMS_PIPE.UNPACK_MESSAGE_ROWID(temp_rowid);

>              /* display rowid results */
>              DBMS_OUTPUT.PUT_LINE(ROWIDTOCHAR(temp_rowid));
>           END IF;
>        END LOOP;
>     END;
>     /

The script output looks like this:

>     SQL>
>     14 rows updated.

>     Commit complete.

>     AAAA6OAAGAAAA0FAAA
>     AAAA6OAAGAAAA0FAAB
>     AAAA6OAAGAAAA0FAAC
>     AAAA6OAAGAAAA0FAAD
>     AAAA6OAAGAAAA0FAAE
>     AAAA6OAAGAAAA0FAAF
>     AAAA6OAAGAAAA0FAAG
>     AAAA6OAAGAAAA0FAAH
>     AAAA6OAAGAAAA0FAAI
>     AAAA6OAAGAAAA0FAAJ
>     AAAA6OAAGAAAA0FAAK
>     AAAA6OAAGAAAA0FAAL
>     AAAA6OAAGAAAA0FAAM
>     AAAA6OAAGAAAA0FAAN
>     AAAA6OAAGAAAA0FAAN

>     PL/SQL procedure successfully completed.

In the previous example, the unusual looking ROWIDs are the result of running the test on an Oracle8 database. (Remember that ROWID formats have changed in Oracle8.)

> NOTE: The numeric datatype identifiers in the ORA-06559 message do not match the item type values returned by the NEXT_ITEM_TYPE function.

#### 3.1.4.7 The DBMS_PIPE.NEXT_ITEM_TYPE function

The NEXT_ITEM_TYPE function returns a number indicating the datatype of the next item in the user session's message buffer. The header for this function follows:

>     FUNCTION DBMS_PIPE.NEXT_ITEM_TYPE RETURN INTEGER;

The return value will be one of the following:

Item Type | Description
--- | ---
0 | No more items in buffer
6 | NUMBER
9 | VARCHAR2
11 | ROWID
12 | DATE
23 | RAW

The program does not raise any package exceptions.

#### 3.1.4.7.1 Example

The following PL/SQL block contains an inline procedure called unpack_all_items, which can unpack any message and display its contents using DBMS_OUTPUT. The unpack_all_items procedure uses NEXT_ITEM_TYPE to determine which version of UNPACK_MESSAGE to call for each item.

>     /* Filename on companion disk: pipex2.sql */*
>     set serveroutput on size 100000

>     DECLARE
>        call_stat   INTEGER;

>        PROCEDURE unpack_all_items
>        IS
>           /*
>           || declare temp variables of all message item types
>           */
>           temp_varchar2  VARCHAR2(2000);
>           temp_date      DATE;
>           temp_number    NUMBER;
>           temp_rowid     ROWID;
>           temp_raw       RAW(2000);

>           next_item      INTEGER:=0;

>        BEGIN
>           next_item := DBMS_PIPE.NEXT_ITEM_TYPE;

>           /*
>           || unpack by item type and convert to varchar2
>           */
>           WHILE next_item > 0
>           LOOP
>              IF next_item = 9
>              THEN
>                 DBMS_PIPE.UNPACK_MESSAGE(temp_varchar2);

>              ELSIF next_item = 6
>              THEN
>                 DBMS_PIPE.UNPACK_MESSAGE(temp_number);
>                 temp_varchar2 := 'NUMBER: '||TO_CHAR(temp_number);

>              ELSIF next_item = 11
>              THEN
>                 DBMS_PIPE.UNPACK_MESSAGE_ROWID(temp_rowid);
>                 temp_varchar2 := 'ROWID: '||ROWIDTOCHAR(temp_rowid);

>              ELSIF next_item = 12
>              THEN
>                 DBMS_PIPE.UNPACK_MESSAGE(temp_date);
>                 temp_varchar2 := 'DATE: '||
>                             TO_CHAR(temp_date,'YYYY:MM:DD:HH24:MI:SS');

>              ELSIF next_item = 23
>              THEN
>                 DBMS_PIPE.UNPACK_MESSAGE_RAW(temp_raw);
>                 temp_varchar2 := 'RAW: '||RAWTOHEX(temp_raw);

>              ELSE
>                 temp_varchar2 := 'Invalid item type: '||TO_CHAR(next_item);
>              END IF;

>              /*
>              || display item and determine next item
>              */
>              DBMS_OUTPUT.PUT_LINE(temp_varchar2);
>              next_item := DBMS_PIPE.NEXT_ITEM_TYPE;

>           END LOOP;
>        END unpack_all_items;

>     BEGIN
>        /* empty pipe */
>        DBMS_PIPE.PURGE('OPBIP_TEST_PIPE');

>        /* initialize buffer  */
>        DBMS_PIPE.RESET_BUFFER;

>        /* pack in some data of different types */
>        DBMS_PIPE.PACK_MESSAGE('HELLO THERE');
>        DBMS_PIPE.PACK_MESSAGE(123456789);
>        DBMS_PIPE.PACK_MESSAGE(SYSDATE);
>        DBMS_PIPE.PACK_MESSAGE_RAW(HEXTORAW('FFDDEE2344AA'));

>        /* send and receive the message */
>        call_stat := DBMS_PIPE.SEND_MESSAGE('OPBIP_TEST_PIPE');
>        call_stat := DBMS_PIPE.RECEIVE_MESSAGE('OPBIP_TEST_PIPE');

>        /* call the generic unpack procedure */
>        unpack_all_items;

>     END;

Here is output from running the example script:

>     SQL> @pipex2.sql

>     HELLO THERE
>     NUMBER: 123456789
>     DATE: 1998:02:01:12:01:19
>     RAW: FFDDEE2344AA

>     PL/SQL procedure successfully completed.

The unpack_all_items inline procedure is a prototype for the procedure of the same name found in the dbpipe package, discussed in the "Section 3.1.7" section.

> NOTE: The item type values returned by the NEXT_ITEM_TYPE function do not match the numeric datatype identifiers in the ORA-06559 message.

### 3.1.5 Sending and Receiving Messages

Use DBMS_PIPE's SEND_MESSAGE and RECEIVE_MESSAGE functions to send and receive messages on the pipe you have created.

#### 3.1.5.1 The DBMS_PIPE.SEND_MESSAGE function

The SEND_MESSAGE function sends a message on the named pipe. The message sent is whatever has been packed into the user session's current message buffer. The header for this program follows:

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

Parameters are summarized in the following table.

Parameter | Description
--- | ---
pipename | Name of the database pipe
timeout | Time in seconds to wait for message to be sent
maxpipesize | Maximum size in bytes of the pipe

The value returned is one of the following:

Return Value | Description
--- | ---
0 | Success
1 | Timed out
3 | Interrupted

#### 3.1.5.1.1 Exceptions

The program does not raise any package exceptions. The following Oracle exceptions are raised if the user attempts to receive a message on a pipe belonging to another user or on a NULL pipename:

Number | Description
--- | ---
ORA-23322 | Insufficient privileges to access pipe
ORA-23321 | Pipename may not be NULL

#### 3.1.5.1.2 Restrictions

Note the following restrictions on calling SEND_MESSAGE:

- Pipenames are limited to 128 bytes in length, are case-insensitive, and cannot contain NLS characters.

- Pipenames must not begin with "ORA$", as these names are reserved for use by Oracle Corporation.

#### 3.1.5.1.3 Example

This example shows the use of SEND_MESSAGE to send a message based on a PL/SQL record out on a database pipe.

The pack_send_request procedure can be found in the pipesvr package discussed in the "Section 3.1.7" section.

>      /* Filename on companion disk: pipesvr.sql */*
>      PROCEDURE pack_send_request
>     	(request_rec_IN IN request_rectype
>     	,return_code_OUT OUT NUMBER)
>     IS
>     BEGIN
>     	/* discard any previous unsent message items */
>     	DBMS_PIPE.RESET_BUFFER;

>     	/* pack message in standard order */
>     	DBMS_PIPE.PACK_MESSAGE(request_protocol);
>     	DBMS_PIPE.PACK_MESSAGE(request_rec_IN.response_pipe);
>          DBMS_PIPE.PACK_MESSAGE(request_rec_IN.service);

>          /*
>          || send message to request pipe nowait
>          */
>          return_code_OUT := DBMS_PIPE.SEND_MESSAGE
>                                (pipename => request_pipe
>                                ,timeout  => 0);
>      END pack_send_request;

The SEND_MESSAGE function will implicitly create a public pipe if the pipe specified by the pipename parameter does not already exist. Be careful not to assume that the call to SEND_MESSAGE has been successful. Note that in this example, the value returned by the call to SEND_MESSAGE is passed out of the pack_send_request procedure to its caller, so it will be the caller's responsibility to handle a nonzero return value.

Calls to SEND_MESSAGE will wait for up to the value of the timeout parameter in seconds for the call to complete. Applications using database pipes that stay full of messages may incur lengthy wait times or timeouts. When using SEND_MESSAGE under these circumstances, be careful to specify a timeout that users can tolerate.

Applications experiencing frequent timeouts or long waits when calling SEND_MESSAGE may benefit by increasing the size of the database pipe. This can be done by specifying a value for the maxpipesize parameter that is greater than the current maximum size of the pipe.

#### 3.1.5.2 The DBMS_PIPE.RECEIVE_MESSAGE function

The RECEIVE_MESSAGE function is used to fetch a message from the named pipe into the user session's message buffer. The header for this program follows:

>     FUNCTION DBMS_PIPE.RECEIVE_MESSAGE
>         (pipename IN VARCHAR2
>         ,timeout IN INTEGER DEFAULT MAXWAIT)
>     RETURN INTEGER;

Parameters are summarized in the following table.

Parameter | Description
--- | ---
pipename | Name of the database pipe
timeout | Time in seconds to wait for message to be received

The function returns one of the following values:

Return Value | Description
--- | ---
0 | Success
1 | Timed out
2 | Message too big for buffer
3 | Interrupted

#### 3.1.5.2.1 Exceptions

The program does not raise any package exceptions. The following Oracle exceptions are raised if the user attempts to receive a message on a pipe belonging to another user or on a NULL pipename:

Number | Description
--- | ---
ORA-23322 | Insufficient privileges to access pipe
ORA-23321 | Pipename may not be NULL

#### 3.1.5.2.2 Restrictions

Note the following restrictions on calling RECEIVE_MESSAGE:

- Pipenames are limited to 128 bytes in length, are case-insensitive, and cannot contain NLS characters.

- Pipenames must not begin with "ORA$", as these names are reserved for use by Oracle Corporation.

#### 3.1.5.2.3 Example

This example shows the use of the RECEIVE_MESSAGE function to receive a message based on a PL/SQL record from a database pipe.

The receive_unpack_request procedure can be found in the pipesvr package discussed in the "Section 3.1.7" section.

>      /* Filename on companion disk: pipesvr.sql */*
>      PROCEDURE receive_unpack_request
>     	(timeout_IN IN INTEGER
>     	,request_rec_OUT OUT request_rectype
>     	 ,return_code_OUT OUT NUMBER)
>     IS
>     	/* temp variables */
>     	temp_protocol  request_protocol%TYPE;
>     	temp_return_code  NUMBER;

>     BEGIN

>     	temp_return_code := DBMS_PIPE.RECEIVE_MESSAGE
>     			(pipename => request_pipe
>     			 ,timeout  => timeout_IN);

>     	IF temp_return_code = 0
>     	THEN
>     		/* check if expected protocol */
>     		DBMS_PIPE.UNPACK_MESSAGE(temp_protocol);

>     		IF temp_protocol = request_protocol
>     		THEN
>     			DBMS_PIPE.UNPACK_MESSAGE(request_rec_OUT.response_pipe);
>     			DBMS_PIPE.UNPACK_MESSAGE(request_rec_OUT.service);
>     		ELSE
>     			/* pipe message has unexpected protocol */
>     			temp_return_code := -1;
>     			debug('UNKNOWN PROTOCOL: '||temp_protocol);
>     			DBMS_PIPE.RESET_BUFFER;
>     		END IF;
>     	END IF;

>     	return_code_OUT := temp_return_code;

>     EXCEPTION
>     	WHEN OTHERS THEN
>     		return_code_OUT := SQLCODE;
>     		debug('RECEIVE REQUEST EXCP: '||SQLERRM
>                   ,force_TF_IN=>TRUE);
>              DBMS_PIPE.RESET_BUFFER;

>        END receive_unpack_request;

Calls to RECEIVE_MESSAGE will wait for up to the value of the timeout parameter in seconds for the call to complete. Applications accessing database pipes that are usually empty of messages can incur lengthy wait times or timeouts. When using RECEIVE_MESSAGE under these circumstances, be careful to specify a timeout that users can tolerate.

The RECEIVE_MESSAGE function will implicitly create a public pipe of the given name if one does not already exist.

### 3.1.6 Tips on Using DBMS_PIPE

Oracle does not provide detailed documentation of exactly how database pipes work, nor much in the way of how best to use them. The programs in DBMS_PIPE are quite low-level utilities. Higher-level programs using DBMS_PIPE typically need to make numerous calls to these programs, which must be in the correct order, to handle communications. This can lead to complex and difficult code, unless a structured, modular, template-based approach is used.

Through research and experience, I have adopted a method for building safe, reliable, and extensible higher-level communications layers on top of DBMS_PIPE. The following items are the main elements of this method:

- Define message types using PL/SQL records

- Encapsulate pack/send and receive/unpack logic around record types

- Separate messages using session-specific pipes

- Use a well-defined protocol across applications

- Pay attention to timeout values

- Use RESET_BUFFER in exception handlers and pack/unpack routines

- Take the time to size pipes correctly

- Purge and remove pipes when finished

#### 3.1.6.1 Defining message types and encapsulating communications logic

PL/SQL records and DBMS_PIPE messages both bundle related data items together, so there is a natural affinity between them. When implementing applications that use DBMS_PIPE, do the following:

1. Determine the kinds of message data that will be communicated between sessions.

2. Develop a PL/SQL record type corresponding to each different type of message.

3. Build two procedures around the record type. One procedure takes a PL/SQL record and pipename as IN parameters, packs the record into a message using PACK_MESSAGE, and sends it to the pipe using SEND_MESSAGE. This procedure is usually named pack_send_rectype. The second procedure, usually called receive_unpack_rectype, performs the inverse operation. It takes a pipename as an IN parameter and a record as an OUT parameter, retrieves a message on the pipe using RECEIVE_MESSAGE, and unbundles the message into the record using calls to UNPACK_MESSAGE.

4. Once these two procedures are built, I can send and receive PL/SQL records as messages on database pipes; using these procedure calls, all of the low-level calls to DBMS_PIPE programs are hidden. This approach also makes it easy to extend the messaging to add new data items: simply add a new field to the end of the record type, and new calls to PACK_MESSAGE and UNPACK_MESSAGE to the two procedures.

Examples of this record-to-pipe-message encapsulation technique can be seen in the examples for PACK_MESSAGE and UNPACK_MESSAGE, where procedures to pack/send and receive/unpack a record type called friend_rectype are defined. These examples are repeated here:

>     /* Filename on companion disk: pipex1.sql */*
>     TYPE friend_rectype IS RECORD
>     	(name       VARCHAR2(60)
>     	,birthdate  DATE
>     	,weight_lbs NUMBER
>     	);

>     friend_rec  friend_rectype;

>     PROCEDURE pack_send_friend
>     	(friend_rec_IN IN friend_rectype
>     	,pipename_IN IN VARCHAR2)
>     IS
>     	call_status   INTEGER;
>     BEGIN
>     	/*
>     	||notice the PACK_MESSAGE overloading
>     	*/
>     	DBMS_PIPE.PACK_MESSAGE(friend_rec_IN.name);
>     	DBMS_PIPE.PACK_MESSAGE(friend_rec_IN.birthdate);
>     	DBMS_PIPE.PACK_MESSAGE(friend_rec_IN.weight_lbs);

>     	call_status := DBMS_PIPE.SEND_MESSAGE
>     				(pipename=>pipename_IN,timeout=>0);

>     	IF call_status != 0
>     	THEN
>     		DBMS_OUTPUT.PUT_LINE('Send message failed');
>     	END IF;

>     END pack_send_friend;

>     PROCEDURE receive_unpack_friend
>     	(friend_rec_OUT OUT friend_rectype
>     	 ,pipename_IN IN VARCHAR2)
>     IS
>     	call_status   INTEGER;
>     BEGIN

>     	call_status := DBMS_PIPE.RECEIVE_MESSAGE
>     				(pipename=>pipename_IN,timeout=>0);
>     	/*
>     	||NOTE: UNPACK_MESSAGE overloaded but we must
>     	||      call the correct version
>     	*/
>     	DBMS_PIPE.UNPACK_MESSAGE(friend_rec_OUT.name);
>     	DBMS_PIPE.UNPACK_MESSAGE(friend_rec_OUT.birthdate);
>     	DBMS_PIPE.UNPACK_MESSAGE(friend_rec_OUT.weight_lbs);

>     END receive_unpack_friend;

#### 3.1.6.2 Separating messages

Once a reliable mechanism is established for sending and receiving a specific message type, the next step is to make sure that messages get to their expected recipients. Database pipes themselves can hold any message, so separating messages of different types is usually done by creating specific pipes to handle specific types of messages. Messages intended for a specific user will usually be placed on a pipe whose name is unique to the user session. Unique pipenames can be established using the UNIQUE_SESSION_NAME function. Common pipes -- for example, those on which server programs listen for service requests -- will have pipenames known to all sessions that use the service. These names are typically contained in private global variables embedded in the packages that handle the DBMS_PIPE communications for the server.

#### 3.1.6.3 Establishing messaging protocols

Since any session with access to a database pipe can put a message there, it is good practice for programs that receive and unpack messages to establish a protocol to reliably identify the type of message received prior to unpacking. This protects against receiving ORA-06559 errors because the next item type in the message does not match the item parameter in the call to UNPACK_MESSAGE. It also protects against unpacking a message successfully, only to find out that the data itself was not really what was expected. Therefore, it is good practice for all messages to have a protocol identifier as the first item in the message. This item should always be a specific datatype. I use and recommend VARCHAR2, because safer and more meaningful protocol identifiers than numbers can be created this way.

Once protocol identifiers are established, receive/unpack routines will have code that looks like the following:

>     call_status := DBMS_PIPE.RECEIVE_MESSAGE(pipename_IN, timeout_value);

>     IF call_status = 0
>     THEN
>        /* unpack protocol id */
>        DBMS_PIPE.UNPACK_MESSAGE(protocol_id);

>        IF protocol_id = 'EXPECTED ID FOR THIS ROUTINE'
>        THEN
>           /*
>           || OK, we know what message type, unpack the rest here
>           || using calls to DBMS_PIPE.UNPACK_MESSAGE
>           */
>        ELSE
>           DBMS_PIPE.RESET_BUFFER;
>           RAISE_APPLICATION_ERROR('Invalid protocol');
>        END IF;
>     END IF;

#### 3.1.6.4 Paying attention to timeouts

Note also in the previous code fragment that a timeout is specified in the call to RECEIVE_MESSAGE, which is another of my recommended best practices. Sessions will block and wait for up to the value of the timeout parameter for calls to SEND_MESSAGE or RECEIVE_MESSAGE to complete. The default for this parameter is the constant DBMS_PIPE.maxwait, which equates to 1000 days! Since most users are not quite this patient, it pays to spend some time determining and using acceptable timeout values.

#### 3.1.6.5 Using RESET_BUFFER

The code fragment also illustrates one place where I use the RESET_BUFFER procedure: when a message has been received but the protocol is unrecognized. This effectively discards the message. Another place where the message buffer should be reset for safety is at the beginning of message packing routines. Resetting here ensures that the message packed does not include any items that may have been packed into the buffer prior to entering the routine (i.e., that the message is only what is intended). Finally, another good place to use RESET_BUFFER is in exception handlers for programs using DBMS_PIPE programs. Since exceptions indicate unexpected results, it is safe practice to make sure that the message buffer is initialized under these circumstances.

#### 3.1.6.6 Sizing and removing pipes for good memory management

Finally, my best practices call for proper sizing, purging, and removal of database pipes. These all amount to observing good memory management in the Oracle shared pool. Pipes that are too large may interfere with other shared pool operations. Pipes that are too small for their traffic volume can result in long wait times or timeouts in calls to SEND_MESSAGE. Pipes that are no longer needed and that still contain messages effectively waste SGA memory because they will not be aged out of the shared pool. Thus, it pays to spend time making pipe sizes large enough to handle traffic with minimum wait times, yet as small as possible to conserve memory, and to remove pipes or at least purge them of messages when they are no longer needed.

### 3.1.7 DBMS_PIPE Examples

This section contains several longer examples of using DBMS_PIPE.

#### 3.1.7.1 Communicating with the outside world

One of the primary advantages of DBMS_PIPE is that it facilitates the long-desired ability to communicate with the "outside world" from within Oracle. In this new situation, database pipes can provide users access to external services from within their Oracle sessions. Oracle's package specification script for DBMS_PIPE (dbmspipe.sql ) presents a relatively complete example of how a "stock price request server" service could be implemented. Several examples of how to implement such external services using 3GL languages like C are available through online sources. In particular, Oracle Technical Support Bulletin 105688.158 gives a complete implementation of a daemon process written in C that listens on a database pipe and provides Oracle sessions with the ability to execute operating system commands from PL/SQL (similar to the HOST command in SQL*Plus).

#### 3.1.7.2 Exploring DBMS_PIPE

I had several questions about database pipes and decided to explore the following:

- Can a message packed into the buffer be unpacked prior to sending?

- Do PACK_MESSAGE and RECEIVE_MESSAGE use a common buffer?

- Does the PACK_MESSAGE procedure do any data compression to conserve memory?

I developed several tests to help answer these questions. The first question seemed quite straightforward. This is the test I developed and the results:

>     DECLARE
>        message_out    VARCHAR2(2000);

>     BEGIN
>        /* try to pack and unpack */
>        DBMS_PIPE.PACK_MESSAGE('This is my message');
>        DBMS_PIPE.UNPACK_MESSAGE(message_out);
>        DBMS_OUTPUT.PUT_LINE
>           ('message unpacked: '||message_out);
>     END;
>     /

>     DECLARE
>     *
>     ERROR at line 1:
>     ORA-06556: the pipe is empty, cannot fulfill the unpack_message request
>     ORA-06512: at "SYS.DBMS_PIPE", line 71
>     ORA-06512: at line 11

Calling UNPACK_MESSAGE immediately following a call to PACK_MESSAGE generated an exception, so it appears that the first answer is no: the packed message buffer cannot be unpacked prior to sending. However, check out the results of the following test:

>     DECLARE
>        message_out    VARCHAR2(2000);

>     BEGIN
>        /* initialize buffer */
>        DBMS_PIPE.RESET_BUFFER;

>        /* try to pack and unpack */
>        DBMS_PIPE.PACK_MESSAGE('This is my message');
>        DBMS_PIPE.UNPACK_MESSAGE(message_out);
>        DBMS_OUTPUT.PUT_LINE
>           ('message unpacked: '||message_out);
>     END;
>     /

>     message unpacked: This is my message

>     PL/SQL procedure successfully completed.

The only difference in the second test was that RESET_BUFFER was called prior to packing and unpacking the message. Furthermore, subsequent executions of the first test block completed successfully even though RESET_BUFFER was not explicitly called. So the real answer to the first question appears to be that packed messages can be unpacked prior to sending as long as RESET_BUFFER has been previously called in the session. This confuses me, and I don't like the fact that Oracle does not expose more details about the inner workings of the local message buffer in relation to packing and unpacking messages.

The second question ("Do PACK_MESSAGE and UNPACK_MESSAGE use a common buffer?") is a little trickier. It actually occurred to me only after exploring the first question about unpacking a packed buffer before sending. I wondered whether packing and unpacking messages could happen independently of each other in the message buffer, or whether the session message buffer was essentially a single slot with room for only one message. I expanded my earlier test script into the following:

>     /* Filename on companion disk: pipe2.sql */*
>     DECLARE
>        test_pipename  VARCHAR2(30):='OPBIP_TEST_PIPE2';
>        call_status    INTEGER;
>        message1_out   VARCHAR2(2000);
>        message2_out   VARCHAR2(2000);

>     BEGIN
>        DBMS_PIPE.RESET_BUFFER;

>        /* make sure pipe is empty */
>        call_status := DBMS_PIPE.CREATE_PIPE(test_pipename);
>        DBMS_PIPE.PURGE(test_pipename);

>        /* pack and send first message  */
>        DBMS_PIPE.PACK_MESSAGE('This is message one');
>        call_status := DBMS_PIPE.SEND_MESSAGE(test_pipename);
>        DBMS_OUTPUT.PUT_LINE('call status send1: '||TO_CHAR(call_status));

>        /* now pack second message without sending  */
>        DBMS_PIPE.PACK_MESSAGE('This is message two');

>        /* receive, unpack and print message */
>        call_status := DBMS_PIPE.RECEIVE_MESSAGE(test_pipename);
>        DBMS_OUTPUT.PUT_LINE('call status receive1: '||TO_CHAR(call_status));
>        DBMS_PIPE.UNPACK_MESSAGE(message1_out);
>        DBMS_OUTPUT.PUT_LINE('message unpacked: '||message1_out);

>        /* now send message two...is it still there?  */
>        call_status := DBMS_PIPE.SEND_MESSAGE(test_pipename);
>        DBMS_OUTPUT.PUT_LINE('call status send2: '||TO_CHAR(call_status));

>        /* receive, unpack and print message */
>        call_status := DBMS_PIPE.RECEIVE_MESSAGE(test_pipename);
>        DBMS_OUTPUT.PUT_LINE('call status receive2: '||TO_CHAR(call_status));
>        DBMS_PIPE.UNPACK_MESSAGE(message2_out);
>        DBMS_OUTPUT.PUT_LINE('message unpacked: '||message2_out);
>     END;
>     /
>     call status send1: 0
>     call status receive1: 0
>     message unpacked: This is message one
>     call status send2: 0
>     call status receive2: 0
>     message unpacked: This is message one

>     PL/SQL procedure successfully completed.

Notice that all calls to SEND_MESSAGE and RECEIVE_MESSAGE returned 0, indicating success. However, message two was never sent or received; instead, message one was sent and received twice. This indicates that the message buffer can contain only one message at a time for either sending or receiving. Receiving message one from the pipe overlaid message two in the buffer. It is interesting that the second call to SEND_MESSAGE sent the message that was just unpacked into the buffer, not the last message packed. It seems that a message that has been received and unpacked can also be sent without being repacked. As with the first question, this is somewhat confusing and counterintuitive, and again begs for more detailed documentation from Oracle on DBMS_PIPE.

One idea this second test gave me was forwarding messages from one pipe to another without consuming them. I developed a procedure to do just that; it's discussed later in "Section 3.1.7.3, "The dbpipe utility package"."

The concept of "packing" message items into a buffer suggested that perhaps the items were also being compressed somehow. If this were true, then message items containing strings of repeating characters should pack tightly into the 4096-byte buffer, and the buffer could contain more than 4096 bytes worth of messages. In order to test this theory, I developed a procedure to stuff as many copies as possible of an input string into the message buffer and count exactly how big the resulting message is. Here is the source code for the pipe1 procedure:

>     /* Filename on companion disk: pipe1.sql */*
>     CREATE OR REPLACE PROCEDURE pipe1
>        (message_item_IN IN VARCHAR2)
>        /*
>        || Tests whether DBMS_PIPE compresses
>        || string message items on packing by
>        || stuffing buffer full and counting total
>        || size of message.
>        ||
>        || Author:  John Beresniewicz, Savant Corp
>        || Created: 09/16/97
>        ||
>        */
>     IS
>        test_pipename  VARCHAR2(30):='OPBIP_TEST_PIPE';
>        call_status       INTEGER;

>        item_counter   INTEGER :=0;
>        total_msg_size  INTEGER :=0;

>        buffer_full    EXCEPTION;
>        PRAGMA EXCEPTION_INIT(buffer_full,-6558);

>     BEGIN

>        /* make sure pipe is empty and buffer initialized */
>        call_status := DBMS_PIPE.CREATE_PIPE(test_pipename);
>        DBMS_PIPE.PURGE(test_pipename);
>        DBMS_PIPE.RESET_BUFFER;

>        BEGIN
>           /* buffer_full exception ends the loop */
>           LOOP
>              DBMS_PIPE.PACK_MESSAGE(message_item_IN);

>              /*
>              || increment total size:  1 byte for datatype and
>              || 2 bytes for item length
>              */
>              total_msg_size := total_msg_size+3+LENGTHB(message_item_IN);
>              item_counter := item_counter +1;
>           END LOOP;

>        EXCEPTION
>           WHEN buffer_full
>              THEN
>                 /* test if message can send OK on buffer_full */
>                 call_status := DBMS_PIPE.SEND_MESSAGE(test_pipename);

>                 IF call_status = 0
>                 THEN
>                    /* OK, display results for this message item */
>                    DBMS_OUTPUT.PUT_LINE
>                       ('Items Packed: '||TO_CHAR(item_counter));
>                    DBMS_OUTPUT.PUT_LINE
>                       ('Total Msg Size: '||TO_CHAR(total_msg_size+1));
>                 ELSE
>                    DBMS_OUTPUT.PUT_LINE
>                       ('Pipe Send Error, return code: '||
>                                            TO_CHAR(call_status));
>                 END IF;
>           WHEN OTHERS
>              THEN
>                 DBMS_OUTPUT.PUT_LINE('Oracle Error: '||TO_CHAR(SQLCODE));
>        END;

>     END pipe1;

There are a couple of useful techniques demonstrated in pipe1. For one, the EXCEPTION_INIT pragma is used to define an exception to trap the buffer full condition. This exception is then used to exit the message packing loop, which is somewhat unusual but precisely what we need in this case. Also, the pipe is created and immediately purged to ensure that it is empty for the test. The purge is done because the DBMS_PIPE.CREATE_PIPE call will succeed if the pipe already exists, and it may contain messages, which could interfere with the test. Since the test is designed to measure how much can be packed into the local buffer, DBMS_PIPE.RESET_BUFFER is called to make sure that the buffer starts off completely empty.

The pipe1 procedure is not particularly useful, except to answer the question about whether message items are compressed. Well, here are the results from several calls to pipe1 using different message items:

>     SQL> execute pipe1('This is a long text message');

>     Items Packed: 136
>     Total Msg Size: 4081

>     PL/SQL procedure successfully completed.

>     SQL> execute pipe1(RPAD(' ',2000));

>     Items Packed: 2
>     Total Msg Size: 4007

>     PL/SQL procedure successfully completed.

>     SQL> execute pipe1('1');

>     Items Packed: 1023
>     Total Msg Size: 4093

>     PL/SQL procedure successfully completed.

The tests show there is no data compression taking place when message items are packed into the buffer. This is most clearly seen in the second test, where only two strings of 2000 blanks could be packed into the buffer. Also note the inefficiency of packing many small items into a message (third test) since the three bytes of per-item overhead account for most of the space used.

The conclusion I've drawn from all these tests: the inner workings of DBMS_PIPE are not at all intuitive or obvious. I'm still somewhat confused by some of the test results, and the lack of clear documentation by Oracle is frustrating. The good news is that reliable pipe-based communications can be achieved by following the simple guidelines and best practices discussed previously. Doing so will help avoid programs that enter those murky areas which my testing purposely explored.

#### 3.1.7.3 The dbpipe utility package

While conducting my experiments on DBMS_PIPE, I had a couple of ideas for some utility programs. One thing I wanted was a kind of "sniffer" program that could show me the contents of any pipe. Since I was not following safe pipe programming practices (on purpose) -- I kept stuffing all kinds of messages into all kinds of pipes -- I often did not know what had gotten where. I needed a generic program that could show me the contents of any pipe without knowing message specifics such as number of items and their datatypes. Another idea was to forward a message from one pipe to another. This seemed potentially useful, perhaps as the basis for a kind of pipe-based broadcasting or chain-letter application.

It turns out that one key to both of these utilities was creating utility programs that could unpack and repack any message without knowing the form of its contents in advance.

These ideas became the dbpipe package. Here is the package specification:

>     /* Filename on companion disk: dbpipe.sql */*
>     CREATE OR REPLACE PACKAGE dbpipe
>        /*
>        || Package of interesting utilities illustrating use of
>        || DBMS_PIPE programs.  Includes a forwarding program to
>        || pass pipe messages from one pipe to another, a peek
>        || program to inspect and replace pipe messages, and
>        || generic unpack and pack programs.
>        ||
>        || Author:  John Beresniewicz, Savant Corp
>        ||
>        || 10/10/97: added purge_all_pipes
>        || 10/10/97: made cannot_use_pipe a public
>        ||           exception
>        || 10/05/97: added makepipe and closepipe
>        || 09/28/97: added invalid_item_type exception to
>        ||           unpack_to_tbl
>        || 09/25/97: added safe or cool mode to forward
>        || 09/21/97: created
>        ||
>        || Compilation Requirements:
>        ||
>        || EXECUTE on DBMS_PIPE
>        || EXECUTE on DBMS_SESSION
>        || SELECT  on SYS.V_$DB_PIPES
>        ||
>        || Execution Requirements:
>        ||
>        */
>     AS
>        /*
>        || declare exceptions raised by various DBMS_PIPE
>        || programs when user cannot access a private pipe
>        || or pipename is null
>        */
>        cannot_use_pipe   EXCEPTION;
>        PRAGMA EXCEPTION_INIT(cannot_use_pipe,-23322);
>        null_pipename   EXCEPTION;
>        PRAGMA EXCEPTION_INIT(null_pipename,-23321);

>        /*
>        || message_rectype records can capture any single
>        || item which can be packed into a DBMS_PIPE message
>        */
>        TYPE message_rectype IS RECORD
>           (item_type  INTEGER
>           ,Mvarchar2  VARCHAR2(4093)
>           ,Mdate      DATE
>           ,Mnumber    NUMBER
>           ,Mrowid     ROWID
>           ,Mraw       RAW(4093)
>           );

>        /*
>        || message_tbltype tables can hold an ordered list of
>        || message items, thus any message can be captured
>        */
>        TYPE message_tbltype IS TABLE OF message_rectype
>           INDEX BY BINARY_INTEGER;

>        /*
>        || unpacks message buffer into table,
>        || optionally displays message to screen
>        */
>        PROCEDURE unpack_to_tbl
>           (message_tbl_OUT OUT message_tbltype
>           ,display_TF IN BOOLEAN := FALSE);
>        /*
>        || packs message buffer from message table
>        */
>        PROCEDURE pack_from_tbl
>           (message_tbl_IN IN message_tbltype);

>        /*
>        || forward a message from one pipe to another,
>        || supports two techniques (safe and cool)
>        */
>        PROCEDURE forward
>           (from_pipename_IN IN VARCHAR2
>           ,to_pipename_IN IN VARCHAR2
>           ,timeout_secs_IN IN INTEGER := 10
>           ,safe_mode_IN IN BOOLEAN := FALSE);

>        /*
>        || takes sample message from a pipe and displays the
>        || contents, replaces message back into pipe if
>        || boolean parameter is TRUE
>        */
>        PROCEDURE peek
>           (pipename_IN IN VARCHAR2
>           ,timeout_secs_IN IN INTEGER := 60
>           ,replace_message_TF IN BOOLEAN := TRUE);

>        /*
>        || encapsulates DBMS_PIPE.CREATE_PIPE and returns
>        || FALSE if ORA-23322 is raised, indicating
>        || the pipename is already used and not accessible
>        || to the caller
>        */
>        FUNCTION makepipe
>           (pipename_IN IN VARCHAR2
>           ,maxsize_bytes_IN IN INTEGER DEFAULT 8192
>           ,private_IN IN BOOLEAN DEFAULT TRUE)
>        RETURN BOOLEAN;

>        /*
>        || encapsulates DBMS_PIPE.REMOVE_PIPE and returns
>        || FALSE if ORA-23322 is raised, indicating
>        || the pipename exists and is not removable
>        || by the caller
>        */
>        FUNCTION closepipe
>           (pipename_IN IN VARCHAR2)
>        RETURN BOOLEAN;

>        /*
>        || purges all pipes the caller can access
>        */
>        PROCEDURE purge_all_pipes;

>     END dbpipe;

#### 3.1.7.3.1 Unpack_to_tbl and pack_from_tbl procedures

The two procedures unpack_to_tbl and pack_from_tbl implement the generic unpack and pack functionality. They use PL/SQL tables of records based on message_tbltype, which is designed to hold an ordered list of items of any datatype. Each row in a table of type message_tbltype contains two data values: an entry in the item_type field indicating the type of this message item (as returned by DBMS_PIPE.NEXT_ITEM_TYPE) and an entry in the field of the corresponding datatype with the value of this message item. The unpack_to_tbl procedure unpacks all items in a newly received message into a message table, indexing them in the table by their unpack order. The pack_from_tbl procedure takes a message table loaded in this fashion and repacks the original message into the message buffer in index order. The unpack_to_tbl procedure can also optionally use the DBMS_OUTPUT built-in package (described in Chapter 6, Generating Output from PL/SQL Programs) to display the message unpacked.

Here are the full package bodies for unpack_to_tbl and pack_from_tbl. Note how unpack_to_tbl grew out of the example code for the DBMS.PIPE.NEXT_ITEM_TYPE function.

>     /* Filename on companion disk: dbpipe.sql */*
>     PROCEDURE unpack_to_tbl
>           (message_tbl_OUT OUT message_tbltype
>           ,display_TF IN BOOLEAN := FALSE)
>        IS
>           /*
>           || NOTE: this procedure should only be called after
>           || a successful call to DBMS_PIPE.RECEIVE_MESSAGE
>           */

>           /* empty table to flush output table on exception */
>           null_message_tbl message_tbltype;

>           /*
>           || temp display variable extra long to account
>           || for RAWTOHEX conversion that can double size
>           */
>           temp_varchar2  VARCHAR2(8186);

>           next_item      INTEGER;
>           item_count     INTEGER := 0;

>        BEGIN

>           next_item := DBMS_PIPE.NEXT_ITEM_TYPE;

>           /*
>           || loop through all items, unpacking each by item
>           || type and convert to varchar2 for display
>           */
>           WHILE next_item > 0
>           LOOP
>              /*
>              || increment item count and store item type
>              */
>              item_count := item_count + 1;
>              message_tbl_OUT(item_count).item_type := next_item;

>              /*
>              || now use next_item to call correct unpack procedure,
>              || saving item to message_tbl
>              ||
>              || also stuff temp_varchar2 with string conversion
>              || of the item
>              */
>              IF next_item = 9
>              THEN
>                 DBMS_PIPE.UNPACK_MESSAGE
>                    (message_tbl_OUT(item_count).Mvarchar2);

>                 temp_varchar2 := 'VARCHAR2: '||
>                          message_tbl_OUT(item_count).Mvarchar2;
>              ELSIF next_item = 6
>              THEN
>                 DBMS_PIPE.UNPACK_MESSAGE
>                    (message_tbl_OUT(item_count).Mnumber);

>                 temp_varchar2 := 'NUMBER: '||
>                       TO_CHAR(message_tbl_OUT(item_count).Mnumber);

>              ELSIF next_item = 11
>                 THEN
>                 DBMS_PIPE.UNPACK_MESSAGE_ROWID
>                    (message_tbl_OUT(item_count).Mrowid);

>                 temp_varchar2 := 'ROWID: '||
>                       ROWIDTOCHAR(message_tbl_OUT(item_count).Mrowid);

>              ELSIF next_item = 12
>              THEN
>                 DBMS_PIPE.UNPACK_MESSAGE
>                    (message_tbl_OUT(item_count).Mdate);

>                 temp_varchar2 := 'DATE: '||
>                    TO_CHAR(message_tbl_OUT(item_count).Mdate,
>                                      'YYYY:MM:DD:HH24:MI:SS');

>              ELSIF next_item = 23
>              THEN
>                 DBMS_PIPE.UNPACK_MESSAGE_RAW
>                    (message_tbl_OUT(item_count).Mraw);

>                 temp_varchar2 := 'RAW: '||
>                    RAWTOHEX(message_tbl_OUT(item_count).Mraw);

>              ELSE
>                 temp_varchar2 := 'Invalid item type: '||
>                                      TO_CHAR(next_item);

>                 RAISE invalid_item_type;
>              END IF;

>              /*
>              || display results and get next item type
>              */
>              IF display_TF
>              THEN
>                 DBMS_OUTPUT.PUT_LINE(temp_varchar2);
>              END IF;

>              next_item := DBMS_PIPE.NEXT_ITEM_TYPE;

>           END LOOP;

>        EXCEPTION
>           WHEN invalid_item_type
>           THEN
>              message_tbl_OUT := null_message_tbl;

>        END unpack_to_tbl;

>        PROCEDURE pack_from_tbl
>           (message_tbl_IN IN message_tbltype)
>        IS
>           /*
>           || packs the session message buffer from a generic
>           || message table
>           */
>        BEGIN
>           FOR i IN message_tbl_IN.FIRST..message_tbl_IN.LAST
>           LOOP
>              IF message_tbl_IN(i).item_type = 9
>              THEN
>                 DBMS_PIPE.PACK_MESSAGE(message_tbl_IN(i).Mvarchar2);

>              ELSIF message_tbl_IN(i).item_type = 6
>              THEN
>                 DBMS_PIPE.PACK_MESSAGE(message_tbl_IN(i).Mnumber);

>              ELSIF message_tbl_IN(i).item_type = 12
>              THEN
>                 DBMS_PIPE.PACK_MESSAGE(message_tbl_IN(i).Mdate);

>              ELSIF message_tbl_IN(i).item_type = 11
>              THEN
>                 DBMS_PIPE.PACK_MESSAGE_ROWID(message_tbl_IN(i).Mrowid);

>              ELSIF message_tbl_IN(i).item_type = 23
>              THEN
>                 DBMS_PIPE.PACK_MESSAGE_RAW(message_tbl_IN(i).Mraw);

>              END IF;

>           END LOOP;

>        ENDpack_from_tbl;

I really like these utilities, but they suffer from a potentially serious limitation inherited from Oracle's rather poor memory management for PL/SQL tables of records. Basically, each row of a PL/SQL table of type message_tbltype consumes at least enough memory to fill out the variable-length columns, which is greater than eight kilobytes. Thus, unpacking a message with more than a few items in it can result in a very large PL/SQL table. This is demonstrated by the following test results, which use the my_session.memory procedure (see Chapter 11, Managing Session Information) to display user session memory before and after unpacking a message.

>     /* Filename on companion disk: pipemem.sql. */*
>     set serveroutput on size 100000

>     DECLARE
>        null_msg_tbl dbpipe.message_tbltype;
>        msg_tbl dbpipe.message_tbltype;
>        call_stat  INTEGER;

>     BEGIN

>        /* pack a message with a number of items */
>        FOR i in 1..50
>        LOOP
>           DBMS_PIPE.PACK_MESSAGE('message number: '||TO_CHAR(i));
>        END LOOP;

>        /* send and receive the message */
>        call_stat :=DBMS_PIPE.SEND_MESSAGE('PIPEX');
>        call_stat :=DBMS_PIPE.RECEIVE_MESSAGE('PIPEX');

>        /* use the generic unpack and show memory */
>        dbpipe.unpack_to_tbl(msg_tbl,FALSE);
>        my_session.memory;

>        /* now free, release and show memory */
>        msg_tbl := null_msg_tbl;
>        DBMS_SESSION.FREE_UNUSED_USER_MEMORY;
>        my_session.memory;

>     END;
>     /
>     session UGA: 41160
>     session PGA: 987576
>     session UGA: 41160
>     session PGA: 137760

>     PL/SQL procedure successfully completed.

The test shows that using unpack_to_tbl on a message with 50 items results in session PGA memory exceeding 900 kilobytes in size, most of which is wasted. Clearly, this is not a good scenario for a real application with many users, so the general usefulness of unpack_to_tbl and pack_from_tbl will have to wait until Oracle fixes these PL/SQL memory management problems.

> NOTE: The problem caused by unpacking messages with more than a few items in them can result in a very large PL/SQL table that has been fixed in Oracle PL/SQL8.

#### 3.1.7.3.2 The peek procedure

Developers or DBAs working with and testing DBMS_PIPE applications may really like the peek procedure built on top of the generic pack and unpack procedures. The peek procedure lets you pull a message off any pipe (which you have permission to use), look at its content, and place it back into the pipe, if you desire. Note that using peek will change the message order in the pipe, since database pipes are FIFO queues.

>      /* Filename on companion disk: dbpipe.sql */*
>     PROCEDURE peek
>     	(pipename_IN IN VARCHAR2
>     	,timeout_secs_IN IN INTEGER := 60
>     	,replace_message_TF IN BOOLEAN := TRUE)
>      IS
>     	/*
>     || Takes a sample message from a pipe, unpacks and displays
>     	|| contents using unpack_to_tbl procedure.
>     	||
>     	|| If replace_message_TF parameter is TRUE (the default),
>     	|| then the message is replaced into the pipe.NOTE: this
>     	|| will change message order in the pipe.
>     	*/

>     	message_tblmessage_tbltype;
>     	call_statusINTEGER;

>     	/* empty table used to free and release memory */
>     	null_message_tblmessage_tbltype;

>      BEGIN

>     	call_status := DBMS_PIPE.RECEIVE_MESSAGE
>     		(pipename=>pipename_IN, timeout=>timeout_secs_IN);

>     	IF call_status = 0
>     	THEN
>     		unpack_to_tbl(message_tbl, display_TF=>TRUE);

>     		IF replace_message_TF
>      		THEN
>     			/*
>     			|| repack message into initialized buffer
>     			*/
>     			DBMS_PIPE.RESET_BUFFER;
>     			pack_from_tbl(message_tbl);

>     			/*
>     			|| replace message on the pipe
>     			*/
>     			call_status := DBMS_PIPE.SEND_MESSAGE
>     				(pipename=>pipename_IN, timeout=>0);
>     		END IF;

>     	 	/*
>     		|| empty message_tbl and free memory
>     		*/
>     		message_tbl := null_message_tbl;
>     		DBMS_SESSION.FREE_UNUSED_USER_MEMORY;

>     	END IF;

>     END peek;

The peek procedure takes the memory management limitations into account. It returns memory consumed by the unpack_to_tbl procedure to the operating system by initializing message_tbl and calling DBMS_SESSION.FREE_UNUSED_USER_MEMORY.

#### 3.1.7.3.3 The forward procedure

The final fun utility from dbpipe to be discussed here is the forward procedure, which lets you forward a message from one pipe to another. The procedure has four IN parameters:

from_pipename_IN and to_pipename_IN

  Receiving and sending pipes for the message forwarding.

timeout_secs_IN

  Determines the number of seconds to wait for a message to forward (on the pipe from_pipename_IN).

safe_mode_IN

  A Boolean that determines which of two message forwarding techniques to use (which I call "safe" and "cool"). Safe mode forwarding uses unpack_to_tbl and pack_from_tbl to physically unbundle and recreate the message before sending it on to_pipename_IN. Cool mode forwarding is based on the idea that the best way to forward a message should be to execute DBMS_PIPE.RECEIVE_MESSAGE followed immediately by DBMS_PIPE.SEND_MESSAGE. After all, forwarding should be fast -- so why bother with the overhead of unpacking and repacking?

Well, it turns out that you cannot just receive and immediately send a message using DBMS_PIPE unless you have previously called the DBMS_PIPE.PACK_MESSAGE procedure. Why? I have no idea; it just seems to be another one of those mysteries of DBMS_PIPE that I happened to discover during my experimentation. I don't like the fact that it's mysterious, but I do like the fact that it works, so I used this "feature" to implement the cool forwarding mode.

>      /* Filename on companion disk: dbpipe.sql. */*
>     PROCEDURE forward
>     	(from_pipename_IN IN VARCHAR2
>     	,to_pipename_IN IN VARCHAR2
>     	,timeout_secs_IN IN INTEGER := 10
>     	,safe_mode_IN IN BOOLEAN := FALSE)
>     IS
>     	call_status INTEGER;
>     	message_tbl message_tbltype;

>     BEGIN
>     	/* initialize buffer */
>     	DBMS_PIPE.RESET_BUFFER;

>     	IF NOT safe_mode_IN
>     	THEN
>     		/*
>     		|| do an initial pack so COOL mode forwarding will work,
>     		|| why this is necessary is unknown
>     		*/
>     		DBMS_PIPE.PACK_MESSAGE('bogus message');
>     	END IF;

>     	/*
>     	|| receive the message on from_pipename, if success
>     	|| then forward on to_pipename
>     	*/
>     	call_status := DBMS_PIPE.RECEIVE_MESSAGE
>     				(pipename=>from_pipename_IN
>     				,timeout=>timeout_secs_IN);

>     	IF call_status = 0
>     	THEN
>     		/*
>     		|| safe mode does full unpack and repack
>     		*/
>     		IF safe_mode_IN
>     		THEN
>     		unpack_to_tbl(message_tbl);
>     		pack_from_tbl(message_tbl);
>     		END IF;

>     		/*
>     		|| OK, now send on to_pipename
>     		*/
>     		call_status := DBMS_PIPE.SEND_MESSAGE
>     				(pipename=>to_pipename_IN
>     				,timeout=>timeout_secs_IN);
>     	END IF;

>     END forward;

#### 3.1.7.4 Implementing a server program

One common application of DBMS_PIPE is to implement an external service interface, as mentioned previously. This interface allows Oracle users to communicate with host operating system programs and receive data from them into their session context. What about writing a service provider program internal to Oracle? That is, what about writing a PL/SQL program that will listen on a database pipe and provide certain Oracle-based services to client sessions connected to the same Oracle database?

There are a number of possible applications of such internal service programs, including:

- Complex calculation engines

- Debug message logging

- Audit message logging

- Transaction concentrators

- Batch program scheduling

#### 3.1.7.5 The pipesvr package

I have written a package that demonstrates how to use DBMS_PIPE to implement a basic PL/SQL server program and associated client programs. The package implements basic client-server communications, as well as a simple server-side debugger. Here is the specification for the pipesvr package:

>     /* Filename on companion disk: pipesvr.sql */*
>     CREATE OR REPLACE PACKAGE pipesvr
>     AS
>        /*
>        || Illustrates the use of DBMS_PIPE to implement
>        || communications between a PL/SQL background server
>        || program and client programs.
>        ||
>        || Clients communicate requests over a database pipe
>        || on which the server listens and receive responses
>        || on pipes unique to each session.
>        ||
>        || The server can be set to place debugging info into a
>        || table.
>        ||
>        || Author:  John Beresniewicz, Savant Corp
>        ||
>        || 10/04/97: created
>        ||
>        || Compilation Requirements:
>        ||
>        || EXECUTE on DBMS_PIPE
>        ||
>        || Execution Requirements:
>        ||
>        */

>        /*
>        || simple server program which listens indefinitely on
>        || database pipe for instructions
>        */
>        PROCEDURE server;

>        /*
>        || Client programs
>        */

>        /* stop the server */
>        PROCEDURE server_stop;

>        /* turn server debug mode toggle on or off */
>        PROCEDURE server_debug_on;
>        PROCEDURE server_debug_off;

>        /* get and display server status using DBMS_OUTPUT */
>        PROCEDURE server_status;

>     END pipesvr;

Once the server is running, it listens on a database pipe for client service requests. When a request is received, the server processes the request and goes back to listening on the pipe. In the case of the server_status client procedure call, the server sends its current status back to the client over a pipename unique to the session. The following record types and variables, declared in the package body of pipesvr, are used to implement the client-server communications:

>        /* used as a tag for this application */
>        app_id   VARCHAR2(10) := 'OPBIP$';

>        /* identifiers for message protocols */
>        request_protocol VARCHAR2(20) := app_id||'REQUEST$';
>        status_protocol  VARCHAR2(20) := app_id||'STATUS$';

>        /* server listens on this pipe */
>        request_pipe VARCHAR2(30) := app_id||'SERVER$';

>        /* client responses come on this pipe, unique to each client */
>        my_response_pipe VARCHAR2(100) := app_id||
>                                         DBMS_PIPE.UNIQUE_SESSION_NAME;

>        /*
>        || requests to server made in this format,
>        || should never need to override response_pipe
>        */
>        TYPE request_rectype IS RECORD
>              (response_pipe VARCHAR2(100) := my_response_pipe
>              ,service  stop_req%TYPE
>              );

>        /*
>        || server reports status in this format
>        */
>        TYPE status_rectype IS RECORD
>              (start_date    DATE
>              ,total_requests   INTEGER := 0
>              ,debug_status  VARCHAR2(5) := 'OFF'
>              );

>        /* private global for server current status */
>        status_rec status_rectype;

#### 3.1.7.5.1 Message types

Two record types have been declared for the two kinds of messages that will be handled: service request messages (sent from client to server) and server status messages (sent from server to client). Corresponding to each record (message) type is a protocol identifier to use when unpacking messages.

#### 3.1.7.5.2 Pipenames

The following pipenames are established for proper message separation:

- request_pipe, into which all client requests are placed for receipt by the server

- my_response_pipe, from which each session receives its response from the server

#### 3.1.7.5.3 Pack/send, receive/unpack encapsulation

In keeping with the best practices for safe pipe communications, the following four (package private) procedures are implemented in the body of pipesvr (only the specifications are shown below):

>       /* Filename on companion disk: pipesvr.sql */*
>       /*
>       || private program to put service request on pipe,
>       || called by client programs
>       */
>       PROCEDURE pack_send_request
>     	(request_rec_IN IN request_rectype
>         	 ,return_code_OUT OUT NUMBER);

>       /*
>       || private program to receive request on the
>       || request pipe
>       */
>       PROCEDURE receive_unpack_request
>     	(timeout_IN IN INTEGER
>          	,request_rec_OUT OUT request_rectype
>          	,return_code_OUT OUT NUMBER);

>       /*
>       || private program to put request on pipe,
>       || called by client programs
>       */
>       PROCEDURE pack_send_status
>          	(status_rec_IN IN status_rectype
>          	,response_pipe_IN IN my_response_pipe%TYPE
>          	,return_code_OUT OUT NUMBER);

>       /*
>       || private program to receive status on unique
>       || session pipe
>       */
>       PROCEDURE receive_unpack_status
>     	(timeout_IN IN INTEGER
>     	,status_rec_OUT OUT status_rectype
>     	,return_code_OUT OUT NUMBER);

#### 3.1.7.5.4 The server procedure

The server procedure itself is quite straightforward. It begins by creating the request pipe and initializing its private status record. Then it loops forever (or until the terminate_TF boolean is TRUE) on request_pipe for client requests using receive_unpack_request. Valid requests are passed on to the process_request procedure, which encapsulates the inelegant IF...THEN logic required to handle various types of requests. Finally, when the loop terminates, due to setting terminate_TF to TRUE, the pipe is removed and the program ends.

The code for the server is surprisingly simple.

>     /* Filename on companion disk: pipesvr.sql */*
>     PROCEDURE server
>     IS
>     	request_rec  request_rectype;
>     	temp_return_code  NUMBER;

>     BEGIN
>     	/* create pipe */
>     	temp_return_code := DBMS_PIPE.CREATE_PIPE(request_pipe);

>     	/* initialize status rec */
>     	status_rec.start_date := SYSDATE;
>     	status_rec.total_requests := 0;
>     	status_rec.debug_status := 'OFF';

>     	/*
>     	|| loop forever and process requests
>     	*/
>     	WHILE NOT terminate_TF
>     	LOOP
>     		receive_unpack_request
>     			(timeout_IN => DBMS_PIPE.maxwait
>     			,request_rec_OUT=> request_rec
>     			,return_code_OUT => temp_return_code);

>     		IF temp_return_code != 0
>     		THEN
>     			DBMS_PIPE.PURGE(request_pipe);
>     			debug('REQUEST PIPE STAT: '||temp_return_code);
>     		ELSE
>     			process_request(request_rec);
>     			debug('REQUEST PROCESSED');
>     		END IF;
>     	END LOOP;

>     	/*
>     	|| terminating: remove pipe and exit
>     	*/
>     	temp_return_code := DBMS_PIPE.REMOVE_PIPE(request_pipe);

>     EXCEPTION
>     	WHEN OTHERS THEN
>     		debug('SERVER EXCP: '||SQLERRM, force_TF_IN=>TRUE);
>     		temp_return_code := DBMS_PIPE.REMOVE_PIPE(request_pipe);

>     END server;

#### 3.1.7.5.5 The process_request procedure

When the server procedure receives a valid service request, it calls the process_request procedure. This procedure has the responsibility of interpreting the service request and performing the requested action. Note that this procedure sets the terminate_TF Boolean, which stops the server. You must always code a stop routine into this type of service program, or you will have to kill the process running the procedure. Other services performed by process_request include setting debugging to on or off, and sending the server's current status_rec back to the requesting session on a database pipe using pack_send_status.

>     /* Filename on companion disk: pipesvr.sql */*
>     /*
>     || private program to encapsulate request processing
>     || logic (lots of IF...THEN stuff) of server
>     */
>     PROCEDURE process_request
>     	(request_rec_IN IN request_rectype)
>     IS
>     	temp_return_code NUMBER;
>     BEGIN
>     	/* increment total */
>     	status_rec.total_requests := status_rec.total_requests +1;

>     	/* stop the server, this is a MUST have */
>     	IF request_rec_IN.service = stop_req
>     	THEN
>     		terminate_TF := TRUE;

>     	ELSIF request_rec_IN.service = debugon_req
>     	THEN
>     		debug_TF := TRUE;
>     		status_rec.debug_status := 'ON';

>     	ELSIF request_rec_IN.service = debugoff_req
>     	THEN
>     		debug_TF := FALSE;
>     		status_rec.debug_status := 'OFF';

>     	ELSIF request_rec_IN.service = status_req
>     	THEN
>     		pack_send_status
>     			(status_rec_IN=>status_rec
>     			,response_pipe_IN=> request_rec_IN.response_pipe
>     			,return_code_OUT=> temp_return_code);
>     		debug('SEND STATUS: '||temp_return_code);
>     	/* unrecognized request */

>     	ELSE
>     		DBMS_PIPE.RESET_BUFFER;
>     		debug('UNKNOWN REQUEST: '||request_rec_IN.service);
>     	END IF;

>     END process_request;

#### 3.1.7.5.6 Debug procedure

Notice that the server procedure makes several calls to a procedure called debug. The debug procedure dumps informational messages into a simple table to allow tracking of server-side events. This procedure usually inserts data to the table only if the server is in debug mode, as determined by the debug_TF global variable. This prevents too many debugging rows from being inserted when the server is operating normally. The debug procedure can be forced to write to the table by setting a parameter called force_TF_IN to TRUE. This is used to guarantee that certain debugging information gets into the table regardless of the server's current debug mode. Debugging messages are usually forced in exception handlers, as in the server procedure. Here is the source code for debug:

>     /* Filename on companion disk: pipesvr.sql */*
>     /*
>     || private program to put debug messages into table
>     || if boolean is TRUE, or if force_IN is TRUE
>     || NOTE: commits after inserting row
>     */
>     PROCEDURE debug
>     	(message_IN IN VARCHAR2
>     	,force_TF_IN IN BOOLEAN := FALSE)
>     IS
>     BEGIN
>     	IF debug_TF OR force_TF_IN
>     	THEN
>     		INSERT INTO pipesvr_debug
>     		VALUES (SYSDATE, message_IN);
>     		COMMIT;
>     	END IF;
>     END debug;

#### 3.1.7.5.7 Client side procedures

The four client-side procedures all have to send specific request records to the server, and the request records they use vary only in the contents of the service field. I reduced code redundancy by creating the client_request procedure as follows:

>     /* Filename on companion disk: pipesvr.sql. */*
>     /*
>     || private program to make simple service requests,
>     || if request_rectype gets more complex or need more
>     || flexibility use pack_send_request instead
>     */
>     PROCEDURE client_request(request_IN IN stop_req%TYPE)
>     IS

>     	request_rec request_rectype;
>     	temp_return_code  NUMBER;

>     BEGIN
>     	request_rec.service := request_IN;

>     	pack_send_request
>     		(request_rec_IN => request_rec
>     		,return_code_OUT => temp_return_code);

>     END client_request;

The client_request procedure loads the service field of a request record and then calls pack_send_request. This procedure helps simplify client programs, as seen in server_stop:

>     PROCEDURE server_stop
>     IS
>     BEGIN
>     	client_request(stop_req);
>     END server_stop;

The server_status procedure calls client_request and then waits for up to a minute on the receive_unpack_status procedure. If a status record is successfully received from the server, it is displayed using the DBMS_OUTPUT package.

I hope the pipesvr package will serve as a useful template to those seeking to implement PL/SQL service programs. It works and it incorporates the best practices for using DBMS_PIPE, so it should be a good starting point.

2.5 DBMS_SQL Examples |  | 3.2 DBMS_ALERT: Broadcasting Alerts to Users

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.