Intersession communication using PL/SQL packages

Intersession communication as implied by name refers to the communication between two user sessions. The sessions can belong to a single or they can be of two different users.

There are two ways you can achieve this inside an Oracle database.

• Using database structures
• Using PL/SQL packages

First method requires the use database structures for example tables, synonyms etc. This type of communication is suitable if you want your data to be shared between sessions. Of Course nobody would like to create database structures just for the sake of conveying a simple message. Secondly this type of communication is limited by transaction control which means that in order for another session to see your message you must commit your current transaction.

For message communication and similar requirements between sessions Oracle provides two built in PL/SQL packages namely DBMS_PIPE and DBMS_ALERT. The functionality of both is a bit from one another and each serves a different purpose.


DBMS_PIPE uses memory structures called pipes to send and receive messages from one user session to the other. The pipes are pretty much similar to UNIX pipes. The is no requirement of permanent or semi-permanent database structures and the communication takes place in memory. Also this type of communication is not limited by transaction control.

DBSM_ALERT works and implements the publish and subscribe paradigm. To get information about something important happening inside database an event is published and users can subscribe for that event. Once subscribed user will start getting notified about the event happenings. The memory structure used by this package is not formally referred to as pipe but works pretty much like public pipes.