DBMS PIPE

DBMS_PIPE is a PL/SQL package that allows two or more sessions in the same Oracle instance to communicate with each other (inter-session messaging), similar in concept to a Unix pipe.

Example

Session 1: Create a pipe and listen for messages on it:

DECLARE
  status    NUMBER;
  message   VARCHAR2(80);
  pipe_name VARCHAR2(30) := 'MY_PIPE';
BEGIN
  -- Create a pipe
  status := DBMS_PIPE.create_pipe(pipe_name);

  -- Listen for incoming messages on the pipe
  status := DBMS_PIPE.receive_message(pipename => pipe_name,
                                       timeout  => DBMS_PIPE.maxwait);

  -- Message received successfully.
  IF status = 0 THEN
     DBMS_PIPE.unpack_message(message);
     DBMS_OUTPUT.put_line('Message received: ' || message);
  END IF;
END;
/

Session 2: Send a message into the pipe:

DECLARE
  status    NUMBER;
  message   VARCHAR2(80) := 'Hello you on the other side';
  pipe_name VARCHAR2(30) := 'MY_PIPE';
BEGIN
  DBMS_PIPE.pack_message(message);
  status := DBMS_PIPE.send_message(pipe_name);
END;
/

Monitor

SQL> SELECT * FROM v$db_pipes;
   OWNERID NAME                           TYPE     PIPE_SIZE
---------- ------------------------------ ------- ----------
         0 MY_PIPE                        PRIVATE       1400