Automatic Notification From AQ

Notifications can be set from AQ to notify when a message arrives. Following channels are available for notification -

• Email notification
• Stored procedure invocation
• Http URL

Use procedure  DBMS_AQ.REGISTER to register for a message notification. More details can be found at this url -

http://docs.oracle.com/cd/B28359_01/server.111/b28420/aq_opers.htm#i1007235

For email notifications, hostname, port and 'from' email address required to be configured at database level. To set hostname, port etc refer below link -

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_aqelm.htm#CACFDHHH 

To find the existing settings run this block -

DECLARE
  host        varchar2(100);
  port        number;
  fromAddress varchar2(100);
BEGIN
  DBMS_AQELM.GET_MAILHOST(host);
  DBMS_AQELM.GET_MAILPORT(port);
  DBMS_AQELM.GET_SENDFROM(fromAddress);
 
  dbms_output.put_line('Mail Host: '||host);
  dbms_output.put_line('Mail Port: '||port);
  dbms_output.put_line('Mail From Address: '||fromAddress);
END;