how to fix PLS-00306: wrong number or types of arguments in call to 'DEQUEUE'

46 Views Asked by At

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

0

There are 0 best solutions below