AQ Propagation - error adding subscriber

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: 19 Sep 2005 08:05:48 -0700
Message-ID: <1127142348.600429.192050@g44g2000cwa.googlegroups.com>

Guys,

I am trying to get AQ propagation up and running on 9.2.0.6 (HP-UX), where I have rule based propagation based on a field of the user_data payload in the queue table. I have created a simple type and queue as follows:-

create or replace TYPE simple_q_type AS OBJECT (
destination varchar2(255),
request xmltype
);
/

begin
DBMS_AQADM.CREATE_QUEUE_TABLE(QUEUE_TABLE => 'simple_q_table'

,QUEUE_PAYLOAD_TYPE => 'simple_q_type'
,COMPATIBLE => '8.1'
,STORAGE_CLAUSE => 'TABLESPACE bal_small_ts_1'
,SORT_LIST => 'PRIORITY,ENQ_TIME'
,MULTIPLE_CONSUMERS => true
,MESSAGE_GROUPING => 0);

end;
/

begin
dbms_aqadm.create_queue(queue_name=> 'bal.simple_q',

                        queue_table=> 'bal.simple_q_table',
			queue_type=> DBMS_AQADM.NORMAL_QUEUE,
			max_retries=> '5',
			retry_delay=> '0',
			retention_time=> '0',
			comment=> '');

dbms_aqadm.start_queue('simple_q', TRUE, TRUE); end;
/

I then created two queues on another database and a database link for them. Now when I try and add a subscriber to my local queue based on the user_Data.destination field it fails:-

begin
DBMS_AQADM.ADD_SUBSCRIBER (

   queue_name     => 'bal.simple_q',
   subscriber     =>
sys.aq$_agent('Consumer1','bal.receiver_q1_at_proptest',null),
   rule           => 'simple_q_table.user_data.destination =

''queue_1''',

   transformation => NULL);
end;
/

ERROR at line 1:
ORA-25448: rule BAL.SIMPLE_Q$17 has errors ORA-00904: "SIMPLE_Q_TABLE"."USER_DATA"."DESTINATION": invalid identifier

ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4412
ORA-06512: at "SYS.DBMS_AQADM", line 301
ORA-06512: at line 2

The documentation states that anything referencing a type should be prefixed with tab.user_data.field - which is what I have done.

I tried to select from the table using the type field in the where clause:-

SQL> select *
  2 from simple_q_table
  3 where user_data.destination = 'something'; where user_data.destination = 'something'

      *
ERROR at line 3:
ORA-00904: "USER_DATA"."DESTINATION": invalid identifier

Prefixing it with the tablename does not work either:-

  1 select *
  2 from simple_q_table
  3* where simple_q_table.user_data.destination = 'something' SQL> /
where simple_q_table.user_data.destination = 'something'

      *
ERROR at line 3:
ORA-00904: "SIMPLE_Q_TABLE"."USER_DATA"."DESTINATION": invalid identifier

BUT, if I give the table an alias and use it:-

  1 select *
  2 from simple_q_table q
  3* where q.user_data.destination = 'something' SQL> / no rows selected

It works.

Aliasing the tablename is fine in a select statement, but it is not going to work out when adding a subscriber to the queue as you cannot do it there.

Does anyone know if this is a bug, or better know what I am doing wrong when adding a subscriber?

Thanks,

Stephen. Received on Mon Sep 19 2005 - 10:05:48 CDT