Skip to Main Content
  • Questions
  • Dequeue is not working with REGISTER procedure

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Swathi.

Asked: July 15, 2020 - 9:19 pm UTC

Last updated: July 16, 2020 - 3:43 pm UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Creating queue table

WHENEVER SQLERROR CONTINUE

PROMPT -=-=-=- DROPPING WQ_WF_SOBJECTLIST_PPWK-=-=-=-;

EXEC SYS.DBMS_AQADM.STOP_QUEUE(queue_name => 'WINFORCE.WQ_WF_SOBJECTLIST_PPWK');
EXEC SYS.DBMS_AQADM.DROP_QUEUE(queue_name =>'WINFORCE.WQ_WF_SOBJECTLIST_PPWK');
EXEC SYS.DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'WINFORCE.WQT_WF_SOBJECTLIST_PPWK');

WHENEVER SQLERROR EXIT
PROMPT -=-=-=- CREATE_QUEUE WQ_WF_SOBJECTLIST_PPWK-=-=-=-;

DECLARE
   SUBSCRIBER   SYS.AQ$_AGENT;
BEGIN
   SYS.DBMS_AQADM.CREATE_QUEUE_TABLE (
      QUEUE_TABLE           => 'WINFORCE.WQT_WF_SOBJECTLIST_PPWK'
     ,QUEUE_PAYLOAD_TYPE    => 'WINFORCE.WY_WF_NOTIFICATION_TYPE'
     ,STORAGE_CLAUSE        => 'PCTFREE 88, INITRANS 125'
     ,MULTIPLE_CONSUMERS    => TRUE);
   SYS.DBMS_AQADM.CREATE_QUEUE (
      QUEUE_NAME            => 'WINFORCE.WQ_WF_SOBJECTLIST_PPWK'
     ,QUEUE_TABLE           => 'WINFORCE.WQT_WF_SOBJECTLIST_PPWK'
     ,QUEUE_TYPE            => SYS.DBMS_AQADM.NORMAL_QUEUE
     ,MAX_RETRIES           => 2
     ,RETRY_DELAY           => 30
     ,RETENTION_TIME        => 0 --we have a retry mechanism.So, we dont need to save the events anymore.
                                );

   SYS.DBMS_AQADM.START_QUEUE (
      QUEUE_NAME            => 'WINFORCE.WQ_WF_SOBJECTLIST_PPWK'
     ,ENQUEUE               => TRUE
     ,DEQUEUE               => TRUE);
END;
/


This is my enqueue proc
/* Formatted on 7/15/2020 5:10:41 PM (QP5 v5.326) */
CREATE OR REPLACE PROCEDURE ENQUEUE_WF_NOTIFICATION (
    IN_WF_OID                    IN     VARCHAR2,
    IN_WF_NOTIFICATION           IN     WINFORCE.WY_WF_NOTIFICATION_TYPE,
    OUT_ENQUEUE_STATUS              OUT VARCHAR2,
    OUT_ENQUEUE_STATUS_MESSAGE      OUT VARCHAR2)
IS
    PRAGMA AUTONOMOUS_TRANSACTION;

    C_METHOD_NAME              CONSTANT VARCHAR2 (30) := 'ENQUEUE_WF_NOTIFICATION';
    V_GLOBAL_ACCOUNT_ID                 WINFORCE.WT_WF_NOTIFICATION_STATUSES.GLOBAL_ACCOUNT_ID%TYPE;
    C_ENQUEUE_STATUS_SUCCESS   CONSTANT VARCHAR2 (30) := 'SUCCESS';
    C_ENQUEUE_STATUS_ERROR     CONSTANT VARCHAR2 (30) := 'ERROR';

    V_QUEUE_OPTIONS                     DBMS_AQ.ENQUEUE_OPTIONS_T;
    V_MESSAGE_PROPERTIES                DBMS_AQ.MESSAGE_PROPERTIES_T;
    R_MESSAGE_ID                        RAW (16);
    V_SEQ_NUM                           NUMBER := 0;
    V_QUEUE_NAME                        WINFORCE.WT_WF_QUEUE_DESIGNATORS.QUEUE_NAME%TYPE
        := 'WINFORCE.WQ_WF_SOBJECTLIST';
    L_MAINT_INTERACTION                 VARCHAR (255);
    V_SYNC_DELAY_IN_SECONDS             NUMBER (5) := 0;
BEGIN
    V_MESSAGE_PROPERTIES.CORRELATION := IN_WF_OID;

    V_QUEUE_NAME := 'My_Queue_Name';//Queue name is dynamic based on the transaction name
    V_MESSAGE_PROPERTIES.DELAY := 30;  //We have multiple queues and delay is dynamic through a table

    -- First let'S ENQUEUE!
    DBMS_AQ.ENQUEUE (QUEUE_NAME           => V_QUEUE_NAME,
                     ENQUEUE_OPTIONS      => V_QUEUE_OPTIONS,
                     MESSAGE_PROPERTIES   => V_MESSAGE_PROPERTIES,
                     PAYLOAD              => IN_WF_NOTIFICATION,
                     MSGID                => R_MESSAGE_ID);

    COMMIT;


    OUT_ENQUEUE_STATUS := C_ENQUEUE_STATUS_SUCCESS;
    OUT_ENQUEUE_STATUS_MESSAGE :=
           'Successfully enqueued in Queue : '
        || V_QUEUE_NAME
        || 'and delayed for 60 secs';
EXCEPTION
    WHEN OTHERS
    THEN
        ROLLBACK;
        OUT_ENQUEUE_STATUS := C_ENQUEUE_STATUS_ERROR;
        OUT_ENQUEUE_STATUS_MESSAGE :=
            C_PACKAGE_NAME || '.' || C_METHOD_NAME || '>>' || SQLERRM;
END ENQUEUE_WF_NOTIFICATION;



Dequeue PROC
/* Formatted on 7/15/2020 4:50:24 PM (QP5 v5.326) */
CREATE OR REPLACE PROCEDURE DEQUEUE_SOBJECTLIST_PPWK (
    CONTEXT    RAW,
    REGINFO    SYS.AQ$_REG_INFO,
    DESCR      SYS.AQ$_DESCRIPTOR,
    PAYLOAD    RAW,
    PAYLOADL   NUMBER)
AS
    L_DEQUEUE_OPTIONS        DBMS_AQ.DEQUEUE_OPTIONS_T;
    L_MESSAGE_PROPERTIES     DBMS_AQ.MESSAGE_PROPERTIES_T;
    MESSAGE                  RAW (16);
    C_METHOD_NAME   CONSTANT VARCHAR2 (30) := 'DEQUEUE_SOBJECTLIST_PPWK';
    L_MSG_ID                 RAW (16);
    CNT                      NUMBER := 0;
    CNT_MAX                  NUMBER := 50;
    MORE_MSGS                BOOLEAN := TRUE;
    NO_MESSAGES              EXCEPTION;
    PRAGMA EXCEPTION_INIT (NO_MESSAGES, -25228);
BEGIN
    L_DEQUEUE_OPTIONS.CONSUMER_NAME := DESCR.CONSUMER_NAME;
    L_DEQUEUE_OPTIONS.MSGID := DESCR.MSG_ID;

    LOOP
        --EVENTS ARE DEQUEING FROM THE BELOW PROCEDURE
        DBMS_AQ.DEQUEUE (QUEUE_NAME           => DESCR.QUEUE_NAME,
                         DEQUEUE_OPTIONS      => L_DEQUEUE_OPTIONS,
                         MESSAGE_PROPERTIES   => L_MESSAGE_PROPERTIES,
                         PAYLOAD              => MESSAGE,
                         MSGID                => L_MSG_ID);
        
  WK_SOBJECTLIST_RETRIEVER.PP_POST_DQ_CLBK (
            L_DEQUEUE_OPTIONS.MSGID);
        COMMIT;
    END LOOP;
EXCEPTION
    WHEN NO_MESSAGES
    THEN
        DBMS_OUTPUT.PUT_LINE ('No more messages for processing');
        COMMIT;
END DEQUEUE_SOBJECTLIST_PPWK;


And adding subscriber and Register
/* Formatted on 7/15/2020 4:36:21 PM (QP5 v5.326) */
BEGIN
    DBMS_AQADM.ADD_SUBSCRIBER (
        queue_name   => 'WINFORCE.WQ_WF_SOBJECTLIST_PPWK',
        subscriber   =>
            SYS.AQ$_AGENT ('WF_SOBJ_PPWK_SBSCRBR',
                           'WINFORCE.WQ_WF_SOBJECTLIST_PPWK',
                           NULL));



    DBMS_AQ.REGISTER (SYS.AQ$_REG_INFO_LIST (SYS.AQ$_REG_INFO (
                                                 'WINFORCE.WQ_WF_SOBJECTLIST_PPWK:WF_SOBJ_PPWK_SBSCRBR',
                                                 DBMS_AQ.NAMESPACE_AQ,
                                                 'plsql://WINFORCE.DEQUEUE_SOBJECTLIST_PPWK',
                                                 HEXTORAW ('FF'))),
                      1);
END;
/


Transactions are getting enqueued but not dequeued until we specifically call the dequeue procedure. What am I doing wrong? I compared with all code and unable to find the issue.

and Chris said...

Add some error logging to your dequeue procedure and you'll see:

ORA-25215: user_data type and queue type do not match


You're enqueuing a WY_WF_NOTIFICATION_TYPE (whatever that is) and dequeuing a RAW(16).

You need to enqueue and dequeue the same data type, e.g. changing the MESSAGE to WY_WF_NOTIFICATION_TYPE.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library