Re: Show "progress" of a process using pl/sql web toolkit?

One thing you may want to look into: DBMS_PIPE. This allows you to send messages from session to another. One of the big things that seperates from another Oracle notification technology, DBMS_ALERT, is that DBMS_PIPE signals get sent out immediately, whereas DBMS_ALERT signals only get sent when a transaction is complete.

If you are doing many fast transactions, DBMS_ALERT may be a good choice - you raise signals using DBMS_ALERT in your "working" transactions, and receive the signals in your listening transaction for display. Because DBMS_ALERT only sends once you have commits, the information being transmitted to the listener represents real work that has been completed. The other nice thing about DBMS_ALERT is that it can work in a non-polling manner - you make a call to DBMS_ALERT.WAIT and it will block until the next message has been sent by the "worker".  Of course, if your transactions are fast and frequent, you will probably end up polling, since every time you call WAIT a new message will be ready to consume. One caveat/feature: DBMS_ALERT does a form of event compression, in that if you send many signals out with the same identity, only the last one will reach the listener, if they weren't blocking on WAIT at the time the signals were sent. You can also run into blocking problems on the sender side if you have multiple open transactions all trying to send the same message . Make sure you test it in worst case to make sure it's doing what you want.

If you're performing a single long transaction with lots of PL/SQL work inside it, or you don't want to lose any status messages being sent, DBMS_PIPE may be the way to go. You can listen with another session, but get status updates on what's going on inside the other transaction.  Of course, if the transaction rolls back, you'll need some way of making sure the listener displays the appropriate information. Otherwise, it's a more "obvious" mechanism - you open a pipe between two processes (sessions) and just start blasting away. I'm not positive, but I believe you may also be able to listen on the pipes opened with DBMS_PIPE using processes other than Oracle sessions.

Hope that helps. Received on Tue May 31 2005 - 14:42:05 CDT