The two packages, DBMS_ALERT and DBMS_PIPE, are very powerful inter-process communication packages. Both allow for one session to talk to another session in the database. DBMS_ALERT is very much like a UNIX operating system 'signal', and DBMS_PIPE is very much like a UNIX 'named pipe'. Since a lot of confusion exists over which package to use and when, I've decided to handle them together.
The package DBMS_ALERT is designed to allow a session to signal the occurrence of some event in the database. Other sessions that are interested in this event would be notified of its occurrence. Alerts are designed to be transactional in nature, meaning that you might signal an alert in a trigger, or some stored procedure, but until your transaction actually commits, the alert will not be sent out to the waiting sessions. If you rollback, your alert is never sent. It is important to understand that the session wishing to be notified of the alert in the database must either occasionally 'poll' for the event (ask the database if it has been signaled), or block (wait) in the database, waiting for the event to occur.
The package DBMS_PIPE on the other hand, is a more generic inter-process communication package. It allows one or more sessions to 'read' on one end of a named pipe, and one or more sessions to 'write' messages onto this pipe. Only one of the 'read' sessions will ever get the message (and at least one session will), and it is not possible to direct a given message on a single named pipe to a specific session. It will be somewhat arbitrary as to which session will read a given message written to a pipe when there is more then one 'reader' available. Pipes are, by design, not transactional in nature - as soon as you send a message, the message will become available to other sessions. You do not need to commit, and committing or rolling back will not affect the outcome of sending the pipe.
The major difference between alerts and pipes is the transactional (or not) nature of the two. Alerts are useful when you desire to transmit a message to one or more sessions after it has been successfully committed to the database. Pipes are useful when you desire to transmit a message to a single session immediately. Examples of when you might use alerts are:
You have a GUI chart to display stock data on a screen. When the stock information is modified in the database, the application should be notified so that it knows to update the screen
You wish to put a notification dialogue up in an application when a new record is placed into a table so the end user can be notified of 'new work'
Examples of when you might choose to use a database pipe would be:
You have a process running on some other machine in the network that can perform an operation for you. You would like to send a message to this process to ask it to do something for you. In this way, a database pipe is much like a TCP/IP socket.
You would like to queue some data up in the SGA so that another process will ultimately come along, read out and process. In this fashion, you are using a database pipe like a non-persistent FIFO queue that can be read by many different sessions.
There are other examples of both, but these cover the main uses of alerts and pipes, and give a good characterization of when you might use one over the other. You use alerts when you want to notify a community of users of an event that has definitely taken place (after the commit). You use pipes when you want to immediately send a message to some other session out there (and typically wait for a reply).
Now that we understand the basic intention of alerts and pipes, we'll take a look at some of the implementation details of each.
DBMS_ALERT and DBMS_PIPE are both installed by default in the database. Unlike many of the supplied packages, EXECUTE on these packages is not granted to PUBLIC. In Oracle 8.0 and up, EXECUTE on these packages is granted to the EXECUTE_CATALOG_ROLE. In prior releases, these packages had no default grants whatsoever.
Since EXECUTE is granted to a role, and not to PUBLIC, you will find that you cannot create a stored procedure that is dependent on these packages, since roles are never enabled during the compilation of a procedure/package. You must have EXECUTE granted directly to your account.
The DBMS_ALERT package is very small, consisting of only seven entry points. I shall discuss the six of most interest here. The application that wishes to receive an alert will be primarily interested in:
REGISTER - To register interest in a named alert. You may call REGISTER many times in a session with different names, in order to be notified when any one of a number of events occurs.
REMOVE - To remove your interest in an event, in order to prevent the server from attempting to notify of you an event.
REMOVEALL - To remove your interest in all named alerts you registered for.
WAITANY - To wait for any of the named alerts, in which you have registered your interest, to be fired. This routine will tell you the name of the event that was fired, and provide access to the brief message that might accompany it. You may either wait for a specific duration of time, or not wait at all (to allow for an occasional 'poll' from the application to see if any event has taken place, but not block waiting for an event to occur).
WAITONE - To wait for a specific named alert to be fired. Like WAITANY, you may wait for a specified duration of time, or not wait at all.
And the application that wishes to signal, or fire an alert, is interested only in the routine:
SIGNAL - To signal an alert upon the commit of the current transaction. A rollback will 'unsignal'.
So, DBMS_ALERT is very easy to use. A client application interested in being notified of an event might contain code such as:
tkyte@TKYTE816> begin 2 dbms_alert.register( 'MyAlert' ); 3 end; 4 / PL/SQL procedure successfully completed. tkyte@TKYTE816> set serveroutput on tkyte@TKYTE816> declare 2 l_status number; 3 l_msg varchar2(1800); 4 begin 5 dbms_alert.waitone( name => 'MyAlert', 6 message => l_msg, 7 status => l_status, 8 timeout => dbms_alert.maxwait ); 9 10 if ( l_status = 0 ) 11 then 12 dbms_output.put_line( 'Msg from event is ' || l_msg ); 13 end if; 14 end; 15 /
They simply register their interest in the named alert, MyAlert, and then call DBMS_ALERT.WAITONE to wait for this alert to be fired. Notice that since DBMS_ALERT.MAXWAIT is used, a constant from the DBMS_ALERT package, this code will just 'sit there'. It is blocked in the database waiting for this event to occur. The interested client application might use a much smaller timeout period specified in seconds (perhaps 0, meaning no waiting should occur) so it could poll for an event. For example, an Oracle Forms application might have a timer that goes off every minute and calls DBMS_ALERT.WAITONE to see if some event has occurred. If so, the screen will be updated. A Java thread might become active every so often to check for an event, and update some shared data structure, and so on.
Now, in order to signal this alert, all we need to do is:
tkyte@TKYTE816> exec dbms_alert.signal( 'MyAlert', 'Hello World' ); PL/SQL procedure successfully completed. tkyte@TKYTE816> commit; Commit complete.
in another session. You should immediately see:
... 15 / Msg from event is Hello World PL/SQL procedure successfully completed.
in the session that was blocked waiting for the alert, so this session will no longer be blocked. This simple example shows the most commonly used format of DBMS_ALERT. Some sessions wait on a named alert, and another session signals it. Until the signaling session commits, the alert does not go through. You'll see this yourself easily using two SQL*PLUS sessions.
It gets more interesting with alerts when we ask ourselves:
What happens when many messages get 'signaled' at more or less the same time by different sessions?
What happens if I call signal repeatedly - how many alerts will be generated in the end?
What happens if more than one session signals an alert after I registered interest in it, but before I've called one of the wait routines? Same question, only what happens when more than one session signals an alert between my calls to wait?
The answers to these questions will point out some of the side effects of alerts; some of the things you need to be aware of when using them. I'll also suggest ways to avoid some of the issues these questions raise.
If we re-execute our small test from above, have the one session register its interest in MyAlert, wait on it, and then start up two additional sessions, we can easily see what happens when more than one session signals an alert simultaneously. In this test, both of the other two sessions will execute:
tkyte@TKYTE816> exec dbms_alert.signal( 'MyAlert', 'Hello World' ); and nothing else (no commit). What you will observe in this case is that the session, which issued the second signal, is blocked. This shows that if N sessions attempt to signal the same named event concurrently, N-1 of them will block on the DBMS_ALERT.SIGNAL call. Only one of the sessions will continue forward. Alerts are serial in nature, and care must be taken to avoid issues with this.
The database is designed to provide highly concurrent access to data. DBMS_ALERT is one of those tools that can definitely limit scalability in this area. If you place an INSERT trigger on a table and this trigger places a DBMS_ALERT.SIGNAL call when fired then, if the table is subject to frequent INSERT statements, you will serialize all INSERTs on that particular table whenever someone is registered for that alert. For this reason, you may want to consider limiting the number of overall sessions that might signal an alert. For example, if you have a live data feed coming into your database so that there is only one session inserting data into this table, DBMS_ALERT would be appropriate. On the other hand, if this is an audit trail table that everyone must INSERT into frequently, DBMS_ALERT would not be an appropriate technology.
One method to avoid this serialization by many sessions could be to use DBMS_JOB (detailed in its own section in this appendix). You might write a procedure in which the only thing you do is signal the alert and commit:
tkyte@TKYTE816> create table alert_messages 2 ( job_id int primary key, 3 alert_name varchar2(30), 4 message varchar2(2000) 5 ) 6 / Table created. tkyte@TKYTE816> create or replace procedure background_alert( p_job in int ) 2 as 3 l_rec alert_messages%rowtype; 4 begin 5 select * into l_rec from alert_messages where job_id = p_job; 6 7 dbms_alert.signal( l_rec.alert_name, l_rec.message ); 8 delete from alert_messages where job_id = p_job; 9 commit; 10 end; 11 / Procedure created.
Then, your database trigger would look like this:
tkyte@TKYTE816> create table t ( x int ); Table created. tkyte@TKYTE816> create or replace trigger t_trigger 2 after insert or update of x on t for each row 3 declare 4 l_job number; 5 begin 6 dbms_job.submit( l_job, 'background_alert(JOB);' ); 7 insert into alert_messages 8 ( job_id, alert_name, message ) 9 values 10 ( l_job, 'MyAlert', 'X in T has value ' || :new.x ); 11 end; 12 / Trigger created.
to have the alert signaled by a background process after you commit. In this fashion:
Alerts are still transactional
They will not serialize your foreground processes (interactive applications)
The drawback is that jobs are not necessarily run right away; it might be a little while before the alert gets out. In many cases, I have found this to be acceptable (it is important to notify the waiting process that something has occurred, but a short lag time is generally OK). Advanced queues (AQ) also supply a highly scalable method of signaling events in the database. They are more complex to use than DBMS_ALERT, but offer more flexibility in this area.
Now the question is, what if I signal the same named alert many times in my application, and then commit? How many alerts actually get signaled? Here, the answer is simple: one. DBMS_ALERT works very much like a UNIX signal would. The UNIX OS uses signals to notify processes of events that have occurred in the operating system. One such event for example is 'I/O is ready', meaning that one of the files (or sockets or such) you have open is ready for more I/O. You might use this signal when building a TCP/IP-based server for example. The OS will notify you when a socket you have opened,has data that is waiting to be read on it, rather than you going to each socket, and peeking into it to see if it has more data ready. If the OS determines five times that the socket has data to be read, and it did not get a chance to notify you yet, it will not tell you five times, it will only tell you once. You get the event, 'socket X is ready to be read'. You do not get all of the prior events about that socket. DBMS_ALERT works in the same exact fashion.
Returning to our simple example from above, we would run the snippet of code that registers its interest in an event, and calls the WAITONE routine to wait for this event. In another session, we will execute:
tkyte@TKYTE816> begin 2 for i in 1 .. 10 loop 3 dbms_alert.signal( 'MyAlert', 'Message ' || i ); 4 end loop; 5 end; 6 / PL/SQL procedure successfully completed. tkyte@TKYTE816> commit; Commit complete.
In the other window, we will see the feedback:
Msg from event is Message 10 PL/SQL procedure successfully completed.
Only the very last message we signaled will get out - the intervening messages will never be seen. You must be aware the DBMS_ALERT will, by design, drop messages by a session. It is not a method to deliver a sequence of messages, it is purely a signaling mechanism. It gives you the ability to tell a client application 'something has happened'. If you rely on each and every event you ever signal to be received by all sessions, you will be disappointed (and most likely have a bug in your code on your hands).
Again, DBMS_JOB can be used to some extent to resolve this issue if it is paramount for each event to be signaled. However, at this point, an alternate technology comes to mind. Advanced queues, (a topic outside the scope of this book), can be used to satisfy that requirement in a much better fashion.
This is the last question; what happens if more than one session signals an alert after I registered interest in it, but before I've called one of the wait routines? Same question, only what happens when more than one session signals an alert between my calls to wait? The answer is the same as when a single session makes many calls to DBMS_ALERT.SIGNAL. Only the last event is remembered, and signaled out. You can see this by placing a PAUSE in the simple SQL*PLUS script we have been using so that it reads like this:
begin dbms_alert.register( 'MyAlert' ); end; / pause
Now, in some other sessions, call the DBMS_ALERT.SIGNAL with unique messages (so you can distinguish them) and commit each message. For example, modify our simple loop from above as follows:
tkyte@TKYTE816> begin 2 for i in 1 .. 10 loop 3 dbms_alert.signal( 'MyAlert', 'Message ' || i ); 4 commit; 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed.
After you do that and return to this original session, simply hit the Enter key, and the block of code that calls WAITONE will execute. Since the alert we are waiting on has been signaled already, this block of code will return immediately, and will show us we received the last message that was signaled by this alert. All of the other intervening messages from the other sessions are lost, by design.
The DBMS_ALERT package is suitable for those cases where you wish to notify a large audience of interested clients about events in the database. These named events should be signaled by as few sessions as possible, due to inherent serialization issues with DBMS_ALERT. Since messages will by design be 'lost', DBMS_ALERT is suitable as an event notification process. You can use it to notify an interested client that data in a table T has changed for example, but to try and use it to notify these clients of the changes of individual rows in T would not work (due to the fact that only the 'last' message is saved). DBMS_ALERT is a very simple package to use and requires little to no set up.
DBMS_PIPE is a package supplied to allow two sessions to communicate with each other. It is an inter-process communication device. One session can write a 'message' on a pipe, and another session can 'read' this message. In UNIX, the same concept exists in the form of a named pipe in the operating system. With named pipes, we can allow one process to write data to another process.
The DBMS_PIPE package, unlike DBMS_ALERT, is a 'real time' package. As soon as you call the SEND_MESSAGE function, the message is sent. It does not wait for a COMMIT; it is not transactional. This makes DBMS_PIPE suitable for cases where DBMS_ALERT is not (and vice-versa). We can use DBMS_PIPE to allow two sessions to have a conversation (not something we can do with DBMS_ALERT). Session one could ask session two to perform some operation. Session two could do it, and return the results to session one. For example, assume session two is a C program that can read a thermometer attached to the serial port of the computer it is running on, and return the temperature to session one. Session one needs to record in a database table the current temperature. It can send a 'give me the temperature' message to session two, which would find out what this is, and write the answer back to session one. Session one and session two may or may not be on the same computer - all we know is that they are both connected to the database. I am using the database much like I might use a TCP/IP network to perform communication between two processes. In the case of DBMS_PIPE however, I do not need to know a hostname and a port number to connect to, like you would with TCP/IP - just the name of the database pipe upon which to write my request.
There are two types of pipes available in the database - public and private. A public pipe can either be created explicitly via a call to CREATE_PIPE, or you may just implicitly create one upon sending a message on it. The major difference between an explicit and implicit pipe is that the pipe created via the CREATE_PIPE call should be removed by your application when it is done using it, whereas the implicit pipe will age out of the SGA after it hasn't been accessed for a while. A public pipe is set up such that any session, which has access to the DBMS_PIPE package can read and write messages on the pipe. Therefore, public pipes are not suitable for sensitive or even just 'important' data. Since pipes are typically used to perform a conversation of sorts, and a public pipe allows anyone to read or write this conversation, a malicious user could either remove messages from your pipe, or add additional 'garbage' messages onto your pipe. Either action would have the effect of breaking the conversation or protocol between the sessions. For this reason, most applications will use a private pipe.
Private pipes may be read or written, only by sessions that operate under the effective user ID of the owner of the pipe and the special users SYS and INTERNAL. This means only definer rights (see the Chapter 23, Invoker and Definer Rights) stored procedures owned by the owner of the pipe or sessions logged in as the owner of the pipe, SYS, or INTERNAL can read or write on this pipe. This significantly enhances the reliability of pipes as no other session or piece of code can corrupt or intercept your protocol.
A pipe is an object that will live in the SGA of your Oracle instance. It is not a disk-based mechanism at all. Data in a pipe will not survive a shutdown and startup - any information in this pipe at shutdown will be flushed, and will not be in the pipe again upon startup.
The most common usage of pipes is to build your own customized services or servers. Prior to the introduction of external procedures in Oracle 8.0, this was the only way to implement a stored procedure in a language other than PL/SQL. You would create a 'pipe' server. In fact, ConText (the precursor to interMedia text) was implemented using database pipes in Oracle 7.3, onwards. Over time, some of its functionality was implemented via external procedures, but much of the indexing logic is still implemented via database pipes.
Due to the fact that any number of sessions can attempt to read off of a pipe, and any number may attempt to write on a given pipe, we must implement some logic to ensure that we can deliver messages to the correct session. If we are going to create our own customized service (for example the thermometer demonstration from earlier) and add it to the database, we must make sure that the answer for session A's question gets to session A, and not session B. In order to satisfy that very typical requirement, we generally write our requests in one message onto a pipe with a well-known name, and include in this message, a unique name of a pipe we expect to read our response on. We can show this in the following figure:
Step 1 - Session A will write it's request, 'What is the temperature? Answer on pipe A ' onto the well-known pipe named 'temperature pipe'. At the same time, other sessions may be doing the same thing. Each message will be queued into the pipe in a 'first in, first out' fashion.
Step 2 - The temperature server will read a single message out of the pipe, and query whatever service it is providing access to.
Step 3 - The temperature server will use the unique pipe name, which the session requesting the information wrote onto the pipe, to write a response (pipe A in this example). We use an implicit queue for this response (so the response pipe disappears right after we are done). If we planned on making many such calls, we would want to use an explicitly created pipe to keep it in the SGA during our session (but we would have to remember to clean it out upon logout!).
Step 4 - Session A reads the response back from the pipe it told the temperature server to write the answer on.
The same sequence of events would take place for Session B. The temperature server would read its request, query the temperature, look at the message to find the name of the pipe to answer on, and write the response back.
One of the interesting aspects of database pipes is that many sessions can read from the pipe. Any given message placed onto the pipe will be read by exactly one session, but many sessions can be reading at the same time. This allows us to 'scale' up the above picture. In the above, it is obvious we could have many sessions requesting data from the 'temperature server', and it would serially process them one after the other. There is nothing stopping us from starting more than one temperature server as thus:
We can now service two concurrent requests. If we started five of them, we could do five at a time. This is similar to connection pooling, or how the multi-threaded server in Oracle itself works. We have a pool of processes ready to do work, and the maximum amount of concurrent work we can do at any point in time is dictated by the number of processes we start up. This aspect of database pipes allows us to scale up this particular implementation easily.
Oracle8 release 8.0 introduced the ability to implement a stored procedure in C directly, and Oracle8i gave us the ability to implement the stored procedure in Java. Given this, is the need for DBMS_PIPE and 'pipe servers' gone? The short answer is no, absolutely not.
When we covered external routines, we described their architecture. C-based external routines for example, execute in an address space separate from the PL/SQL stored procedure. There exists a one-to-one mapping between the number of sessions concurrently using an external procedure, and the number of separate address spaces created. That is, if 50 sessions concurrently call the external routine, there will be 50 EXTPROC processes or threads at least. C-based external routines are architected in a manner similar to the dedicated server mode of Oracle. Just as Oracle will create a dedicated server for each concurrent session, it will also create an EXTPROC instance for every concurrent external routine call. Java external routines are executed in much the same fashion - one-to-one. For every session using a Java external routine, there will be a separate JVM instance running in the server with its own state and resources.
A pipe server on the other hand works like the MTS architecture does in Oracle. You create a pool of shared resources (start up N pipe servers), and they will service the requests. If more requests come in concurrently than can be handled, the requests will be queued. This is very analogous to the MTS mode of Oracle, whereby requests will be queued in the SGA, and dequeued by a shared server as soon as they completed processing the prior request they were working on. The temperature example we walked through earlier is a good example of this. The first diagram depicts a single pipe server running; one temperature at a time will be retrieved and returned to a client. The second diagram depicts two pipe servers running to service all of the requests. Never more than two concurrent requests will be processed. The thermometer will never have more than two clients hitting it.
The reason this is important is that it gives us a great capability to limit concurrent accesses to this shared resource. If we used external routines, and 50 sessions simultaneously requested the temperature, they may very well 'crash' the thermometer if it was not designed to scale up to so many requests. Replace the thermometer with many other shared resources, and you may find the same problem arises. It can handle a couple of concurrent requests, but if you tried to hit it with many simultaneous requests, either it would fail, or performance would suffer to the point of making it non-functional.
Another reason why a pipe server might make sense is in accessing some shared resource that takes a long time to 'connect to'. For example, I worked on a project a couple of years ago at a large university. They needed access to some mainframe transactions (then needed to call up to a mainframe to get some student information). The initial connection to the mainframe might take 30 to 60 seconds to complete but after that, it was very fast (as long as we didn't overload the mainframe with tons of concurrent requests). Using a pipe server, we were able to initiate the connection to the server once, when the pipe server started up. This single pipe server would run for days using that initial connection. Using an external routine we would have to initiate the connect once per database session. An implementation that used external routines would quite simply not work in this environment due to the high startup costs associated with the mainframe connection. The pipe server not only gave them the ability to limit the number of concurrent mainframe requests, but it also provided the ability to do the expensive mainframe connection once, and then reuse this connection many hundreds of thousands of times.
If you are familiar with the reasoning behind using connection pooling software in a 3-tier environment, you are already familiar with why you would want to use pipes in certain circumstances. They provide the ability to reuse the outcome of a long running operation (the connection to the database in the case of the connection pooling software) over an over, and they give you the ability to limit the amount of resources you consume concurrently (the size of your connection pool).
One last difference between a pipe server and external routines is where the pipe server can run. Suppose in the temperature server example, the database server was executing on Windows. The temperature probe is located on a UNIX machine. The only object libraries available to access it are on UNIX. Since a pipe server is just a client of the database like any other client, we can code it, compile it, and run it on UNIX. The pipe server need not be on the same machine, or even platform, as the database itself. An external routine on the other hand, must execute on the same machine with the database server itself - they cannot execute on remote machines. Therefore, a pipe server can be used in circumstances where an external routine cannot.
On the Apress web site (http://www.apress.com/), you'll find an example of a small pipe server. It answers the frequently asked question, 'How can I run a host command from PL/SQL?' With the addition of Java to the database and C external procedures, we could easily implement a host command function with either technology. However, what if I do not have access to a C compiler, or I don't have the Java component of the database available - what then? The example shows how we could very simply setup a small 'pipe server' that can do host commands using nothing more than SQL*PLUS and the csh scripting language. It is fairly simple, consisting of only a few lines of csh, and even fewer of PL/SQL. It shows much of the power of database pipes though, and should give you some ideas for other interesting implementations.
Database pipes are a powerful feature of the Oracle database that allow for any two sessions to have a 'conversation' with each other. Modeled after pipes in UNIX, they allow you to develop your own protocol for sending and receiving messages. The small example available on the Wrox web site demonstrates how easy it can be to create a 'pipe server', an external process that receives requests from database sessions and does something 'special' on their behalf. Database pipes are not transactional, differentiating them from database alerts, but it is this non-transactional feature that makes them so useful in many cases. Amongst many others, I have used database pipes to add features to the database such as:
Sending e-mail.
Printing files.
Integrating non-Oracle, non-SQL data sources.
Implementing the equivalent of DBMS_LOB.LOADFROMFILE for LONGs and LONG RAWs.
