DBMS_AQADM.STOP_QUEUE : dbms_aqadm « System Packages « Oracle PL/SQL Tutorial






SQL>
SQL>
SQL> create or replace type myTable as object (
  2      msg_identifier  VARCHAR2(25),
  3      msg_body        VARCHAR2(100),
  4      msg_date        DATE);
  5  /


SQL>
SQL> DECLARE
  2      v_msg       myTable;
  3      v_deqopts   DBMS_AQ.DEQUEUE_OPTIONS_T;
  4      v_msg_prop  DBMS_AQ.MESSAGE_PROPERTIES_T;
  5      v_msgid RAW(16);
  6      dq_timeout  EXCEPTION;
  7      PRAGMA EXCEPTION_INIT(dq_timeout, -25228) ;
  8
  9  BEGIN
 10      LOOP
 11         v_deqopts.wait := 1 ;
 12         DBMS_AQ.DEQUEUE(queue_name => 'test_q',
 13          dequeue_options => v_deqopts,
 14          message_properties => v_msg_prop,
 15          payload => v_msg,
 16          msgid => v_msgid);
 17         DBMS_OUTPUT.PUT_LINE(v_msg.msg_identifier ||' Data: '
 18          ||v_msg.msg_body||'  Date: '||v_msg.msg_date);
 19      END LOOP;
 20
 21      COMMIT ;
 22
 23  EXCEPTION
 24      WHEN dq_timeout THEN NULL ;
 25  END ;
 26  /
Message 1 Data: test message data 1  Date: 24-JUL-08
Message 2 Data: test message data 2  Date: 24-JUL-08
Message 3 Data: test message data 3  Date: 24-JUL-08
Message 4 Data: test message data 4  Date: 24-JUL-08
Message 5 Data: test message data 5  Date: 24-JUL-08
Message 6 Data: test message data 6  Date: 24-JUL-08
Message 7 Data: test message data 7  Date: 24-JUL-08
Message 8 Data: test message data 8  Date: 24-JUL-08
Message 9 Data: test message data 9  Date: 24-JUL-08
Message 10 Data: test message data 10  Date: 24-JUL-08

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> BEGIN
  2      DBMS_AQADM.STOP_QUEUE(queue_name => 'test_q');
  3      DBMS_AQADM.DROP_QUEUE(queue_name => 'test_q');
  4      DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'test_q_table');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop type myTable ;

Type dropped.

SQL>








31.4.dbms_aqadm
31.4.1.dbms_aqadm.create_queue_table
31.4.2.DBMS_AQADM.STOP_QUEUE