I am using Oracle 11g in Linux Platform .
I am trying to dequeue a message based on a condition and the condition involves a variable .
DECLARE
l_enqueue_options DBMS_AQ.enqueue_options_t;
l_dequeue_options DBMS_AQ.dequeue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_message_handle RAW(16);
l_event_msg event_msg_type;
worker_status VARCHAR2(30) := NULL;
worker_no NUMBER;
BEGIN
SELECT worker_no_seq.nextval INTO worker_no FROM DUAL;
l_event_msg := event_msg_type(worker_no, 'Hi', 'NEW', systimestamp, NULL);
DBMS_AQ.enqueue(queue_name => 'event_queue',
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload => l_event_msg,
msgid => l_message_handle);
COMMIT;
l_dequeue_options.deq_condition := 'tab.user_data.worker_id = :worker_no
AND tab.user_data.status=''DONE''';
DBMS_AQ.dequeue(queue_name => 'event_queue',
dequeue_options => l_dequeue_options,
message_properties => l_message_properties,
payload => l_event_msg,
msgid => l_message_handle);
COMMIT
END;
Here I am en-queuing a message which is dequeued by another procedure and some calculations are done based on the message and the message is enqueued back with same worker_id and status is updated as DONE . Now the code given above is a blocking call to dequeue the message with same worker_id and with status as 'DONE' . But since the condition involves a variable I am getting an error
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at "SYS.DBMS_AQ", line 335
Let me know what should be done ?
] 
Blog
Search
Help
Members
Register
Login
Home
