I'm testing Oracle 11.2g Advanced queues.
Got stuck with DBMS_AQ.DEQUEUE procedure.
Getting error:
SQL Error [6550] [65000]: ORA-06550: line 15, column 2: PLS-00306: wrong number or types of arguments in call to 'DEQUEUE' ORA-06550: line 15, column 2: PL/SQL: Statement ignored
where is the issue?
This is the anonymous pl/sql block I'm executing:
DECLARE
l_dequeue_options DBMS_AQ.dequeue_options_t;
o_message_properties DBMS_AQ.message_properties_t;
o_msgid RAW(16);
o_payload WIMO.xml_object_t;
l_xml_msg XMLType;
l_country_name varchar2(30);
BEGIN
--l_dequeue_options.navigation := DBMS_AQ.NEXT_MESSAGE;
--l_dequeue_options.dequeue_mode := DBMS_AQ.REMOVE_NODATA ;
--l_dequeue_options.visibility := DBMS_AQ.ON_COMMIT;
-- dequeue message
DBMS_AQ.DEQUEUE(
queue_name => 'TEST.CI_TO_TEST_Q',
dequeue_options => l_dequeue_options,
message_propoerties => o_message_properties,
payload => o_payload,
msgid => o_msgid
);
l_xml_msg := o_payload.payload;
/*
* <?xml version="1.0"?>
<countries>
<country>
<name>Canada</name>
</country>
</countries>
*/
SELECT value(p).extract('/name/text()').getstringval()
INTO l_country_name
FROM TABLE (xmlsequence(EXTRACT(l_xml_msg,'/countries/country/name'))) p;
dbms_output.put_line(l_country_name);
END;
below providing commands to create all required assets before it is possible to dequeue a corresponding message:
CREATE TYPE WIMO.xml_object_t AS object(
payload XMLtype
);
--2. queue table
-- should it be in a separate tablespac? probably yes
BEGIN
sys.DBMS_AQADM.CREATE_QUEUE_TABLE(
queue_table => 'TEST.CI_TO_TEST_QT',
queue_payload_type => 'TEST.xml_object_t',
multiple_consumers => FALSE,
comment => 'queue table that hosts messages for which TEST is a consumer'
--secure => TRUE
);
END;
--3. Exception queue table
BEGIN
sys.DBMS_AQADM.CREATE_QUEUE_TABLE(
queue_table => 'TEST.CI_TO_TEST_E_QT',
queue_payload_type => 'TEST.xml_object_t',
multiple_consumers => FALSE,
comment => 'exception queue table that hosts messages for which TEST is a consumer'
);
END;
--4. exception queue
BEGIN
-- Exception queue
DBMS_AQADM.CREATE_QUEUE(
queue_name => 'TEST.CI_TO_TEST_E_Q',
queue_table => 'TEST.CI_TO_TEST_E_QT',
queue_type => DBMS_AQADM.EXCEPTION_QUEUE,
--max_retries IN NUMBER DEFAULT NULL,
--retry_delay IN NUMBER DEFAULT 0,
--dependency_tracking IN BOOLEAN DEFAULT FALSE,
comment => 'exception queue that hosts messages for which TEST is a consumer');
END;
--5. noraml queue
BEGIN
-- normal queue
DBMS_AQADM.CREATE_QUEUE(
queue_name => 'TEST.CI_TO_TEST_Q',
queue_table => 'TEST.CI_TO_TEST_QT',
--queue_type IN BINARY_INTEGER DEFAULT NORMAL_QUEUE,
--max_retries IN NUMBER DEFAULT NULL,
--retry_delay IN NUMBER DEFAULT 0,
retention_time => DBMS_AQADM.INFINITE,
--dependency_tracking IN BOOLEAN DEFAULT FALSE,
comment => 'queue that hosts messages for which TEST is a consumer');
END;
COMMIT;
--6. start a queue
BEGIN
DBMS_AQADM.START_QUEUE(
queue_name => 'TEST.CI_TO_TEST_Q',
enqueue => TRUE,
dequeue => TRUE);
END;
COMMIT;
--7. enqueue message
DECLARE
l_msg XMLtype;
l_enqueue_options DBMS_AQ.enqueue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_xml_msg XMLtype;
l_payload WIMO.xml_object_t;
r_msg_id RAW(16);
BEGIN
l_xml_msg := XMLtype('<?xml version="1.0"?><countries><country><name>Canada</name></country></countries>');
l_payload := WIMO.xml_object_t(l_xml_msg);
l_message_properties.priority := 1;
l_message_properties.delay := DBMS_AQ.NO_DELAY;
l_message_properties.expiration := DBMS_AQ.NEVER;
l_message_properties.correlation := 1;
l_message_properties.exception_queue :='TEST.CI_TO_TEST_E_Q';
l_enqueue_options.visibility := DBMS_AQ.ON_COMMIT;
DBMS_AQ.ENQUEUE(
queue_name => 'TEST.CI_TO_TEST_Q',
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload => l_payload,
msgid => r_msg_id);
dbms_output.put_line(r_msg_id);
END;
COMMIT;
I expect a value Canada in the output.
I'm using this documentation to validate input parameters.
Oracle version I'm working with Oracle 11.2.0.4.0