Forum Stats

  • 3,781,971 Users
  • 2,254,578 Discussions
  • 7,879,885 Comments

Discussions

AQ$WF_BPEL_QTAB having 2 entries for MESG_STATE as Ready and processed?

3474321
3474321 Member Posts: 34
edited Feb 26, 2020 10:34AM in Business Process Management Suite

Hi All,

We have requirement to subscribe to the business event from SOA while doing so we observed that WF_BPEL_QTAB having 2 entries for each transaction as ready and processed in msg_state fields where as it is not changing from ready to processed and it is creating 2 entries as ready and processed.

pastedImage_0.png

But processed data contains all the fields like de-queue time and date where as ready state entry does not contain all the data.

Here my question is , Is this the behavior of WF_BPEL_QTAB table? or should it contain only one entry?

Regards,

HB

Tagged:

Answers

  • Martien van den Akker
    Martien van den Akker Member Posts: 2,776 Bronze Crown
    edited Sep 5, 2017 10:01AM

    Hi,

    You should investigate the columns to the right of the AQ$WF_BPEL_QTAB query. At the left you'll probably find a column consumer or subscriber (I'm not sure by heart, I haven't got an environment at hand). You could also check the table User_Queue_subscribers (https://docs.oracle.com/cd/B19306_01/server.102/b14257/aq_views.htm#CHDGEHHE ).

    Probably you'll find two rows for the WF_BPEL_Q.

    When enqueueing a message to the WF_BPEL_Q it is duplicated for each Subscriber. So two subscribers, will result in two rows in the queue-table for each message.

    In your case one consumer has dequeue-ed it's message, the other hasn't.

    You can remove the message to dequeue them one by one for the particular subscriber.

    Regards,
    Martien

  • 3474321
    3474321 Member Posts: 34
    edited Sep 6, 2017 2:03AM

    Hi Martiesn,

    How to remove the message to dequeue one by one for particular subscriber. Here it is an automated process and we are not touching the queues manually.

  • Martien van den Akker
    Martien van den Akker Member Posts: 2,776 Bronze Crown
    edited Sep 6, 2017 5:20AM

    Hi,

    To dequeue a message for a subscriber in pl/sql:

    /* Dequeue messages from msg_queue_multiple: */

    DECLARE

      dequeue_options DBMS_AQ.dequeue_options_t;

      message_properties DBMS_AQ.message_properties_t;

      message_handle RAW(16);

      message aq.message_typ;

      no_messages exception;

      pragma exception_init (no_messages, -25228);

    BEGIN

      dequeue_options.wait := DBMS_AQ.NO_WAIT;

      BEGIN

      /* Consumer BLUE will get MESSAGE 2: */

      dequeue_options.consumer_name := 'BLUE';

      dequeue_options.navigation := FIRST_MESSAGE;

      LOOP

      DBMS_AQ.DEQUEUE(queue_name => 'msg_queue_multiple',

      dequeue_options => dequeue_options,

      message_properties => message_properties,

      payload => message,

      msgid => message_handle);

      DBMS_OUTPUT.PUT_LINE ('Message: ' || message.subject ||

      ' ... ' || message.text );

      dequeue_options.navigation := NEXT_MESSAGE;

      END LOOP;

      EXCEPTION

      WHEN no_messages THEN

      DBMS_OUTPUT.PUT_LINE ('No more messages for BLUE');

      COMMIT;

    END;

    To do this in a loop:

    BEGIN

    /* Consumer RED will get MESSAGE 1 and MESSAGE 2: */

      dequeue_options.consumer_name := 'RED';

    dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE

      LOOP

      DBMS_AQ.DEQUEUE(queue_name => 'msg_queue_multiple',

      dequeue_options => dequeue_options,

      message_properties => message_properties,

      payload => message,

      msgid => message_handle);

      DBMS_OUTPUT.PUT_LINE ('Message: ' || message.subject ||

      ' ... ' || message.text );

      dequeue_options.navigation := NEXT_MESSAGE;

      END LOOP;

      EXCEPTION

      WHEN no_messages THEN

      DBMS_OUTPUT.PUT_LINE ('No more messages for RED');

      COMMIT;

    END;

    I got this from: https://docs.oracle.com/cd/B10500_01/appdev.920/a96587/apexampl.htm#33919 (although 9.2, it still applies to 12c).

    I'd put this in a package and make things a bit generic/parameterized. After dequeueing the messages from the (unused) subscriber, make sure that you remove it (https://docs.oracle.com/database/121/ADQUE/aq_admin.htm#ADQUE2721 ).

    Regards,
    Martien

This discussion has been closed.