Oracle 出列不使用寄存器过程

创建队列表

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;
/

这是我的入队过程

/* 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;

出队过程

/* 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;

并添加订户和寄存器

/* 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;
/

事务正在排队，但没有出队，直到我们专门调用出队过程。我做错了什么？我与所有代码进行了比较，无法找到问题所在。

将一些错误日志记录添加到您的出队过程中，您将看到:

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

您正在排队WY_WF_NOTIFICATION_TYPE (无论是什么) 并出队RAW(16)。

您需要将same数据类型，例如将消息更改为WY_WF_NOTIFICATION_TYPE。