This case study will explain how the Oracle Pipes work and how to use DBMS_PIPE. 

What is the Oracle Pipe?
--------------------
The Oracle Pipe is a type of communication between sessions in the same instance of a database. Messages are put inside the pipe by one session and other takes the message out. 

You can have a pipe that is public or private. When a pipe is public, it means everybody can receive or send messages to it. And when it is a private then only the session with the same
user can access it.

Gathered By: John Kazerooni

This case study will explain how the Oracle Pipes work and how to 
use DBMS_PIPE. 

What is the Oracle Pipe?
--------------------
The Oracle Pipe is a type of communication between sessions in
the same instance of a database. Messages are put inside the pipe
by one session and other takes the message out. 

You can have a pipe that is public or private. When a pipe is
public, it means everybody can receive or send messages to it. 
And when it is a private then only the session with the same
user can access it.

Make sure that user(s) have grant execute on the dbms_pipe,
dbms_output packages.

Run the dbmspipe.sql script, if you don't have this package or
you get the following error message:
PLS-00201: identifier 'DBMS_PIPE' must be declared
ORA-06550: line ?, column ?:

start %ORACLE_HOME%\rdbms\admin\dbmspipe.sql

--This procedure will send a message.
CREATE OR REPLACE PROCEDURE send_message 
(p_message VARCHAR2)
AS
    result NUMBER;
    my_pipe VARCHAR2(30); 
    user user_users%ROWTYPE; 

BEGIN
    --Query username.
    SELECT * INTO user FROM user_users;

    -- Get a unique session identifier.
    my_pipe:=dbms_pipe.UNIQUE_SESSION_NAME;

    -- Put the messages inside the private buffer.
    dbms_pipe.pack_message(my_pipe);

    -- Send the messages stored into the private buffer thru the pipe. 
    result:=dbms_pipe.send_message('waiter');

    -- Put the messages inside the private buffer.
    dbms_pipe.pack_message(user.USERNAME); 
    dbms_pipe.pack_message(p_message); 

    -- Send the messages.
    result:=dbms_pipe.send_message(my_pipe); 
END send_message; 
/

-- This procedure will get the message.
CREATE OR REPLACE PROCEDURE get_message
AS
    result number; 
    v varchar2(4000); 
    his_pipe varchar2(30); 

BEGIN
    -- Wait for message to appear in the pipe
    -- up to value of maxwait in seconds and then
    -- place them on the private buffer.
    -- The return value of 0, it means that process
    -- was successful.
    result:=dbms_pipe.RECEIVE_MESSAGE('waiter');

    -- Retrieve the messages from the buffer and 
    -- place them in variables based on their types. 
    dbms_pipe.unpack_message(his_pipe); 
    dbms_output.put_line('Pipe :'||his_pipe); 

    result:=dbms_pipe.RECEIVE_MESSAGE(his_pipe); 
    result:=dbms_pipe.next_item_type;

    WHILE result<>0 LOOP
        -- varchar2 
        dbms_pipe.unpack_message(v); 
        dbms_output.put_line(v); 
        result:=dbms_pipe.next_item_type; 
    END LOOP;

EXCEPTION 
    WHEN others THEN
        dbms_output.put_line('error: '||to_char(result)); 
        dbms_pipe.purge(his_pipe); 
END;
/ 

-- Query all pipes that you have.

COL name FORMAT A40 
SELECT * FROM v$db_pipes
/

-- This procedure will remove all your pipes.
CREATE OR REPLACE PROCEDURE clean_pipes
IS
    result number; 
BEGIN 
    FOR i IN (SELECT * FROM v$db_pipes) LOOP
        dbms_output.put('Pipe '||i.name);
        BEGIN 
            result:=dbms_pipe.remove_pipe(i.name); 
            dbms_output.put_line(' removed.');
        EXCEPTION 
            WHEN others THEN
                dbms_output.put_line(' not removed.'); 
        END; 
    END LOOP; 
END clean_pipes; 
/

To execute the procedures:

EXECUTE send_message ('this is my message');

EXECUTE get_message;
EXECUTE clean_pipes;

Good Luck!