Oracle PL/SQL Tutorial/System Packages/dbms aqadm

dbms_aqadm.create_queue_table

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  /
Type created.
SQL>
SQL> BEGIN
  2    dbms_aqadm.create_queue_table(
  3      queue_table => "test_q_table",
  4      queue_payload_type => "myTable",
  5      comment => "Test Queue Table") ;
  6    dbms_aqadm.create_queue(
  7      queue_name => "test_q",
  8      queue_table => "test_q_table",
  9      comment => "Test Queue") ;
 10
 11    dbms_aqadm.start_queue("test_q");
 12
 13  END ;
 14  /
PL/SQL procedure successfully completed.
SQL>

DBMS_AQADM.STOP_QUEUE

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>