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

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.

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;