[Chapter 3] 3.2 DBMS_ALERT: Broadcasting Alerts to Users

3.2 DBMS_ALERT: Broadcasting Alerts to Users

The DBMS_ALERT package provides a facility to broadcast notification of database events (alerts) to multiple users who have previously registered their interest in receiving those alerts. You will use the DBMS_ALERT package to implement applications that respond immediately to data modifications of interest to the application. In this way, you can avoid the need to do regular polling on the data to determine if changes have taken place. This is typically accomplished by having the application register to receive an alert on the specific data of interest, querying the data to establish a baseline, and then waiting for the alert to be signaled, which indicates the need to requery the data. Alerts can be automatically signaled using database triggers on the tables of interest, so that all modifications to the data will signal the alert, regardless of which application or user modified the data. Alerts are asynchronous and transaction-based, meaning that users can wait for and receive notification after the signaling event and that only committed transactions (usually involving data changes) will signal the alert.

Here are two good examples of applications that could be implemented using DBMS_ALERT:

Graphical displays of statistics that must be updated whenever the underlying data changes
An online auction where bidders want to be notified when they have been outbid on an item

3.2.1 Getting Started with DBMS_ALERT

The DBMS_ALERT package is created when the Oracle database is installed. The dbmsalrt.sql script (found in the built-in packages source code directory, as described in Chapter 1) contains the source code for this package's specification. This script is called by catproc.sql, which is normally run immediately after database creation. Under Oracle7, no privileges are automatically granted on DBMS_ALERT. Under Oracle8, the EXECUTE_CATALOG_ROLE role is granted EXECUTE privilege on DBMS_ALERT. Thus the DBMS_ALERT programs are not generally available to users. Access to DBMS_ALERT is obtained by granting EXECUTE privilege explicitly to users or roles that require use of the package.

Note also that a public synonym for DBMS_ALERT is not created automatically by dbmsalrt.sql, so references to the package's programs must be qualified by the owning schema (SYS), unless synonyms have been created. To create a public synonym for DBMS_ALERT, issue the following SQL command:

CREATE PUBLIC SYNONYM DBMS_ALERT FOR SYS.DBMS_ALERT;

3.2.1.1 DBMS_ALERT programs

Table 3.2 lists the programs included in the DBMS_ALERT package.

Table 3.2: DBMS_ALERT Programs
Name	Description	Use in SQL?
REGISTER	Registers interest in notification of an alert	No
REMOVE	Unregisters interest in notification of an alert	No
REMOVEALL	Unregisters interest in all alert notification	No
SET_DEFAULTS	Sets polling loop interval	No
SIGNAL	Signals the occurrence of an alert	No
WAITANY	Waits for any registered alerts to occur	No
WAITONE	Waits for a specific registered alert to occur	No

DBMS_ALERT does not declare any package exceptions of its own. Many of the individual programs raise Oracle exceptions under certain circumstances, as described in the following sections.

3.2.1.2 DBMS_ALERT nonprogram elements

The DBMS_ALERT package contains one nonprogram element, maxwait. It is defined as follows:

maxwait CONSTANT INTEGER := 86400000;

The maxwait constant is the maximum time to wait for an alert. It is used as the default value for the timeout parameter in the WAITONE and WAITANY procedures. The value of 86400000 seconds corresponds to 1000 days.

3.2.2 The DBMS_ALERT Interface

This section describes the programs available through the DBMS_ALERT package.

3.2.2.1 The DBMS_ALERT.REGISTER procedure

The REGISTER procedure registers interest in a specific alert by a database session. Once registered, the session will be notified of any occurrences of the alert. The header for this procedure is,

PROCEDURE DBMS_ALERT.REGISTER
   (name IN VARCHAR2);

where name is the name of the alert to register for notification.

3.2.2.1.1 Exceptions

The REGISTER procedure does not raise any package exceptions. It will raise an ORA-20000 exception for specific error conditions, with message text indicating the error as follows:

ORU-10021: Lock request error; status: n
ORU-10025: Lock request error; status: n

3.2.2.1.2 Restrictions

Note the following restrictions on calling REGISTER:

Alert names are limited to 30 bytes and are case-insensitive.
Alert names beginning with "ORA$" are reserved for use by Oracle Corporation.

3.2.2.1.3 Example

In this example, the session will be registered to be notified of the EMP_INSERT alert, which is raised whenever INSERTs are performed on the EMP table:

BEGIN
   DBMS_ALERT.REGISTER('EMP_INSERT');
END;

3.2.2.2 The DBMS_ALERT.REMOVE procedure

The REMOVE procedure unregisters a session's interest in receiving notification of a specific alert. It has the following header,

PROCEDURE DBMS_ALERT.REMOVE
   (name IN VARCHAR2);

where name is the name of the alert to unregister from notification.

The REMOVE procedure does not raise any package exceptions, nor does it assert a purity level with the RESTRICT_REFERENCES pragma.

3.2.2.2.1 Example

The following example unregisters for the EMP_INSERT alert:

BEGIN
   DBMS_ALERT.REMOVE('EMP_INSERT');
END;

3.2.2.3 The DBMS_ALERT.REMOVEALL procedure

The REMOVEALL procedure unregisters the session from receiving notification of any and all alerts that have been previously registered. Here's the header:

PROCEDURE DBMS_ALERT.REMOVEALL;

The REMOVEALL procedure does not raise package exceptions, nor does it assert a purity level with the RESTRICT_REFERENCES pragma.

3.2.2.3.1 Example

This example stops all alert notifications to the session:

SQL> execute DBMS_ALERT.REMOVALL;

3.2.2.4 The DBMS_ALERT.SET_DEFAULTS procedure

The SET_DEFAULTS procedure is used to set session configurable settings used by the DBMS_ALERT package. Currently, the polling loop interval sleep time is the only session setting that can be modified using this procedure. The header for this procedure is,

PROCEDURE DBMS_ALERT.SET_DEFAULTS
   (sensitivity IN NUMBER);

where sensitivity is the polling interval sleep time in seconds.

The SET_DEFAULTS procedure does not raise any package exceptions.

3.2.2.4.1 Example

The following example sets the polling interval to one second:

SQL>  execute DBMS_ALERT.SET_DEFAULTS(600);

Setting the polling interval is relevant primarily to users of DBMS_ALERT under Oracle Parallel Server (OPS), since under OPS a polling loop is required to check for alerts issued from another Oracle instance.

3.2.2.5 The DBMS_ALERT.SIGNAL procedure

The SIGNAL procedure posts notification of the occurrence of an alert, which is then propagated to all sessions registered for the alert. Alert notification happens only if and when the signaling transaction COMMITs. Here's the header:

PROCEDURE DBMS_ALERT.SIGNAL
   (name IN VARCHAR2
   ,message IN VARCHAR2);

Parameters are summarized in the following table.

Name	Description
name	Name of the alert to signal
message	Message to associate and pass on with the alert

When you are signaling alerts using SIGNAL, it is important to COMMIT (or ROLLBACK) the signaling transaction as soon as possible. Several problems can develop when signaling transactions are held open too long, including the following:

Other sessions signaling this alert will block and wait until the COMMIT.
Under the multithreaded server, a shared server will be bound to the session until the COMMIT.
The signaling session will receive an error if it waits on the alert prior to a COMMIT.

If the signaling transaction is rolled back, no sessions will be notified of the alert. Thus the alerts in DBMS_ALERT are strictly transaction-based.

Multiple sessions can signal the same alert. Note that this process is serialized using DBMS_LOCK (described in Chapter 4, User Lock and Transaction Management) and can add significant wait times unless transactions are closed quickly (as noted earlier).

3.2.2.5.1 Exceptions

This program does not raise any package exceptions. It will raise an ORA-20000 exception for specific error conditions, with message text indicating the error as follows:

ORU-10001: Lock request error, status: n
ORU-10016: Error: n sending on pipe `pipename'
ORU-10017: Error: n receiving on pipe `pipename'
ORU-10022: Lock request error, status: n

3.2.2.5.2 Restrictions

Note the following restrictions on calling SIGNAL:

Alert names are limited to 30 bytes and are case-insensitive.
Alert names beginning with "ORA$" are reserved for use by Oracle Corporation.
RESTRICT_REFERENCES cannot be called in SQL.

3.2.2.5.3 Example

This trigger will signal the EMP_INSERT alert when rows are inserted into the EMP table. The empid column is passed as the alert message for receivers of the alert to use:

CREATE OR REPLACE TRIGGER emp_ARI
AFTER INSERT ON emp
FOR EACH ROW
BEGIN
   /*
   || signal alert that emp has been inserted,
   || passing the empid as alert message 
   */
   DBMS_ALERT.SIGNAL('EMP_INSERT', :NEW.empid);
   END IF;

END emp_ARI;
/

3.2.2.6 The DBMS_ALERT.WAITANY procedure

The WAITANY procedure waits for notification of any alerts for which the session is registered. The procedure call will complete when the first alert is signaled or when the wait timeout is reached. Here's the header:

PROCEDURE DBMS_ALERT.WAITANY
   (name OUT VARCHAR2
   ,message OUT VARCHAR2
   ,status OUT INTEGER
   ,timeout IN NUMBER DEFAULT MAXWAIT);

Parameters are summarized in the following table.

Name	Description
name	Name of the alert that occurred
message	Message attached to alert when signaled
status	Status of WAITANY call: 0 means alert; 1 means timeout
timeout	Time in seconds to wait for alerts

When multiple alerts for which the session is registered have been signaled, the call to WAITANY will return the most recent alert that has occurred.

If a session waits on an alert that it has also signaled, a lock request exception will occur unless a COMMIT has taken place between the calls to the SIGNAL and WAITANY procedures.

The WAITANY call uses a polling loop to detect alerts. This avoids notification problems that could otherwise occur when signaled, but uncommitted alerts mask notification of subsequent committed alerts. The polling loop begins with a 1-second interval that increases exponentially to 30 seconds.

3.2.2.6.1 Exceptions

The program does not raise any package exceptions. The program will raise an ORA-20000 exception for specific error conditions, with message text indicating the error as follows:

ORU-10002: Lock request error, status: n
ORU-10015: Error: n waiting for pipe status
ORU-10020: Error: n waiting on lock request
ORU-10024: No alerts registered

3.2.2.6.2 Restrictions

Note the following restrictions on WAITANY:

The message parameter is limited to 1800 bytes in length.
WAITANY cannot be called in SQL.

3.2.2.6.3 Example

This example waits five minutes to receive the next alert for which the session is registered. If an alert is received, it is displayed. If the EMP_INSERT alert is received, the employee id should be the message, and the employee status is changed without displaying the alert.

DECLARE
   alert_msg     VARCHAR2(1800);
   alert_status  INTEGER;
   alert_name;
BEGIN
   DBMS_ALERT.WAITANY(alert_name, alert_msg, alert_status, 300);

   IF alert_status = 1
   THEN
      DBMS_OUTPUT.PUT_LINE('timed out');

   ELSIF alert_name = 'EMP_INSERT'
   THEN
      UPDATE emp SET status = 'REGISTERED'
      WHERE empid := alert_msg;

   ELSE
      DBMS_OUTPUT.PUT_LINE('received alert: '||alert_name);
   END IF;
END;
/

3.2.2.7 The DBMS_ALERT.WAITONE procedure

The WAITONE procedure waits to be notified of an occurrence of the specified alert. The procedure call will complete when the alert is signaled or when the wait timeout is reached. Here's the header:

PROCEDURE DBMS_ALERT.WAITONE
   (name IN VARCHAR2
   ,message OUT VARCHAR2
   ,status OUT INTEGER
   ,timeout IN NUMBER DEFAULT MAXWAIT);

Parameters are summarized in the following table.

Name	Description
name	Name of the alert to wait for
message	Message attached to alert when signaled
status	Status of WAITONE call: 0 means alert; 1 means timeout
timeout	Time in seconds to wait for alerts

Note the following special cases:

If the alert has been registered and signaled prior to the call to the WAITONE procedure, the call will return immediately with the most recent occurrence of the alert.
When multiple instances of the alert have been signaled, the call to WAITONE will return the most recent occurrence of the alert.
If a session waits for and has also signaled the alert, a lock request exception will occur unless a COMMIT has taken place between the calls to the SIGNAL and WAITONE procedures.

3.2.2.7.1 Exceptions

This program does not raise any package exceptions. The program will raise an ORA-20000 exception for specific error conditions, with message text indicating the error as follows:

ORU-10019: Error: n on lock request
ORU-10023: Lock request error; status: n
ORU-10037: Attempting to wait on uncommitted signal from same session

3.2.2.7.2 Restrictions

Note the following restrictions on WAITONE:

The message parameter is limited to 1800 bytes in length.
The WAITONE procedure cannot be called in SQL.

3.2.2.7.3 Example

This example waits specifically on the EMP_INSERT alert and updates the status when it is signaled:

DECLARE
   alert_msg     VARCHAR2(1800);
   alert_status  INTEGER;
BEGIN
   DBMS_ALERT.WAITONE('EMP_INSERT', alert_msg, alert_status, 300);

   IF alert_status = 1
   THEN
      DBMS_OUTPUT.PUT_LINE('timed out');

   ELSE
      UPDATE emp SET status = 'REGISTERED'
      WHERE empid := alert_msg;

      DBMS_OUTPUT.PUT_LINE('employee registered');
   END IF;
END;
/

3.2.3 DBMS_ALERT Examples

The DBMS_ALERT package is a good example of how you can build higher-level functionality out of lower-level built-ins. Both the DBMS_LOCK and DBMS_PIPE packages are used extensively in the implementation of DBMS_ALERT.

NOTE: If you have an old Version 7.1 installation of Oracle, you can check out dbmsalrt.sql to see exactly how this is done, since the code is not wrapped.

An important feature of the alerting mechanism in DBMS_ALERT is that it is transaction-based. This means that alerts will be sent to registered sessions only if and when the signaling session issues a COMMIT. If the signaler issues a ROLLBACK instead, the alerts will not be sent. Applications that are interested only in real changes to data in the database will benefit from using transaction-based alerts. Applications that need to signal other sessions regardless of transaction boundaries or data modifications (like debuggers or auditing monitors) will probably need to use DBMS_PIPE instead of DBMS_ALERT.

What kind of application might actually need to be alerted to changes in data? The classic example given in the Oracle documentation is a continuous graphical display of data extracted from some table. Pulling data from the table at set intervals using a polling mechanism can be very inefficient. For one thing, the data may not have changed since the last pull, so a refresh is not really necessary. Also, if the application is separated from the database by a network (as it most likely would be), then the overhead of redundant data extraction is multiplied. In this example, the application could use DBMS_ALERT to suspend itself and wait for a signal to awaken and pull new data for the display. The signal will be received only when data in the table has actually been modified (i.e., a new pull is truly necessary).

3.2.3.1 The online auction

Well, I wanted to do something new and original. I spent some time thinking about other examples for using DBMS_ALERT. Finally, I realized that I had participated in a perfect application for this technology many times already: an online auction. During an auction (especially a virtual one over a computer network), it is important to know when an item you have a bidding interest in has been bid upon. In a traditional auction, this happens because items are auctioned off serially, so bids can only be placed on the current item. In an online auction, the participants are not in a room together, and the auction itself typically takes longer than a traditional auction. Also, it is desirable to auction multiple items simultaneously, taking advantage of the virtual nature of the auction. An auction application that notifies participants of bidding activity relevant (to them) would relieve them of having to constantly monitor their screens to stay abreast of the auction. Bidding could take place simultaneously on multiple items since users interested in those items would automatically be notified of new bids.

3.2.3.2 The auction schema

The online auction was perfect for DBMS_ALERT, so I set about to prove the concept. First, I needed a basic schema. Professional data modelers may wince, but I came up with the following:

Object	Type	Description
AUCTION_ITEMS	TABLE	Items up for auction
BIDS	TABLE	Bids on auction items
HIGH_BIDS	VIEW	High bids by item

These objects are created by the auction.ddl script, reproduced as follows:

/* Filename on companion disk:  auction.ddl */*
rem *********************************************************
rem  AUCTION.DDL 
rem
rem  Creates objects used in the "online auction" example
rem  for the DBMS_ALERT package.  
rem
rem  Auction_items -- table of items being auctioned
rem  Bids -- table of bids placed on items
rem  High_bids -- view showing the current high bids on
rem               items and who placed them
rem
rem  Author:  John Beresniewicz, Savant Corp
rem
rem
rem  12/07/97: created
rem *********************************************************
DROP VIEW high_bids;
DROP TABLE bids;
DROP TABLE auction_items;

CREATE TABLE auction_items 
   (id            VARCHAR2(20)  NOT NULL PRIMARY KEY
   ,description   VARCHAR2(200) NOT NULL
   ,min_bid       NUMBER        NOT NULL
   ,curr_bid      NUMBER
   ,status        VARCHAR2(10) 
                  CONSTRAINT   valid_status
                  CHECK (status IN ('OPEN','CLOSED') )
   );

CREATE TABLE bids
   (bidder        VARCHAR2(30)
   ,item_id       VARCHAR2(20)  
                  REFERENCES auction_items(id) 
                  ON DELETE CASCADE
   ,bid           NUMBER         NOT NULL
   );

CREATE OR REPLACE VIEW high_bids
   (item_id
   ,item_desc
   ,bidder
   ,high_bid)
AS
SELECT
        BID.item_id
       ,AI.description
       ,BID.bidder
       ,BID.bid
  FROM
        bids           BID
       ,auction_items  AI
 WHERE
        BID.item_id    = AI.id
   AND  BID.bid        = (SELECT  MAX(bid) 
                            FROM  bids    B2
                           WHERE  BID.item_id = B2.item_id)
/

The AUCTION_ITEMS table contains an identifier and a description of each auction item. There are also columns for the minimum bid, status, and current high bid. This latter is really redundant with information derived in the HIGH_BIDS view, but this denormalization makes for a more interesting example.

The BIDS table holds the bidding activity. Each bid is a bid on an auction_item by a user for a specified amount. Originally, I had a BIDDERS table to track the auction participants, and this would likely be necessary for a real-world application. However, to simplify the example I decided to use the Oracle session username to identify bidders. Thus, there is an assumption that the online auction users will all be connected using unique usernames. The BIDS table also has a complex integrity constraint, which states that all bids must exceed the previous high bid for the same item (this is, after all, how an auction works). An additional constraint is that no bids may be updated or deleted from the table. These constraints are enforced by database triggers discussed later.

The HIGH_BIDS view selects the highest bid for each item along with the item's description and the bidder who made the bid. The auction application's GUI component can make use of this view to display current bidding levels for all items.

3.2.3.3 Auction system requirements

Some basic requirements of the online auction application are as follows:

Enforce the complex integrity constraint on the BIDS table.
Enforce the no-update, no-delete rule on the BIDS table.
Update the CURR_BID column of AUCTION_ITEMS for new bids.
Inform bidders when they have been outbid on an item.
Inform bidders when an item is closed from further bidding.

There is certainly more than one way to satisfy these requirements, especially the data integrity constraints on the two tables. I decided to implement a combination of database triggers and a package called auction. The database triggers enforce some data integrity constraints and signal changes to interested bidders using DBMS_ALERT.SIGNAL. A procedure called place_bid is responsible for placing bids on items, making sure that the complex integrity constraint is satisfied, and that the bidder is registered to receive notice of any bidding or status changes on the item. Another packaged procedure, called watch_bidding, demonstrates how an application might use DBMS_ALERT.WAITANY to be alerted for any bidding activity of interest to the user.

One immediate issue to address is what the alert names should be. The auction_items.id column seems a natural option since all alerts will concern a specific item.

3.2.3.4 Integrity constraint triggers

Here are the triggers for the auction_items and bids tables:

/* Filename on companion disk:  auction2.sql */*
CREATE OR REPLACE TRIGGER auction_items_ARU
AFTER UPDATE ON auction_items
FOR EACH ROW
BEGIN
   /* 
   || trigger enforces no update of item_id and also
   || signals an alert when status changes
   */
   IF UPDATING ('ITEM_ID')
   THEN 
      RAISE_APPLICATION_ERROR(-20000, 'Cannot update item id');
   
   ELSIF UPDATING ('STATUS') AND (:NEW.status != :OLD.status)
   THEN
      /* send new status on as the alert message */
      DBMS_ALERT.SIGNAL(:NEW.id, :NEW.status);
   END IF;
END auction_items_ARU;
/

CREATE OR REPLACE TRIGGER bids_ARIUD
AFTER INSERT OR UPDATE OR DELETE ON bids
FOR EACH ROW
BEGIN
   /* 
   || enforce all bids are final rule
   */
   IF UPDATING OR DELETING
   THEN
      RAISE_APPLICATION_ERROR
         (-20001, 'Cannot update or delete, all bids final!');
   ELSE
   /*
   || signal alert on item, send bidder name as message
   */
      DBMS_ALERT.SIGNAL(:NEW.item_id, :NEW.bidder);
   END IF;

END bids_ARIUD;
/

The triggers enforce the basic integrity rules that auction_items.id is a non-updatable column and that rows in the BIDS table cannot be updated or deleted. More importantly, they signal database alerts to registered sessions that auction data has changed using DBMS_ALERT.SIGNAL. The trigger on auction items signals status changes for items. Note the additional check requiring that :NEW.status be different from :OLD.status in order for the alert to be signaled. Also note that the item id is used as the alert name and that the new item status is passed as the alert's message. The trigger on BIDS signals the alert named by the item id and passes the bidder's name as the message. The use of the message parameter with the alerts allows the alert receiver to implement a context-sensitive response to the alert.

By the way, my naming convention for triggers has the table name suffixed by a string like [ A|B ][ R|S ][ I|U|D ] where:

A or B indicates an AFTER or BEFORE trigger
R or S indicates ROW or STATEMENT level trigger
I and/or U and/or D indicates an INSERT or UPDATE or DELETE

3.2.3.5 The auction package

The rest of the online auction requirements are implemented in the auction package. Here is the package specification:

/* Filename on companion disk:  auction1.sql */*
CREATE OR REPLACE PACKAGE auction
   /*
   || Implements a simple interactive bidding system
   || using DBMS_ALERT to keep bidders informed 
   || of activity in items they are interested in.
   || 
   || The item_id is used as the ALERT name for the 
   || item.
   ||
   || Author:  John Beresniewicz, Savant Corp
   ||
   || 12/07/97: created
   ||
   || Compilation Requirements: 
   ||
   || EXECUTE on DBMS_ALERT
   || SELECT, UPDATE on AUCTION_ITEMS
   || INSERT on BIDS
   || SELECT on HIGH_BIDS
   ||
   || Execution Requirements:
   ||
   */
AS
   /*
   || exceptions raised and handled in PLACE_BID
   || procedure
   */
   invalid_item   EXCEPTION;
   bid_too_low    EXCEPTION;
   item_is_closed EXCEPTION;

   /*
   || place a bid on an item, the bid must exceed any 
   || other bids on the item (and the minimum bid)
   ||
   || bidding on an item registers interest in the 
   || item using DBMS_ALERT.REGISTER
   ||
   || only this procedure should be used to add rows
   || to the bids table, since it also updates 
   || auction_items.curr_bid column
   */
   PROCEDURE place_bid
      (item_id_IN IN VARCHAR2
      ,bid_IN IN NUMBER);

   /* 
   || close bidding on an item
   */
   PROCEDURE close_item(item_id_IN IN VARCHAR2);

   /*
   || watch for any alerts on items bid by the user 
   || indicating other users have raised the bid
   */
   PROCEDURE watch_bidding(timeout_secs_IN IN NUMBER:=300);

END auction;
/

3.2.3.5.1 Place_bid procedure

The place_bid procedure is intended to be used by the GUI application to place all bids in the auction. No INSERTS or UPDATES to the BIDS table should be allowed except through this procedure, as it maintains the complex integrity constraint on the table, updates the curr_bid column of AUCTION_ITEMS, and registers the session for receiving alerts on the item. The body of place_bid looks like this:

/* Filename on companion disk: auction1.sql */*
PROCEDURE place_bid
	(item_id_IN IN VARCHAR2
	,bid_IN IN NUMBER)
IS
	temp_curr_bid auction_items.curr_bid%TYPE;
	temp_statusauction_items.status%TYPE;

	CURSOR auction_item_cur
	IS
	SELECT NVL(curr_bid,min_bid), status
		FROM auction_items
		WHERE id = item_id_IN
		FOR UPDATE OF curr_bid;

BEGIN
	/* 
	|| lock row in auction_items 
	*/
	OPEN auction_item_cur;
	FETCH auction_item_cur INTO temp_curr_bid, temp_status;

	/*
	|| do some validity checks
	*/
	IF auction_item_cur%NOTFOUND
	THEN
		RAISE invalid_item;
	
	ELSIF temp_status = 'CLOSED'
	THEN
		RAISE item_is_closed;

	ELSIF bid_IN <= temp_curr_bid
	THEN
		RAISE bid_too_low;

	ELSE
		/* 
		|| insert to bids AND update auction_items, 
		|| bidders identified by session username
		*/
		INSERT INTO bids (bidder, item_id, bid)
		VALUES (USER, item_id_IN, bid_IN);

		UPDATE auction_items 
		SET curr_bid = bid_IN
		WHERE CURRENT OF auction_item_cur;

		/* 
		|| commit is important because it will send
		|| the alert notifications out on the item
		*/
		COMMIT;

		/*
		|| register for alerts on item since bidding,
		|| register after commit to avoid ORU-10002
		*/
		DBMS_ALERT.REGISTER(item_id_IN);

	END IF;

	CLOSE auction_item_cur;

EXCEPTION
	WHEN invalid_item
		THEN
			ROLLBACK WORK;
			RAISE_APPLICATION_ERROR
				(-20002,'PLACE_BID ERR: invalid item');
	WHEN bid_too_low
		THEN
			ROLLBACK WORK;
			RAISE_APPLICATION_ERROR
				(-20003,'PLACE_BID ERR: bid too low');
	WHEN item_is_closed
		THEN
			ROLLBACK WORK;
			RAISE_APPLICATION_ERROR
				(-20004,'PLACE_BID ERR: item is closed');
	WHEN OTHERS
		THEN
			ROLLBACK WORK;
			RAISE;
END place_bid;

There are a few things to notice about place_bid. First, the row in AUCTION_ITEMS is locked FOR UPDATE to begin the transaction. I chose not to use NOWAIT in the cursor, because the transaction is small and should be quite fast, minimizing contention problems. The COMMIT immediately follows the INSERT and UPDATE and precedes the call to DBMS_ALERT.REGISTER. Originally I had it the other way around, but kept getting ORU-10002 errors when calling DBMS_ALERT.WAITANY immediately after place_bid. What was happening was that the call to DBMS_ALERT.REGISTER was holding a user lock that the insert trigger to BIDS was also trying to get. By doing the COMMIT first, the trigger is able to acquire and release the lock, which can then be acquired by DBMS_ALERT.REGISTER.

NOTE: To avoid the locking problems mentioned, be careful to code applications in such a way that a COMMIT will occur between calls to SIGNAL and REGISTER.

3.2.3.5.2 Exception handling

Exception handling in place_bid is inelegant but useful. The package defines named exceptions that place_bid detects, raises, and then handles using RAISE_APPLICATION_ERROR. In practice, it may be better to pass these out from the procedure to the calling application and let it handle them. Since I was prototyping in SQL*Plus and wanted to see the exception and an error message immediately, I used RAISE_APPLICATION_ERROR. When using DBMS_ALERT, note also that it is very important to terminate transactions to avoid the locking problems mentioned earlier, so the EXCEPTION section makes sure to include ROLLBACK WORK statements.

3.2.3.5.3 The watch_bidding procedure

With the triggers and the place_bid procedure in place, the online auction system is basically ready to go. Since a real application would involve a GUI, but I was prototyping in SQL*Plus, I needed a way to simulate what the GUI should do to receive DBMS_ALERT signals and inform the user of auction activity. This is basically what the watch_bidding procedure does. It could be modified and called directly from the GUI or its logic could be adapted and embedded into the GUI. The watch_bidding procedure uses DBMS_OUTPUT to display bidding alerts received. It also demonstrates the use of the alert message to implement a context-sensitive response to alerts.

Here is the source code for watch_bidding:

/* Filename on companion disk:auction1.sql */*
PROCEDURE watch_bidding(timeout_secs_IN IN NUMBER:=300)
IS
	temp_nameVARCHAR2(30);
	temp_message VARCHAR2(1800);
	temp_statusINTEGER;
BEGIN
	/* 
	|| enter a loop which will be exited explicitly 
	|| when a new bid from another user received or 
	|| DBMS_ALERT.WAITANY call times out
	*/
	LOOP
		/*
		|| wait for up to timeout_secs_IN for any alert
		*/
		DBMS_ALERT.WAITANY
			(temp_name, temp_message, temp_status, timeout_secs_IN);

		IF temp_status = 1
		THEN
			/* 
			|| timed out, return control to application 
			|| so it can do something here if necessary
			*/
			EXIT;

		ELSIF temp_message = 'CLOSED'
		THEN
			/* 
			|| unregister closed item, re-enter loop
			*/
			DBMS_ALERT.REMOVE(temp_name);
			DBMS_OUTPUT.PUT_LINE('Item '||temp_name||
						' has been closed.');

		ELSIF temp_message = USER OR temp_message = 'OPEN'
		THEN
			/*
			|| bid was posted by this user (no need to alert)
			|| re-enter loop and wait for another
			*/
			NULL; 

		ELSE
			/* 
			|| someone raised the bid on an item this user is bidding 
			|| on, application should refresh user's display with a
			|| query on the high_bids view and/or alert visually 
			|| (we will just display a message)
			||
			|| exit loop and return control to user so they can bid
			*/
			DBMS_OUTPUT.PUT_LINE
				('Item '||temp_name||' has new bid: '||
				TO_CHAR(curr_bid(temp_name),'$999,999.00')||
				' placed by: '||temp_message);
			EXIT;
		END IF;
	END LOOP;

END watch_bidding;

The watch_bidding procedure uses DBMS_ALERT.WAITANY to wait for any alerts for which the session has registered. In the auction system, registering for alerts is done when a bid is placed using place_bid. When an alert is received, the name of the alert is the auction item that has been updated. The alert message is used to respond intelligently to the alert as follows:

If the alert signals that bidding is closed on the item, the procedure unregisters the alert using DBMS_ALERT.REMOVE and waits for another alert.
If the alert was raised by the current user placing a bid or indicates that bidding has been opened on the item, the procedure waits for another alert.
If the DBMS_ALERT.WAITANY call times out, control is passed back to the caller.
If the alert is raised by another user, a message is displayed and control is passed back to the caller (so the user can make a new bid).

3.2.3.6 Testing the system

So the question is, does it work? I inserted some rows into the AUCTION_ITEMS table as follows:

/* Filename on companion disk:  auction3.sql */*
INSERT INTO auction_items
VALUES ('GB123','Antique gold bracelet',350.00,NULL,'OPEN');

INSERT INTO auction_items
VALUES ('PS447','Paul Stankard paperweight',550.00,NULL,'OPEN');

INSERT INTO auction_items
VALUES ('SC993','Schimmel print',750.00,NULL,'OPEN');

COMMIT;

I granted EXECUTE privilege on the AUCTION package to two users, USER01 and USER02, and connected two SQL*Plus sessions, one for each user. Then I initiated a bidding war using the following PL/SQL block:

/* Filename on companion disk:  auction4.sql */*
set serveroutput on size 100000
set verify off
BEGIN
   opbip.auction.place_bid('GB123',&bid);
   opbip.auction.watch_bidding(300);
END;
/

On each execution of the previous block in each session, I raised the bid. Here are the results from the USER01 session:

SQL> @auction4
Enter value for bid: 1000
Item GB123 has new bid:    $1,100.00 placed by: USER02

PL/SQL procedure successfully completed.

SQL> /
Enter value for bid: 1200
Item GB123 has new bid:    $1,300.00 placed by: USER02

PL/SQL procedure successfully completed.

USER01 opened the bidding on GB123 with a $1000 bid, which was quickly upped by USER02 to $1100, so USER01 came back with $1200 only to be topped finally by the winning $1300 bid by USER02. USER02's log of events confirms this exciting back and forth bidding war:

SQL> @auction4
Enter value for bid: 1100
Item PS447 has been closed.
Item GB123 has new bid:    $1,200.00 placed by: USER01

PL/SQL procedure successfully completed.

SQL> /
Enter value for bid: 1300

PL/SQL procedure successfully completed.

Note that each user is informed only of bids placed by the other user, and not of their own bids. Note also that USER02 was alerted to the fact that item PS447 (the subject of the previous bidding by USER02) had been closed.

So the auction system really does work, and quite well, as a matter of fact!


3.1 DBMS_PIPE: Communicating Between Sessions		4. User Lock and Transaction Management