bz_msg_clnup.sql

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

REM script name: bz_msg_clnup.sql
REM cleans up the messages in the queue table
REM
REM version: 9i

REM =======================================================
REM cleanup section
REM =======================================================

create or replace procedure clean_msgs (que varchar, consumer varchar) IS
	deqopt	dbms_aq.dequeue_options_t;
	mprop	dbms_aq.message_properties_t;
	msgid	RAW(16);
	more_msgs boolean := TRUE;
	payload bzcardorder_typ;
	no_messages exception;
	pragma exception_init (no_messages, -25228);
begin
	deqopt.consumer_name := consumer;
	deqopt.navigation := DBMS_AQ.FIRST_MESSAGE;
	deqopt.wait := DBMS_AQ.NO_WAIT;	
	deqopt.dequeue_mode := DBMS_AQ.REMOVE_NODATA;

   	WHILE (more_msgs) LOOP
	BEGIN
		dbms_aq.dequeue( queue_name => que,
		  dequeue_options => deqopt,
		  message_properties => mprop,
		  payload => payload,
		  msgid => msgid);
		commit;
		deqopt.navigation := DBMS_AQ.NEXT_MESSAGE;
		
	EXCEPTION
	  WHEN no_messages then
		more_msgs := FALSE;
	  END;

	END LOOP;

end;
/

execute clean_msgs ('bzcardorders_q', 'SHIPPING');
execute clean_msgs ('bzcardorders_q', 'BILLING');
execute clean_msgs ('bzcardorders_q', 'RUSH_ORDER');