Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    20

    Unanswered: dbms_aq enqueue and dequeue problems

    Hi all,
    i am logging in to the oracle with username sys and have another user named aq in the database.
    i am creating priority based multiple user tables and queues and trying to enqueue , dequeue the messages.
    i am creating queuetable and queue in sys login and enqueueing and dequeueing in aq login.

    the code given below works fine
    but dequeue always says "timeout or end-of-fetch during message dequeue from AQ.PRIORITY_MSG_QUEUE_MUL"

    plz help me find out the problemetic code given below.

    create q table for priority and multiple consumers
    .................................................
    execute dbms_aqadm.create_queue_table(queue_table=>'aq.pri ority_msg_mul',sort_list=>'priority,enq_time',mult iple_consumers =>

    true,queue_payload_type=>'aq.message_typ',compatib le=>'8.1');




    create a q
    .................................................. ........
    execute dbms_aqadm.create_queue(queue_name =>'aq.priority_msg_queue_mul',queue_table=>'aq.pri ority_msg_mul');




    start q
    .................................................. ..........
    execute dbms_aqadm.start_queue(queue_name=>'aq.priority_ms g_queue_mul');


    add a subscriber
    .................................................. ......................
    declare
    subscriber sys.aq$_agent;
    begin
    subscriber:=sys.aq$_agent('subscriber1','aq.priori ty_msg_queue_mul@jatdb',null);
    dbms_aqadm.add_subscriber(
    queue_name => 'aq.priority_msg_queue_mul',
    subscriber=>subscriber,
    rule => 'priority < 2');
    end;
    /

    add another subscriber for priority <2
    .................................................. ......................
    declare
    subscriber sys.aq$_agent;
    begin
    subscriber:=sys.aq$_agent('subscriber2','aq.priori ty_msg_queue_mul@jatdb',null);
    dbms_aqadm.add_subscriber(
    queue_name => 'aq.priority_msg_queue_mul',
    subscriber=>subscriber,
    rule => 'priority < 2');
    end;
    /



    add another subscriber with priority >=2
    .................................................. ......................

    declare
    subscriber sys.aq$_agent;
    begin
    subscriber:=sys.aq$_agent('subscriber3','aq.priori ty_msg_queue_mul@jatdb',null);
    dbms_aqadm.add_subscriber(
    queue_name => 'aq.priority_msg_queue_mul',
    subscriber=>subscriber,
    rule => 'priority >= 2');
    end;
    /


    enqueue a msg with priority 1
    .................................................. ...........................
    declare
    eo dbms_aq.enqueue_options_t;
    mp dbms_aq.message_properties_t;
    mtyp aq.message_typ;
    m_id RAW(16);
    msg aq.message_typ;
    begin
    mp.priority:=1;
    DBMS_AQ.ENQUEUE
    (
    queue_name =>'aq.priority_msg_queue_mul',
    enqueue_options =>eo,
    message_properties => mp,
    payload => msg,
    msgid =>m_id
    );
    end;
    /





    enqueue a msg with priority 5
    .................................................. ...........................
    declare
    eo dbms_aq.enqueue_options_t;
    mp dbms_aq.message_properties_t;
    mtyp aq.message_typ;
    m_id RAW(16);
    msg aq.message_typ;
    begin
    mp.priority:=5;
    DBMS_AQ.ENQUEUE
    (
    queue_name =>'aq.priority_msg_queue_mul',
    enqueue_options =>eo,
    message_properties => mp,
    payload => msg,
    msgid =>m_id
    );
    end;
    /



    deque message as sub1
    .................................................. .....................
    declare
    do dbms_aq.dequeue_options_t ;
    mp dbms_aq.message_properties_t ;
    mhandle RAW(16);
    message aq.message_typ;
    begin
    do.consumer_name:='subscriber1';
    do.wait:=60;
    dbms_aq.dequeue(
    queue_name=>'aq.priority_msg_queue_mul',
    dequeue_options=>do,
    message_properties=>mp,
    payload=>message,
    msgid=>mhandle);
    end;
    /




    dequeue message as sub2
    .................................................. .....................
    declare
    do dbms_aq.dequeue_options_t ;
    mp dbms_aq.message_properties_t ;
    mhandle RAW(16);
    message aq.message_typ;
    begin
    do.consumer_name:='subscriber1';
    do.wait:=60;
    dbms_aq.dequeue(
    queue_name=>'aq.priority_msg_queue_mul',
    dequeue_options=>do,
    message_properties=>mp,
    payload=>message,
    msgid=>mhandle);
    end;
    /


    dequeue message as sub3
    .................................................. .....................
    declare
    do dbms_aq.dequeue_options_t ;
    mp dbms_aq.message_properties_t ;
    mhandle RAW(16);
    message aq.message_typ;
    begin
    do.consumer_name:='subscriber3';
    do.navigation:=dbms_aq.first_message;
    do.wait:=60;
    dbms_aq.dequeue(
    queue_name=>'aq.priority_msg_queue_mul',
    dequeue_options=>do,
    message_properties=>mp,
    payload=>message,
    msgid=>mhandle);
    end;
    /


    Thanks and regards,
    Sandy..

  2. #2
    Join Date
    Sep 2003
    Posts
    20
    hi,
    any dba/oracle expert having strong concepts of advanced queueing plz help me finding out of this problem.
    thanks n regards,
    sandy

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    the error u get is not really bad. It's a normal 'error' saying that no more messages are in the queue to be dequeued. Normally u trap this error using the exception handler to have a clean cut at the end of the procedure. Also,u will then be able to do more things in the procedure or return a message to the user saying that all messages have been retrieved.

    Ur problem is more that ur messages do not get dequeued, meaning u give the wrong credentials to retrieve the messages. Like retrieving messages for user a when messages are sent to user b, u won't find any messages.

    I did not yet look at ur code and i will asap, but maybe u can figure our ourself now what is the problem.

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •