Advanced Queuing. Subscribing problem.

From: Alexander Zemerov <azemerov_at_inbox.ru>
Date: 1 Oct 2004 11:34:20 -0700
Message-ID: <73a47d16.0410011034.b0dc5bd@posting.google.com>

I have Oracle 8.1.7.4.0

My steps are
CREATE type message_type as object (subject VARCHAR2(30),text VARCHAR2(80));
/
-- create multiple user queue

EXECUTE dbms_aqadm.create_queue_table (	queue_table =>
'scott.msg_multiple', 	multiple_consumers => TRUE, 	queue_payload_type


=> 'scott.message_type');

EXECUTE dbms_aqadm.create_queue ( 	queue_name  =>
'msg_queue_multiple', 	queue_table => 'scott.msg_multiple');

EXECUTE dbms_aqadm.start_queue ( queue_name => 'msg_queue_multiple');

• create single user queue EXECUTE dbms_aqadm.create_queue_table ( queue_table => 'scott.my_msg', sort_list=> 'PRIORITY,ENQ_TIME', queue_payload_type
  => 'scott.message_type');
  EXECUTE dbms_aqadm.create_queue ( queue_name => 'my_queue', queue_table => 'scott.my_msg'); EXECUTE dbms_aqadm.start_queue (queue_name => 'my_queue');
• try to subscribe DECLARE subscriber sys.aq$_agent; BEGIN subscriber := sys.aq$_agent('Test1','scott.my_queue',null); DBMS_AQADM.ADD_SUBSCRIBER( queue_name => 'msg_queue_multiple', subscriber => subscriber, rule => 'priority < 1' ); END;

and as result I have the next error message - ORA-24057: cannot define subscriber with rule for queue MSG_QUEUE_MULTIPLE ORA-06512: at "SYS.DBMS_AQADM_SYS", line 3426 ORA-06512: at "SYS.DBMS_AQADM", line 295 ORA-06512: at line 5

Explanation for this error code is -
An ADD_SUBSCRIBER or ALTER_SUBSCRIBER procedure with a rule was issued on a queue for which rule based subscribers are not supported. Rule based subscribers currently are supported only for NORMAL (persistent) multi-consumer queues created using an Oracle release 8.1.0 or higher compatible queue table.

But in my case Oracle version is higher then 8.1.0 and my queue is normal and multi-consumer.
What is the problem? Anybody has an idea?

Alexander. Received on Fri Oct 01 2004 - 13:34:20 CDT