Home » Server Options » Streams & AQ » doubt regarding using variable in deq_condition
doubt regarding using variable in deq_condition [message #313146] Thu, 10 April 2008 20:35
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
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 ?
Previous Topic: Errors using streams
Next Topic: MGW agent can't start
Goto Forum:
  


Current Time: Thu Mar 28 06:19:40 CDT 2024