Oracle Pipes

To explain the purpose of the dbms_pipe package, take into account the use of pipe in UNIX,
represented by the ?|? symbol, which allows the communication between one command and another. In
other words, dbms_pipe allows inter-session communication on the same database.

Alone, this is not a very reliable way of message exchange as all information generated by dbms_pipe
is stored in the System Global Area (SGA).  As with any information inside SGA, it will be lost if
the database goes down. Thus, applications that use it often combine it with Streams, proprietary
methods to store the data sent, or rely on it only for non-critical data exchange.

To increase the security of this communication, two pipes can be used; private pipes and public
pipes.  Public pipes are dropped when there is no more data in it.  They may be created implicitly,
i.e. created automatically when first referenced, or explicitly, i.e. using the create_pipe
procedure. The script used for creating the dbms_pipe package is dbms_pipe.sql and can be found at
$ORACLE_HOME/rdbms/admin.

The next example works as an alert to inform an application that data has changed and needs to have
its cache refreshed in order to show the new information. Suppose that there is an employees table
and we want to know each time the salary field is updated.  To accomplish this, create a trigger that
sends updated information to a session that is waiting for these changes.

irst of all, create the employees table that will be used in this example.


create or replace package test_dbms_pipe as
  procedure send_message_pipe (v_name in varchar2, v_sal in number, v_date in date default sysdate);
  procedure receive_message_pipe;
  procedure log_message (v_name in out varchar2, v_sal in out number, v_date in out date);
end test_dbms_pipe;
/

create or replace package body test_dbms_pipe as

procedure send_message_pipe

(

v_name in varchar2,

                  v_sal  in number,

                        v_date in date

                ) as

    v_status number;

  begin

    dbms_pipe.pack_message(v_name);

    dbms_pipe.pack_message(v_sal);

    dbms_pipe.pack_message(v_date);

    v_status := dbms_pipe.send_message('message from pipe!');

    if v_status != 0 then

      raise_application_error(-20001, '!! message pipe error !!');

    end if;

  end send_message_pipe;

  --Create the procedure that will receive the message pipe

  procedure receive_message_pipe as

    v_result integer;

    v_name_r varchar2(3000);

    v_sal_r  number;

  begin

    v_result := dbms_pipe.receive_message(

                    pipename => 'message from pipe!',

                    timeout  => 10);

    if v_result = 0 then

      while v_result = 0 loop

v_result := dbms_pipe.receive_message(

pipename => 'message from pipe!',

timeout  => 10);

dbms_pipe.unpack_message(v_name_r);

dbms_pipe.unpack_message(v_sal_r);

dbms_output.put_line('Full Name: ' || v_name_r);

dbms_output.put_line('Salary: ' || v_sal_r);

      end loop;

    else

      if v_result = 1 then

        dbms_output.put_line('Timeout limit exceeded!');

      else

        raise_application_error(-20002,

                                'error receiving message pipe: ' ||

                                v_result);

      end if;

    end if;

  exception

    when others then

      null;

  end receive_message_pipe;

end test_dbms_pipe;

Create the trigger on the employees table that will use the send_message_pipe procedure to send

information to the pipe. This information will be read with the receive_message_pipe procedure.

--Create the trigger on employee table using the send procedure

create or replace trigger employees_upd_sal

after insert on employees

    for each row

declare

   v_date_1 date;

begin

   v_date_1 := sysdate;

   test_dbms_pipe.send_message_pipe(v_name => :new.name, v_sal => :new.sal);

exception

   when others then

     raise_application_error(num => -20002,

                            msg => 'error message on trigger!');

end employees_upd_sal;

/

Finally, we open two sessions. In one, we execute the receive_message_pipe procedure that has a

timeout configured for 15 seconds. This session will wait for 15 seconds if no inserts are being

made to the employees table. After the 15 seconds, the timeout finishes and shows the values that

were inserted in the table.

--On the first session, execute the procedure receive_message that will output values being inserted in employees table.

set serveroutput on

exec test_dbms_pipe.receive_message_pipe;

--On the second session execute some insert commands on employees table.

insert into employees (name,sal) values ('John Paul',300000);

insert into employees (name,sal) values ('Mike',350000);

insert into employees (name,sal) values ('Brad',400000);

commit;

After we wait for fifteen seconds; the results of the first session will then be displayed.

To check pipes created on the database, use the v$db_pipes view as shown:

col name for a30

select

   *

from

   v$db_pipes;

OWNERID    NAME                           TYPE     PIPE_SIZE

---------- ------------------------------ ------- ----------

           message from pipe!             public        4480

To recap, one session has communicated with the other via send_message_pipe and receive_message_pipe procedures which are inside the dbms_pipe package. This is commonly known as inter-session communication.