Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Help with Sharded Q's

user13117585Mar 31 2024 — edited Mar 31 2024

Good morning everyone,

I'm starting using oracle queues and I would like to see how it works. I'm trying to do the following.

Create a sharded queue of type JMS. Enqueue a message manually and make it automatically processed by two different consumers.

So, I could create the queue like this:

BEGIN
    dbms_aqadm.create_sharded_queue (queue_name  => 'Q1',
                                     queue_payload_type => DBMS_AQADM.JMS_TYPE, 
                                     multiple_consumers => TRUE);
END;
/
BEGIN
    DBMS_AQADM.START_QUEUE(queue_name => 'Q1');
END;
/

I created a small table and procedure for callbacks:

CREATE TABLE callbacks 
(
  ID NUMBER GENERATED ALWAYS AS IDENTITY, 
  d DATE DEFAULT SYSDATE, 
  msg VARCHAR2(100)
);
CREATE OR REPLACE PROCEDURE event_callback AS 
BEGIN
 insert into callbacks(msg) VALUES ('msg 1');
END;
/

When I try to register a new consumer, with this code:

begin
  dbms_aqadm.add_subscriber('Q1', SYS.AQ$_AGENT('subsriber_1', null, null));
  dbms_aq.register(sys.aq$_reg_info_list(sys.aq$_reg_info('Q1:subsriber_1', dbms_aq.namespace_aq, 'plsql://event_callback', hextoraw('FF'))), 1);
end;
/

All look ok…. UNtil you enqueue a message:


DECLARE
   msg SYS.AQ$_JMS_TEXT_MESSAGE;
   queue_options       DBMS_AQ.ENQUEUE_OPTIONS_T;
   message_properties  DBMS_AQ.MESSAGE_PROPERTIES_T;
   message_id RAW(30);

BEGIN
     msg := SYS.AQ$_JMS_TEXT_MESSAGE.CONSTRUCT();  
     msg.set_text('hello 123');
     DBMS_AQ.ENQUEUE(
       queue_name => 'Q1',
       enqueue_options => queue_options,
       message_properties => message_properties,
       payload => msg,
       msgid => message_id);
       commit;
END;

Event is never processed and call back procedure is not called :(

And when I try to dequeue manually, I have an error :


DECLARE
  dequeue_options     DBMS_AQ.dequeue_options_t;
  message_properties  DBMS_AQ.message_properties_t;
  message_handle      RAW(16);
  message             SYS.AQ$_JMS_TEXT_MESSAGE;
  msg_text varchar2(32767);
BEGIN
     DBMS_AQ.DEQUEUE(queue_name          => 'Q1',
                      dequeue_options    => dequeue_options,
                      message_properties => message_properties,
                      payload            => message,
                      msgid              => message_handle);
   
       message.get_text(msg_text);
     DBMS_OUTPUT.PUT_LINE ('Message: ' || msg_text );
  COMMIT;
END;

The error is like this:

ORA-24205: feature Next Message not supported for sharded queues
ORA-06512: at "SYS.DBMS_AQ", line 786

Could anyone help or advise on how to move forward?

Comments
Post Details
Added on Mar 31 2024
5 comments
119 views