Oracle Advance Queue-出队不起作用 [英] Oracle Advance Queue - Dequeue not working
问题描述
我似乎找不到解决问题的方法，我已经被困了几个小时了.
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.
您应该有一个要在其中使用数据的表.
You should have a table where you're dequing the data.
您是否还可以尝试在代理中添加入队表，然后将代理指定到出队表中.
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-出队不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
