1 Reply Latest reply on Mar 24, 2015 6:35 PM by stejac

    soa how to dequeue WF_BPEL_Q

    stejac

      ebs 12.2.4

      database 11.2.0.4

      subscribed to these event 'oracle.apps.fnd.user.insert', 'oracle.apps.global.user.change' and the table WF_BPEL_QTAB is feaded

      i want to read the queue, does anyone have doc or example on how to use DBMS_AQ.DEQUEUE ?

      thanks

        • 1. Re: soa how to dequeue WF_BPEL_Q
          stejac

          DBMS_AQ.DEQUEUE (OAQ42) - Oracle - Oracle - Toad World

          DECLARE

             dequeue_options     dbms_aq.dequeue_options_t;

             message_properties  dbms_aq.message_properties_t;

             payload             APPS_NE.WF_EVENT_T;

             msgid               RAW(16);

          BEGIN

             DBMS_OUTPUT.PUT_LINE (to_char(sysdate,'yyyymmdd hh24:mi:ss'));

             /* consumer_name

             select queue,consumer_name,msg_state,expiration_reason,count(*) from AQ$WF_BPEL_QTAB group by queue,consumer_name,msg_state,expiration_reason;

             */

             dequeue_options.consumer_name := 'WF_BPEL_Q';

             dequeue_options.wait := DBMS_AQ.NO_WAIT;

             dequeue_options.navigation := dbms_aq.FIRST_MESSAGE;

             dequeue_options.dequeue_mode := DBMS_AQ.LOCKED;

            

             DBMS_AQ.DEQUEUE('WF_BPEL_Q',dequeue_options,message_properties,payload,msgid);

           

             DBMS_OUTPUT.PUT_LINE ('Dequeued message id is ' || RAWTOHEX (msgid));

             DBMS_OUTPUT.PUT_LINE ('Dequeued title is ' || payload.event_name);

             DBMS_OUTPUT.PUT_LINE ('Dequeued text is ' || payload.event_key);

           

             DBMS_OUTPUT.PUT_LINE (to_char(sysdate,'yyyymmdd hh24:mi:ss'));

          END;

          1 person found this helpful