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, |
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 |
Creating pipes | -- Ceate a private pipe |
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 |
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 |
putting a wrapper around DBMS_PIPE | -- Create the messager wrapper program |
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. -- Remove your registered interest in a DBMS_ALERT. |
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. |