Advanced Queueing

----------------------------------------------------------------------------------------------------------------------------------

AQ practice/lab test

***** Simple Queue for Single Consumer Only *****
create user testaq identified by testaq default tablespace users;
grant dba to testaq;
conn testaq/testaq
set serveroutput on
create type message_type as object (subject varchar2(30), text varchar2(80));
/
begin
  -- create simple queue
  dbms_aqadm.create_queue_table(
    queue_table=>'testaq.msg',
    sort_list=>'PRIORITY,ENQ_TIME',
    queue_payload_type=>'testaq.message_type');
  dbms_aqadm.create_queue(
    queue_name=>'msg_queue',
    queue_table=>'testaq.msg');
  dbms_aqadm.start_queue(
    queue_name=>'msg_queue');
end;
/
-- send a message to a simple queue (publish)
declare
  eo dbms_aq.enqueue_options_t;
  mp dbms_aq.message_properties_t;
  msgid raw(16);
  msg message_type;
begin
  eo.visibility := dbms_aq.IMMEDIATE;
  eo.relative_msgid := null;
  eo.sequence_deviation := null;
  msg := message_type('TEST2', 'HELLO 2');
  dbms_aq.enqueue('msg_queue',eo,mp,msg,msgid);
  dbms_output.put_line('msgid='||msgid);
end;
/
select * from user_queues where qid in (select qid from v$aq where ready != 0);
select user_data from msg /* This is equivalent to dbms_aq.dequeue(...BROWSE) */;
-- read a message from a simple queue by dequeing
declare
  dequeueopts DBMS_AQ.DEQUEUE_OPTIONS_T;
  msgprops DBMS_AQ.MESSAGE_PROPERTIES_T;
  msgid raw(16);
  msg message_type;
begin
  dequeueopts.wait := 1;
  dbms_aq.dequeue( 'msg_queue', dequeueopts, msgprops, msg, msgid );
  dbms_output.put_line('msgid='||msgid||', subj='||msg.subject||', text='||msg.text);
END;
/

----------------------------------------------------------------------------------------------------------------------------------

select * from dba_queues where qid in (select qid from v$aq where ready != 0);

This is equivalent to dbms_aq.dequeue(...BROWSE):

select user_data from <owner>.<queue_table from dba_queues>;
e.g.
select * from sys.sys$service_metrics_tab;