Of course, at any time, multiple sessions can be writing to the same pipe, and multiple sessions can be reading from the same pipe. A great application use, especially for multiple processor servers, would be an application for student registration. Two or more terminals can use the same form to send data to the same pipe concerning registration of students. The pipe can be read by multiple sessions to then process the records as each one comes across the pipe to process the records much faster. In return, you can send triggers, which can also use pipes if the enrollment gets too large.

By default, pipes will retain the information for up to 1,000 days. The constant is defined in Oracle as

Maxwait CONSTANT INTEGER := 86400000;

The constant is expressed in seconds, so 60 seconds/minute * 60 minutes/hour * 24 hours/day * 1000 days = 8,640,000 seconds. Of course, you could change the default to increase or decrease the time the pipe will retain the data.

When naming a pipe, there are some naming conventions you should follow. You should never begin a pipe name with ORA$, which is reserved for use by Oracle. The pipe name can be up to 128 characters. Also, you should make sure that the pipe name is always unique. When in doubt, assign the name of the pipe to an Oracle-defined name by using the function UNIQUE_SESSION_NAME.

You can change the pipe size from the default of 8192 bytes. You still have to deal with a 4096-byte limitation on the message buffer.

Table 19.7 lists all functions and procedures used with the DBMS_PIPE package.

Table 19.7. DBMS_PIPE functions and procedures.

                                                 continues

Table 19.7. continued

The Functions and Procedures of DBMS_PIPE

This section discusses the functions and procedures in more detail, including the syntax and another complete hands-on example for passing data back and forth between pipes.

The CREATE_PIPE Function
As stated earlier, you primarily need the CREATE_PIPE function to create private pipes.

The Syntax for the CREATE_PIPE Function
The syntax for the CREATE_PIPE function is

FUNCTION CREATE_PIPE(name_of_pipe IN VARCHAR2,
                    pipesize IN INTEGER DEFAULT 8192,
                    private IN BOOLEAN DEFAULT true);
RETURN INTEGER; -- Status on pipe creation

name_of_pipe is the name you will assign to the pipe. The next parameter is pipesize, which is the maximum size of the pipe. The default is 8192 bytes, which can be changed to the size desired. The private parameter simply states whether the pipe is private or public based upon the BOOLEAN value passed. The value should be false for public and true for private. Remember, by calling SEND_MESSAGE, you do not have to use CREATE_PIPE to create a public pipe!

An example of creating a private pipe is simply

v_status := DBMS_PIPE.CREATE_PIPE(`mypipe');

You just created a pipe with a maximum size of 8192 bytes, which is also private (default of true). Remember, this is a function with a return type of status.

To create the public pipe, the code would look similar to

v_status := DBMS_PIPE.CREATE_PIPE(`itpublic',8192,false);

You now have a public pipe called itpublic with a size of 8192 bytes.

If the return value (this example uses the variable v_status) is zero, the pipe was successfully created. If the user does not have access rights to create a pipe, or the pipe name exists, the ORA-23322 exception is raised.

The PACK_MESSAGE Procedure
After a pipe is created (or will be created with SEND_MESSAGE for public pipes), you can send data to the message buffer for later transmittal to the pipe using the PACK_MESSAGE procedure. Because the function is overloaded, you can send a datatype of VARCHAR2, DATE, or NUMBER.

The Syntax for the PACK_MESSAGE Procedure
The format for PACK_MESSAGE is

PROCEDURE PACK_MESSAGE(data IN VARCHAR2);
PROCEDURE PACK_MESSAGE(data IN DATE);
PROCEDURE PACK_MESSAGE(data IN NUMBER);

data is the data that you are sending to the buffer. Remember that the buffer has only 4096 bytes available for use. If you go over this limitation, you will receive the following error:

ORA-06558 buffer in DBMS_PIPE package is full.
No more items allowed.

Using the SEND_MESSAGE Function
Before you overflow your message buffer, you should send the data to the pipe with the SEND_MESSAGE function. This function moves the data in the message buffer to the pipe specified from the function call.

The Syntax for the SEND_MESSAGE Function
The format for the function SEND_MESSAGE is

FUNCTION SEND_MESSAGE(name_of_pipe IN VARCHAR2,
                    timeout IN INTEGER DEFAULT maxwait,
                    pipesize IN INTEGER DEFAULT 8192);
RETURN INTEGER;

name_of_pipe is the name of the pipe already in existence, whether private or public. If no pipe of this name exists, Oracle will create one upon successful execution of SEND_MESSAGE. timeout is how long it will attempt to place the message in the pipe, in seconds. The default is 1000 days. Finally, because you can create a public pipe on execution, you should be able to control the size of the pipe.

The SEND_MESSAGE values are listed in Table 19.8.

Table 19.8. Return values from SEND_MESSAGE.