bz_notif.lst

oracle9i+j2ee开发aq oracle9i+j2ee开发aq oracle9i+j2ee开发aq

SQL> set echo on
SQL> @bz_priv
SQL> grant execute on dbms_aqelm to bz;

Grant succeeded.

SQL> grant create procedure to bz;

Grant succeeded.

SQL> connect bz_adm/bz_adm
Connected.
SQL> @bz_add_subscriber
SQL> REM script name: bz_add_subscriber.sql
SQL> REM This script adds a subscriber to bzcardorders_q
SQL> REM
SQL> REM version: 9i
SQL> 
SQL> REM =======================================================
SQL> REM cleanup section
SQL> REM =======================================================
SQL> execute dbms_aqadm.remove_subscriber ( -
> 	    queue_name => 'BZCARDORDERS_Q', -
> 	    subscriber => sys.aq$_agent('URGENT',null,null)-
> );

PL/SQL procedure successfully completed.

SQL> 
SQL> REM =======================================================
SQL> REM Add subscriber 'URGENT' to BZCARDORDERS_Q
SQL> REM =======================================================
SQL> execute dbms_aqadm.add_subscriber ( -
> 	    queue_name => 'BZCARDORDERS_Q', -
> 	    subscriber => sys.aq$_agent('URGENT',null,null),-
> 	    rule       => 'tab.user_data.first_name = ''BRAJESH'''-
> );

PL/SQL procedure successfully completed.

SQL> 
SQL> connect bz/bz
Connected.
SQL> @bz_email_notif
SQL> REM script name: bz_email_notif.sql
SQL> REM This script adds an email notification for subscriber URGENT
SQL> REM
SQL> REM version: 9i
SQL> 
SQL> REM =======================================================
SQL> REM Add email notification for subscriber 'URGENT' to BZCARDORDERS_Q
SQL> REM =======================================================
SQL> 
SQL> 
SQL> REM =======================================================
SQL> REM Set the mailserver properties
SQL> REM =======================================================
SQL> call sys.dbms_aqelm.set_mailhost('gmsmtp01.oraclecorp.com');

Call completed.

SQL> call sys.dbms_aqelm.set_mailport(25);

Call completed.

SQL> call sys.dbms_aqelm.set_sendfrom('[e-mail]');

Call completed.

SQL> 
SQL> REM =======================================================
SQL> REM Register for email notifications
SQL> REM =======================================================
SQL> 
SQL> declare
  2    reginfolist   sys.aq$_reg_info_list;
  3  begin
  4    reginfolist   := sys.aq$_reg_info_list(
  5  			     sys.aq$_reg_info('bz_adm.bzcardorders_q:URGENT',
  6  				DBMS_AQ.NAMESPACE_AQ,
  7  				'mailto://[e-mail]',
  8  				     null));
  9    dbms_aq.register(reginfolist, 1);
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> @bz_plsql_notif
SQL> REM script name: bz_plsql_notif.sql
SQL> REM This script adds an email notification for subscriber URGENT
SQL> REM
SQL> REM version: 9i
SQL> 
SQL> REM =======================================================
SQL> REM Add PL/SQL notification for subscriber 'URGENT' to BZCARDORDERS_Q
SQL> REM =======================================================
SQL> 
SQL> set serveroutput on;
SQL> 
SQL> create or replace procedure plsqlnotif (context raw, reginfo sys.aq$_reg_info,
  2  	     descr sys.aq$_descriptor, payload raw, payloadl number)
  3  AS
  4  	     dequeue_options DBMS_AQ.dequeue_options_t;
  5  	     message_prop    DBMS_AQ.message_properties_t;
  6  	     message_hdl     RAW(16);
  7  	     message	     BZ_ADM.bzcardorder_typ;
  8  BEGIN
  9  	     -- get the consumer name and message id from the descriptor
 10  	     dequeue_options.msgid := descr.msg_id;
 11  	     dequeue_options.consumer_name := descr.consumer_name;
 12  	     -- dequeue the message
 13  	     DBMS_AQ.DEQUEUE(queue_name => descr.queue_name,
 14  			     dequeue_options => dequeue_options,
 15  			     message_properties => message_prop,
 16  			     payload => message,
 17  			     msgid => message_hdl);
 18  	     commit;
 19  	     dbms_output.put_line('Notification received; message processed');
 20  END;
 21  /

Procedure created.

SQL> 
SQL> show errors;
No errors.
SQL> 
SQL> 
SQL> REM =======================================================
SQL> REM Register for email notifications
SQL> REM =======================================================
SQL> 
SQL> declare
  2    reginfolist   sys.aq$_reg_info_list;
  3  begin
  4    reginfolist   := sys.aq$_reg_info_list(
  5  			     sys.aq$_reg_info('bz_adm.bzcardorders_q:URGENT',
  6  				DBMS_AQ.NAMESPACE_AQ,
  7  				'plsql://bz.plsqlnotif', null));
  8    dbms_aq.register(reginfolist, 1);
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>