bz_plsql_notif.sql

来自「oracle9i+j2ee开发aq oracle9i+j2ee开发aq or」· SQL 代码 · 共 50 行

REM script name: bz_plsql_notif.sql
REM This script adds an email notification for subscriber URGENT 
REM
REM version: 9i

REM =======================================================
REM Add PL/SQL notification for subscriber 'URGENT' to BZCARDORDERS_Q
REM =======================================================

set serveroutput on;

create or replace procedure plsqlnotif (context raw, reginfo sys.aq$_reg_info,
	descr sys.aq$_descriptor, payload raw, payloadl number)
AS
	dequeue_options DBMS_AQ.dequeue_options_t;
	message_prop	DBMS_AQ.message_properties_t;
	message_hdl	RAW(16);
	message		BZ_ADM.bzcardorder_typ;
BEGIN
	-- get the consumer name and message id from the descriptor
	dequeue_options.msgid := descr.msg_id;
	dequeue_options.consumer_name := descr.consumer_name;
	-- dequeue the message
	DBMS_AQ.DEQUEUE(queue_name => descr.queue_name,
			dequeue_options => dequeue_options,
			message_properties => message_prop,
			payload => message,
			msgid => message_hdl);
	commit;
END;
/

show errors;
	

REM =======================================================
REM Register for email notifications
REM =======================================================

declare
  reginfolist	sys.aq$_reg_info_list;
begin
  reginfolist 	:= sys.aq$_reg_info_list(
			sys.aq$_reg_info('bz_adm.bzcardorders_q:URGENT',
			   DBMS_AQ.NAMESPACE_AQ,
			   'plsql://bz.plsqlnotif', null));
  dbms_aq.register(reginfolist, 1);
end;
/