<small id='fCUL5'></small><noframes id='fCUL5'>

  1. <i id='fCUL5'><tr id='fCUL5'><dt id='fCUL5'><q id='fCUL5'><span id='fCUL5'><b id='fCUL5'><form id='fCUL5'><ins id='fCUL5'></ins><ul id='fCUL5'></ul><sub id='fCUL5'></sub></form><legend id='fCUL5'></legend><bdo id='fCUL5'><pre id='fCUL5'><center id='fCUL5'></center></pre></bdo></b><th id='fCUL5'></th></span></q></dt></tr></i><div id='fCUL5'><tfoot id='fCUL5'></tfoot><dl id='fCUL5'><fieldset id='fCUL5'></fieldset></dl></div>
  2. <legend id='fCUL5'><style id='fCUL5'><dir id='fCUL5'><q id='fCUL5'></q></dir></style></legend>
    • <bdo id='fCUL5'></bdo><ul id='fCUL5'></ul>
    1. <tfoot id='fCUL5'></tfoot>

      Oracle Advance Queue - 出队不工作

      Oracle Advance Queue - Dequeue not working(Oracle Advance Queue - 出队不工作)

          <i id='wineY'><tr id='wineY'><dt id='wineY'><q id='wineY'><span id='wineY'><b id='wineY'><form id='wineY'><ins id='wineY'></ins><ul id='wineY'></ul><sub id='wineY'></sub></form><legend id='wineY'></legend><bdo id='wineY'><pre id='wineY'><center id='wineY'></center></pre></bdo></b><th id='wineY'></th></span></q></dt></tr></i><div id='wineY'><tfoot id='wineY'></tfoot><dl id='wineY'><fieldset id='wineY'></fieldset></dl></div>

          1. <small id='wineY'></small><noframes id='wineY'>

            <tfoot id='wineY'></tfoot>
          2. <legend id='wineY'><style id='wineY'><dir id='wineY'><q id='wineY'></q></dir></style></legend>
              <tbody id='wineY'></tbody>
              <bdo id='wineY'></bdo><ul id='wineY'></ul>

                本文介绍了Oracle Advance Queue - 出队不工作的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                问题描述

                我似乎无法找到解决问题的方法,我已经被困在这个问题上好几个小时了.

                I can't seem to find the solution to my problem, I've been stuck at this for hours.

                我正在使用 Oracle AQ:

                I'm usings Oracle AQs:

                       Dbms_Aqadm.Create_Queue_Table(Queue_Table        => 'ITEM_EVENT_QT',
                                                    Queue_Payload_Type => 'ITEM_EVENT',
                                                    Multiple_Consumers => TRUE);
                
                       Dbms_Aqadm.Create_Queue(Queue_Name          => 'ITEM_EVENT_QUEUE',
                                              Queue_Table         => 'ITEM_EVENT_QT',
                                              Max_Retries         => 5,
                                              Retry_Delay         => 0,
                                              Retention_Time      => 432000, -- 5 DAYS
                                              Dependency_Tracking => FALSE,
                                              COMMENT             => 'Item Event Queue');
                       -- START THE QUEUE
                       Dbms_Aqadm.Start_Queue('ITEM_EVENT_QUEUE');
                       -- GRANT QUEUE PRIVILEGES
                       Dbms_Aqadm.Grant_Queue_Privilege(Privilege    => 'ALL',
                                                       Queue_Name   => 'ITEM_EVENT_QUEUE',
                                                       Grantee      => 'PUBLIC',
                                                       Grant_Option => FALSE);
                    END;
                

                这是我的一位订阅者:

                Dbms_Aqadm.Add_Subscriber(Queue_Name => 'ITEM_EVENT_QUEUE',
                                            Subscriber => Sys.Aq$_Agent('ITEM_SUBSCRIBER_1',
                                                                        NULL,
                                                                        NULL),
                                            rule   =>  'tab.user_data.header.thread_no = 1');
                
                   Dbms_Aq.Register(Sys.Aq$_Reg_Info_List(Sys.Aq$_Reg_Info('ITEM_EVENT_QUEUE:ITEM_SUBSCRIBER_1',
                                                                          Dbms_Aq.Namespace_Aq,
                                                                          'plsql://ITEM_API.GET_QUEUE_FROM_QUEUE',
                                                                          HEXTORAW('FF'))),1);
                

                订阅者注册:

                每当我的数据库上发生某个事件时,我都会通过从我的 ITEM_API 包中调用以下过程,使用触发器将事件"添加到我的 AQ:

                Whenever a certain event occurs on my DB, I'm using a trigger to add "the event" to my AQ by calling the following procedure from my ITEM_API package:

                  PROCEDURE ADD_EVENT_TO_QUEUE(I_EVENT       IN ITEM_EVENT,
                                               O_STATUS_CODE OUT VARCHAR2,
                                               O_ERROR_MSG   OUT VARCHAR2) IS
                
                    ENQUEUE_OPTIONS    DBMS_AQ.ENQUEUE_OPTIONS_T;
                    MESSAGE_PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
                    MESSAGE_HANDLE     RAW(16);
                    EVENT              ITEM_EVENT;
                    HEADER_PROP        HEADER_PROPERTIES;
                  BEGIN
                    EVENT                              := I_EVENT;
                    EVENT.SEQ_NO                       := ITEM_EVENT_SEQ.NEXTVAL;
                    ENQUEUE_OPTIONS.VISIBILITY         := DBMS_AQ.ON_COMMIT;
                    ENQUEUE_OPTIONS.SEQUENCE_DEVIATION := NULL;
                    MESSAGE_PROPERTIES.PRIORITY        := 1;
                    MESSAGE_PROPERTIES.DELAY           := DBMS_AQ.NO_DELAY;
                    MESSAGE_PROPERTIES.EXPIRATION      := DBMS_AQ.NEVER;
                    HEADER_PROP                        := HEADER_PROPERTIES(1);
                    EVENT.HEADER                       := HEADER_PROP;
                    DBMS_AQ.ENQUEUE(QUEUE_NAME         => 'ITEM_EVENT_QUEUE',
                                    ENQUEUE_OPTIONS    => ENQUEUE_OPTIONS,
                                    MESSAGE_PROPERTIES => MESSAGE_PROPERTIES,
                                    PAYLOAD            => EVENT,
                                    MSGID              => MESSAGE_HANDLE);
                  EXCEPTION
                    WHEN OTHERS THEN
                      ERROR_HANDLER.LOG_ERROR(NULL,
                                              EVENT.ITEM,
                                              EVENT.SEQ_NO,
                                              SQLCODE,
                                              SQLERRM,
                                              O_STATUS_CODE,
                                              O_ERROR_MSG);
                      RAISE;
                  END ADD_EVENT_TO_QUEUE;
                

                而且它正在工作,因为当我检查我的 AQ 表时,我可以找到事件",但是我的出队方法没有出队,如下图所示,没有 DEQ_TIME.

                And it's working because when I check my AQ table, I can find "the event", however my dequeue method is not dequeing, as you can see in the image bellow, there's no DEQ_TIME.

                这是我的出队方法,同样来自我的 ITEM_API 包:

                Here's my dequeue method, also from my ITEM_API package:

                  PROCEDURE GET_QUEUE_FROM_QUEUE(CONTEXT  RAW,
                                                 REGINFO  SYS.AQ$_REG_INFO,
                                                 DESCR    SYS.AQ$_DESCRIPTOR,
                                                 PAYLOAD  RAW,
                                                 PAYLOADL NUMBER) IS
                
                    R_DEQUEUE_OPTIONS    DBMS_AQ.DEQUEUE_OPTIONS_T;
                    R_MESSAGE_PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
                    V_MESSAGE_HANDLE     RAW(16);
                    I_PAYLOAD            ITEM_EVENT;
                    L_PROC_EVENT         BOOLEAN;
                    O_TARGETS            CFG_EVENT_STAGE_TBL;
                    O_ERROR_MSG          VARCHAR2(300);
                    O_STATUS_CODE        VARCHAR2(100);
                  BEGIN
                    R_DEQUEUE_OPTIONS.MSGID         := DESCR.MSG_ID;
                    R_DEQUEUE_OPTIONS.CONSUMER_NAME := DESCR.CONSUMER_NAME;
                    R_DEQUEUE_OPTIONS.DEQUEUE_MODE  := DBMS_AQ.REMOVE;
                    --R_DEQUEUE_OPTIONS.WAIT          := DBMS_AQ.NO_WAIT;
                    DBMS_AQ.DEQUEUE(QUEUE_NAME         => DESCR.QUEUE_NAME,
                                    DEQUEUE_OPTIONS    => R_DEQUEUE_OPTIONS,
                                    MESSAGE_PROPERTIES => R_MESSAGE_PROPERTIES,
                                    PAYLOAD            => I_PAYLOAD,
                                    MSGID              => V_MESSAGE_HANDLE);
                    IF I_PAYLOAD IS NOT NULL THEN
                      L_PROC_EVENT := PROCESS_EVENT(I_PAYLOAD,
                                                    O_TARGETS,
                                                    O_STATUS_CODE,
                                                    O_ERROR_MSG);
                    END IF;
                  EXCEPTION
                    WHEN OTHERS THEN
                      ERROR_HANDLER.LOG_ERROR(NULL,
                                              NULL,
                                              NULL,
                                              SQLCODE,
                                              SQLERRM,
                                              O_STATUS_CODE,
                                              O_ERROR_MSG);
                      RAISE;
                  END GET_QUEUE_FROM_QUEUE;
                

                我做错了吗?我怎样才能解决这个问题?我认为我的订阅者注册可能有问题,但我不确定.

                Am I doing something wrong? How can I fix this? I think there might be a problem with my subscriber registration, but I'm not sure.

                我刚刚发现如果我删除订阅者和注册,然后重新添加它们,它们将使所有消息出列.但是,如果另一个事件进入队列,它会无限期地停留在那里(或直到我再次删除并添加订阅者):

                I've just figured out that if I remove the subscribers and the register, and then re-add them, they'll dequeue all messages. Howerver if another event gets enqueued, it stays there indefinetly (or until I remove and add the subscribers again):

                状态为 0 且没有 DEQ_TIME 的记录是新记录.

                The record with state 0 and no DEQ_TIME is the new one.

                我需要调度程序或类似的东西吗?

                Do I need a scheduler or something like that?

                我已将调度程序传播添加到我的 AQ:

                I've added a scheduler propagation to my AQ:

                DBMS_AQADM.SCHEDULE_PROPAGATION('ITEM_EVENT_QUEUE');
                

                甚至添加了 next_time 字段:

                and even added the next_time field:

                DBMS_AQADM.SCHEDULE_PROPAGATION('ITEM_EVENT_QUEUE', SYSDATE + 30/86400);
                

                还是不行.有什么建议?我猜 AQ 通知不起作用,而且我的回调过程从未被调用.我该如何解决这个问题?

                Still doesn't work. Any suggestions? I guess the AQ Notifications aren't working, and my callback procedure is never called. How can I fix this?

                我从包中删除了我的程序只是为了测试目的,所以我的队友可以编译 ITEM_API 包(我不知道是否重新编译包,可能会也可能不会对出队过程有影响).还是不行.

                I've removed my procedure from the package just for testing purposes, so my team mates can compile the ITEM_API package (I don't know if recompiling the package, may or may not have impacts on the dequeue process). Still doesn't work.

                推荐答案

                创建一个代码块并运行以下:

                Create a code block and run the following:

                DECLARE
                  dequeue_options      DBMS_AQ.dequeue_options_t;
                  message_properties   DBMS_AQ.message_properties_t;
                  message_handle       RAW (16);
                  I_PAYLOAD            ITEM_EVENT;
                  no_messages exception;
                  msg_content          VARCHAR2 (4000);
                  PRAGMA EXCEPTION_INIT (no_messages, -25228);
                BEGIN
                  dequeue_options.wait := DBMS_AQ.NO_WAIT;
                  dequeue_options.consumer_name := 'ITEM_SUBSCRIBER_1';   
                  dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;
                LOOP    
                 DBMS_AQ.DEQUEUE (queue_name           => 'ITEM_EVENT_QUEUE',
                                      dequeue_options      => dequeue_options,
                                      message_properties   => message_properties,
                                      payload              => I_PAYLOAD,
                                      msgid                => message_handle
                                     );
                END LOOP;
                  EXCEPTION
                  WHEN no_messages
                  THEN
                     DBMS_OUTPUT.PUT_LINE ('No more messages left');
                END;
                

                让我知道您排队的消息会发生什么.

                Let me know what happens to your enqueued messages.

                您应该有一个表,用于对数据进行 dequeing.

                You should have a table where you're dequing the data.

                你也可以试试在agent中加入enqueud表,然后将agent指定到dequeue表中.

                Can you also try adding the enqueud table in the agent and then specify the agent to the dequeue table.

                DECLARE
                  aSubscriber sys.aq$_agent;
                BEGIN 
                  aSubscriber := sys.aq$_agent('ITEM_SUBSCRIBER_1',
                                          'ITEM_EVENT_QUEUE',
                                          0);
                  dbms_aqadm.add_subscriber
                 ( queue_name     => 'ITEM_EVENT_QUEUE'
                  ,subscriber     => aSubscriber);
                END;
                /
                

                这篇关于Oracle Advance Queue - 出队不工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                相关文档推荐

                How to call Oracle function or stored procedure using spring persistence framework?(如何使用 Spring 持久化框架调用 Oracle 函数或存储过程?)
                What can cause intermittent ORA-12519 (TNS: no appropriate handler found) errors(什么会导致间歇性 ORA-12519(TNS:找不到合适的处理程序)错误)
                SQL to return the number of working days between 2 passed in dates(SQL 返回两个传入日期之间的工作日数)
                Oracle Date Subtraction(Oracle 日期减法)
                Is using quot;select *quot; for a cursor in PL/SQL considered bad programming?(正在使用“选择 *PL/SQL 中的游标被认为是糟糕的编程?)
                Using sql/plsql, how do you find out which character set a text uses?(使用 sql/plsql,你如何找出文本使用的字符集?)
                <legend id='HvTiV'><style id='HvTiV'><dir id='HvTiV'><q id='HvTiV'></q></dir></style></legend>

                  <tbody id='HvTiV'></tbody>

                    <bdo id='HvTiV'></bdo><ul id='HvTiV'></ul>

                    <tfoot id='HvTiV'></tfoot>

                        <small id='HvTiV'></small><noframes id='HvTiV'>

                      • <i id='HvTiV'><tr id='HvTiV'><dt id='HvTiV'><q id='HvTiV'><span id='HvTiV'><b id='HvTiV'><form id='HvTiV'><ins id='HvTiV'></ins><ul id='HvTiV'></ul><sub id='HvTiV'></sub></form><legend id='HvTiV'></legend><bdo id='HvTiV'><pre id='HvTiV'><center id='HvTiV'></center></pre></bdo></b><th id='HvTiV'></th></span></q></dt></tr></i><div id='HvTiV'><tfoot id='HvTiV'></tfoot><dl id='HvTiV'><fieldset id='HvTiV'></fieldset></dl></div>