QUEUE_TO_QUEUE parameter not generated

Toad for Oracle
1

When I generate scripts for AQ queues that propagate to remote AQ queues, the Toad-generated PL/SQL block omits the QUEUE_TO_QUEUE parameter of the dbms_aqadm.add_subscriber procedure. Comparing AQ queues doesn't notice differences in these parameters either. Still this way in 16.0.90.1509. I've added it below for illustration.

DECLARE
  aSubscriber sys.aq$_agent;
BEGIN 
  aSubscriber := sys.aq$_agent('SUBR_WMXP021_AQDN_OUTBOUND',
                              '"WMXPROP"."WMXP021_AQDN_OUTBOUND"@WMXP021.WORLD',
                              0);
  dbms_aqadm.add_subscriber
     ( queue_name     => 'OMXOUTBOUND.WMXP021_AQDN_OUTBOUND'
      ,subscriber     => aSubscriber
      ,queue_to_queue => TRUE);
END;
/

Thanks!

Rick

2

Also, Oracle is fussy about the generated '"WMXPROP"."WMXP021_AQDN_OUTBOUND"@WMXP021.WORLD' value of the destination parameter of the call to DBMS_AQADM.SCHEDULE_PROPAGATION procedure too.

BEGIN 
  DBMS_AQADM.SCHEDULE_PROPAGATION (
     queue_name          => 'WMXP021_AQDN_OUTBOUND'
    ,destination         => '"WMXPROP"."WMXP021_AQDN_OUTBOUND"@WMXP021.WORLD'
    ,start_time          => null
    ,duration            => null
    ,next_time           => null
    ,latency             => 60);
END;
/

I get errors until I remove the owner, the quotes, and the .WORLD from the value.

3

Thanks. I'll just log this for now and will try to get to it soon.

The queue_to_queue bit seems straightforward. I'm guessing the destination that I'm showing has that exact format in the data dictionary. Seems risky to just randomly chop out .World and quotes. That one might require some research.

1 Like
4

Oracle versions 12.2 and 19c. Not a big deal, now that I've got it through my thick head that I have to remember to add it or things won't propagate.

5

Actually, the owner seems fine, but not quotes or the .WORLD. The AQ stuff is pretty ancient and probably doesn't use DBMS_ASSERT to validate object names. The parsing of the remote object is probably hard-coded in the DBMS_AQADM package. Still not a big deal to fix the generated code.