Intersession Communication

Intersession communication is the ability to communicate between two different user connections, you have two ways in which you can communicate between sessions

Normally in order for two session to communicate you can use would use a permanent structure (table) to allow one session to access data in other, for instance you could use table to exchange data, however this is controlled by transaction control limitations, the transaction must complete and be comitted before the other user can see the data.

In order to communicate between two session without using a permanent structure and use memory instead (SGA) you can use and do the following:

DBMS_PIPE

uses dynamic memory structures in the SGA called pipes. They are very simular to Unix pipes. Pipes can either be local, public or private accessible. They act as first-in first-out (FIFO) queues. you use pipes to send and receive data between sessions asynchronously.

Are ideal for passing information to external processes that may moniotr or control system resources,
local pipes to control a single programs execution
private pipes to control concurrent programs run by the single user
public pipes to control concurrent programs run by multiple users.

DBMS_ALERT also uses memory structure in the SGA, alhrough not a pipe it works as a public pipe. They are publicly accessible pipes or FIFO queues. These pipes are populated on event triggers and subject to transaction control limits. It works on a publish and subscribe paradigm, it publishes notifications and then it enables subscribers to register their interest in the alert and receive the alert notifications.

DBMS_PIPE

dbms_pipe package is owned by the sys user, you must have execute on this package before using it. You basically have a buffer and a pipe, depending on what you setup (local, private or public) you place packed messages in the local buffer which then get copied to the pipe, you then take the packed message from the pipe into another local buffer which then you unpack the message in the local buffer (see below diagram).

There three types of pipe

local is only a buffer, the buffer can only contain one element, you write a variable-length string to the local buffer, then you may read the string from the buffer, if the buffer is written again before you read it the previous data is lost.
private

private pipes are accessible to all sessions of the user who created the pipe, before writing to the pipe you write to a local buffer first, then you send the contents of the buffer to the pipe, the pipe then can be read into the other sessions buffer where it can be read. The same session can read/write to the private pipe. A private pipe man contain any number of values in a FIFO queue.

By tokenizing the strings you can add indexing which will allow you to order the data, once you have got it from the pipe. Its a bit like a network data, the packets are assembled when all of them have been recieved.

public This is the same as a private pipe but the pipe is accessible to any user with a session.

There are many functions that the dbms_pipe contains

Functions
CREATE_PIPE create a pipe which you can specify the size (default 8192) , name and whether it is a private private
NEXT_ITEM_TYPE reads the contents of the local pipe or buffer, it returns a integer describing the type

0  - an empty buffer
6  - a number datatype
9  - a varchar2 datatype
11 - a rowid datatype
12 - a date datatype
23 - a raw datatype
PACK_MESSAGE takes the value of the acutal parameter and puts it into the local pipe or buffer
PACK_MESSAGE_RAW takes the value of the acutal parameter and puts it into the local pipe or buffer
PACK_MESSAGE_ROWID takes the value of the acutal parameter and puts it into the local pipe or buffer
PURGE clears the local pipe or buffer
RECEIVE_MESSAGE reads the contents of the named pipe and transfers it to the localbuffer
REMOVE_PIPE removes the named pipe
RESET_BUFFER removes the contents of the local buffer
SEND_MESSAGE writes the contents of the local buffer to the named pipe
UNIQUE_SESSION_NAME  
UNPACK_MESSAGE takes a value from the local pipe or buffer and returns it as the out mode value of the actual parameter
UNPACK_MESSAGE_RAW takes a value from the local pipe or buffer and returns it as the out mode value of the actual parameter
UNPACK_MESSAGE_ROWID takes a value from the local pipe or buffer and returns it as the out mode value of the actual parameter
Examples
sending to and receiving from the local pipe or buffer
-- Write to the local buffer
-- Define an anonymous block to populate the local private pipe. DECLARE -- Define variables for functions and procedures. message VARCHAR2(30 CHAR); success INTEGER; BEGIN -- Assign the unique session name to message. message := DBMS_PIPE.UNIQUE_SESSION_NAME; -- Reset the local private pipe. DBMS_PIPE.RESET_BUFFER; -- Write a message to the local private pipe. DBMS_PIPE.PACK_MESSAGE(message); -- Write what was written to the pipe. DBMS_OUTPUT.PUT_LINE('Written to pipe ['||message||']'); END; / -- Read from the local buffer -- Define an anonymous block to populate the local private pipe. DECLARE -- Define variables for functions and procedures. message VARCHAR2(30 CHAR); success INTEGER; BEGIN -- Read a message from the local private pipe. DBMS_PIPE.UNPACK_MESSAGE(message); -- Print the contents of the message. DBMS_OUTPUT.PUT_LINE('Message ['||message||']'); END; /
Creating pipes
-- Ceate a private pipe
DECLARE message_pipe varchar2(30) := 'PLSQL$MESSAGE_INBOX';
  message_size integer      := 20000;
  retval integer; BEGIN -- Define a private pipe. retval := DBMS_PIPE.CREATE_PIPE(message_pipe, message_size); -- Print the retval status. IF (retval = 0) THEN DBMS_OUTPUT.PUT_LINE('MESSAGE_INBOX pipe is created.'); END IF; EXCEPTION -- Raise generic exception. WHEN others THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); RETURN; END; / -- Create a public pipe DECLARE -- Define and declare variables. message_pipe VARCHAR2(30) := 'PLSQL$MESSAGE_INBOX'; message_size INTEGER := 20000; message_flag BOOLEAN := FALSE;                    ## The public pipe flag -- Function output variable. retval INTEGER; BEGIN -- Define a public pipe. retval := DBMS_PIPE.CREATE_PIPE(message_pipe ,message_size ,message_flag);         ## Create the public pipe -- Print the retval status. IF (retval = 0) THEN DBMS_OUTPUT.PUT_LINE('MESSAGE_INBOX pipe is created.'); END IF; EXCEPTION -- Raise generic exception. WHEN others THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); RETURN; END; /
writing to and reading from pipes
-- Write to a named pipe
DECLARE
  -- Define line return to separate pipe writes.
  line_return VARCHAR2(1) := CHR(10);

  -- Define a return value
  flag        INTEGER;
BEGIN
  -- Purge pipe content.
  dbms_pipe.purge('PLSQL$MESSAGE_INBOX');

  -- Print input title.
  DBMS_OUTPUT.PUT_LINE('Input Message to Pipe');
  DBMS_OUTPUT.PUT_LINE('---------------------');

  -- Use a range for-loop to send three messages.
  FOR i IN 1..3 
  LOOP -- Print the input line. DBMS_OUTPUT.PUT_LINE('Message ['||i||']'); -- Use the procedure to put a message in the local buffer. DBMS_PIPE.PACK_MESSAGE('Message ['||i||']'||line_return); -- Send message, success is a zero return value. flag := DBMS_PIPE.SEND_MESSAGE('PLSQL$MESSAGE_INBOX'); END LOOP; -- Print message based on flag status. IF (flag = 0) THEN DBMS_OUTPUT.PUT_LINE('Message sent to PLSQL$MESSAGE_INBOX.'); END IF; END; / -- Read from a named pipe DECLARE -- Define message variable. line_return VARCHAR2(1) := CHR(10); message VARCHAR2(4000); output VARCHAR2(4000); -- Define a return value flag INTEGER; BEGIN -- Print input title. DBMS_OUTPUT.PUT(line_return); DBMS_OUTPUT.PUT_LINE('Output Message from Pipe'); DBMS_OUTPUT.PUT_LINE('------------------------'); -- Use range for-loop to receive and read three messages. FOR i IN 1..3
  LOOP -- Reset the local buffer. DBMS_PIPE.RESET_BUFFER; -- Receive message, success is a zero return value. flag := DBMS_PIPE.RECEIVE_MESSAGE('PLSQL$MESSAGE_INBOX',0); -- Read message from local buffer. DBMS_PIPE.UNPACK_MESSAGE(message); -- Append message to output variable. output := output || message; END LOOP; -- Print message based on flag status. IF (flag = 0) THEN -- Print the output variable. DBMS_OUTPUT.PUT(output); -- Print confirmation message. DBMS_OUTPUT.PUT_LINE( 'Message received from PLSQL$MESSAGE_INBOX.'); END IF; END; /
Using the next_item_type
DECLARE

  -- Define session.
  session     VARCHAR2(30) := DBMS_PIPE.UNIQUE_SESSION_NAME;

  -- Define line return to separate pipe writes.
  line_return VARCHAR2(1) := CHR(10);
  message     VARCHAR2(4000);
  output      VARCHAR2(4000);

  -- Define a return values.
  flag        INTEGER;
  code        INTEGER;

  -- Define and declare input variables.
  message1    INTEGER     := 1776;
  message2    DATE        := TO_DATE('04-JUL-1776');
  message3    VARCHAR2(30 CHAR) := 'John Adams';

  -- Define output variables.
  message11   INTEGER;
  message12   DATE;
  message13   VARCHAR2(30 CHAR);

BEGIN

  -- Purge pipe content.
  DBMS_PIPE.PURGE('PLSQL$MESSAGE_INBOX');


  -- Print input title.
  DBMS_OUTPUT.PUT_LINE('Input Message to Pipe');
  DBMS_OUTPUT.PUT_LINE('Session: ['||session||']');
  DBMS_OUTPUT.PUT_LINE('--------------------------------');


  -- Do the following for message1, message2 and message3:
  -- 1. Print the input line.
  -- 2. Use the procedure to put a message in local buffer
  --    of a specific data type. 
  -- 3. Send message, success is a zero return value.

  -- Process message1.
  DBMS_OUTPUT.PUT_LINE(message1||'[NUMBER]');
  DBMS_PIPE.PACK_MESSAGE(message1);
  flag := DBMS_PIPE.SEND_MESSAGE('PLSQL$MESSAGE_INBOX');


  -- Process message2.
  DBMS_OUTPUT.PUT_LINE(message2||'[DATE]');
  DBMS_PIPE.PACK_MESSAGE(message2);
  flag := DBMS_PIPE.SEND_MESSAGE('PLSQL$MESSAGE_INBOX');


  -- Process message3.
  DBMS_OUTPUT.PUT_LINE(message3||'[VARCHAR2]');
  DBMS_PIPE.PACK_MESSAGE(message3);
  flag := DBMS_PIPE.SEND_MESSAGE('PLSQL$MESSAGE_INBOX');


  -- Print message based on flag status.
  IF (flag = 0) THEN
    DBMS_OUTPUT.PUT_LINE('Message sent to PLSQL$MESSAGE_INBOX.');
  END IF;


  -- Print input title.
  DBMS_OUTPUT.PUT(line_return);
  DBMS_OUTPUT.PUT_LINE('Output Message from Pipe');
  DBMS_OUTPUT.PUT_LINE('Session: ['||session||']');
  DBMS_OUTPUT.PUT_LINE('--------------------------------');


  -- Use range for-loop to receive and read three messages.
  FOR i IN 1..3 
  LOOP -- Reset the local buffer. DBMS_PIPE.RESET_BUFFER; -- Receive message, success is a zero return value. flag := DBMS_PIPE.RECEIVE_MESSAGE('PLSQL$MESSAGE_INBOX',0); -- Get the item type from the buffer contents. code := DBMS_PIPE.NEXT_ITEM_TYPE; -- Use case statement to return string. CASE code -- When buffer contents is a NUMBER. WHEN 6 THEN -- Unpack into a NUMBER variable type. DBMS_PIPE.UNPACK_MESSAGE(message11); output := output || message11 ||'[NUMBER]'||line_return; -- When buffer contents is a VARCHAR2. WHEN 9 THEN -- Unpack into a VARCHAR2 variable type. DBMS_PIPE.UNPACK_MESSAGE(message13); output := output || message13 ||'[VARCHAR2]'||line_return; -- When buffer contents is a DATE. WHEN 12 THEN -- Unpack into a DATE variable type. DBMS_PIPE.UNPACK_MESSAGE(message12); output := output || message12 ||'[DATE]'||line_return; END CASE; END LOOP; -- Print message based on flag status. IF (flag = 0) THEN -- Print the output variable. DBMS_OUTPUT.PUT(output); -- Print confirmation message. DBMS_OUTPUT.PUT_LINE( 'Message received from PLSQL$MESSAGE_INBOX.'); END IF; END; /
putting a wrapper around DBMS_PIPE
-- Create the messager wrapper program
-- Create package specification. CREATE OR REPLACE PACKAGE messenger IS -- Define function specification. FUNCTION send_message (user_name VARCHAR2 ,message VARCHAR2 ,message_box VARCHAR2 DEFAULT 'MESSAGE_INBOX') RETURN INTEGER; -- Define function specification. FUNCTION receive_message RETURN VARCHAR2; END messenger; / -- Create package body. CREATE OR REPLACE PACKAGE BODY messenger IS -- Define local package function to return user name. FUNCTION get_user RETURN VARCHAR2 IS BEGIN -- Use a cursor for-loop to get user name. FOR i IN (SELECT user FROM dual)
    LOOP -- Return the user. return i.user; END LOOP; END get_user; -- Implement package function defined in specification. FUNCTION send_message (user_name VARCHAR2 ,message VARCHAR2 ,message_box VARCHAR2 DEFAULT 'MESSAGE_INBOX') RETURN INTEGER IS -- Define variable for target mailbox. message_pipe VARCHAR2(100 CHAR); BEGIN -- Purge local pipe content. DBMS_PIPE.RESET_BUFFER; -- Declare the target outbox for a message. message_pipe := UPPER(user_name) || '$' || UPPER(message_box); -- Use the procedure to put a message in the local buffer. DBMS_PIPE.PACK_MESSAGE(message); -- Send message, success is a zero return value. IF (DBMS_PIPE.send_message(message_pipe) = 0) THEN -- Message sent, so return 0. RETURN 0; ELSE -- Message not sent, so return 1. RETURN 1; END IF; END send_message; -- Implement package function defined in specification. FUNCTION receive_message RETURN VARCHAR2 IS -- Define variable for target mailbox. message VARCHAR2(4000 CHAR) := NULL; message_box VARCHAR2(100 CHAR); inbox VARCHAR2(14 CHAR) := 'MESSAGE_INBOX'; timeout INTEGER := 0; return_code INTEGER; BEGIN -- Purge local pipe content. DBMS_PIPE.RESET_BUFFER; -- Declare the target outbox for a message. message_box := get_user || '$' || inbox; -- Use the procedure to put a message in the local buffer. return_code := DBMS_PIPE.receive_message(message_box,timeout); -- Evaluate and process return code. CASE return_code WHEN 0 THEN -- Read the message into a variable. DBMS_PIPE.UNPACK_MESSAGE(message); WHEN 1 THEN   -- Assign message.   message := 'The message pipe is empty.'; WHEN 2 THEN   -- Assign message.   message := 'The message is too large for variable.'; WHEN 3 THEN   -- Assign message.   message := 'An interrupt occurred, contact the DBA.'; END CASE; -- Return the message. RETURN message; END receive_message; END messenger; / -- Use the wrapper program DECLARE -- Define local package function to return user name. FUNCTION get_user RETURN VARCHAR2 IS BEGIN -- Use a cursor for-loop to get user name. FOR i IN (SELECT user FROM dual)
    LOOP -- Return the user. return i.user; END LOOP; END get_user; BEGIN -- Send a message. IF (MESSENGER.SEND_MESSAGE(get_user,'Hello World!') = 0)
  THEN -- Receive and print message. DBMS_OUTPUT.PUT_LINE(MESSENGER.RECEIVE_MESSAGE); END IF; END; /

DBMS_ALERT

dbms_alert is an asynchronronous transaction control mechanism, it publishes an event and other users become subscribers by registering their interest in the named alert, so it is a basic publish and subscribe mechanism.dbms_alert implements public pipes through using the dbms_pipe package, it also uses the dbms_pipe memory structure in the SGA, although not a pipe it works like a public pipe. The pipes are populated by event triggers and are subject to transaction control limits. It publishes notifications then it enables subscribers to register to recieve the event notifications.

There are a number of procedures associated with the dbms_alert package, again you should consult the Oracle documentation for a full explaination.

dbms_alert procedures
REGISTER used to subscribe to one or more alerts
REMOVE used to unsubscribe to one or more alerts
REMOVEALL used to unsubscribe to all alerts
SET_DEFAULTS used to alter the default polling interval time, by default it is 5 seconds
SIGNAL signals an alert
WAITONE waits timeout seconds to receive message from a named alert.
Examples
Building a trigger to signal an alert
-- Create a signaling trigger.
CREATE OR REPLACE TRIGGER signal_messages
AFTER
INSERT OR UPDATE OR DELETE
OF message_id ,message_source ,message_destination ,message
ON messages
FOR EACH ROW

BEGIN

  -- Check if no row preivously existed - an insert.
  IF :old.message_id IS NULL THEN
  
    -- Signal Event.
    DBMS_ALERT.SIGNAL('EVENT_MESSAGE_QUEUE',:new.message_source||':Insert');

    -- Insert alert message.
    INSERT INTO     messages_alerts VALUES   (:new.message_source||':Insert');

  -- Check if no row will exist after DML - a delete.
  ELSIF :new.message_id IS NULL THEN

    -- Signal Event.
    DBMS_ALERT.SIGNAL('EVENT_MESSAGE_QUEUE',:old.message_source||':Delete');

    -- Insert alert message.
    INSERT INTO     messages_alerts VALUES   (:old.message_source||':Delete');

  -- This handles update DMLs.
  ELSE

    -- Check if message source is updated.
    IF :new.message_source IS NULL THEN

      -- Signal Event.
      DBMS_ALERT.SIGNAL('EVENT_MESSAGE_QUEUE',:new.message_source||':Update#1');

      -- Insert alert message.
      INSERT INTO     messages_alerts VALUES   (:new.message_source||'Update#1');

    -- A column other than message source is updated.
    ELSE

      -- Signal Event.
      DBMS_ALERT.SIGNAL('EVENT_MESSAGE_QUEUE',:old.message_source||':Update#2');

      -- Insert alert message.
      INSERT INTO     messages_alerts VALUES   (:old.message_source||':Update#2');

    END IF;

  END IF;

END;
/
Registering or Removing interest in an alert

-- Register interest in an alert.
BEGIN
   -- Register interest in an alert.
   DBMS_ALERT.REGISTER('EVENT_MESSAGE_QUEUE');
END;
/

-- Remove your registered interest in a DBMS_ALERT.
BEGIN
   -- Remove/deregister interest from an alert.
   DBMS_ALERT.REMOVE('EVENT_MESSAGE_QUEUE');
END;
/

Waiting on an alert
-- Register interest in an alert.
DECLARE

  -- Define OUT mode variables required from WAITONE.
  message        VARCHAR2(30 CHAR);
  status         INTEGER;

BEGIN

  -- Register interest in an alert.
  DBMS_ALERT.WAITONE('EVENT_MESSAGE_QUEUE', message, status, 30);

  IF (STATUS <> 0) 
  THEN
    -- Print an error message.
    DBMS_OUTPUT.PUT_LINE('A timeout has happened.');
  ELSE
    -- Print title.
    DBMS_OUTPUT.PUT_LINE('Alert Messages Received');
    DBMS_OUTPUT.PUT_LINE('-----------------------');

    -- Print alert message received.
    DBMS_OUTPUT.PUT_LINE(message);
  END IF;
END;
/
Triggering an alert

Just use the standard insert, update or delete into a table statements to trigger an alert

Then just run the above waitone program to see the output.