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>