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

    Unanswered: Problem in Priority Based Enqueuing n dequeuing

    Hi all,

    I am furnishing the code for priority based enquing and dequeuing below. I have opened two sessions with two usernames - aq for enqueuing messages into the priority based queue and sys for dequeuing the priority based queue. Enqueuing is going on smoothly, but the session of user sys is hanging up while trying to dequeue.

    Code:
    ----------


    CONNECT sys/pwd ;

    DROP USER aq CASCADE;
    CREATE USER aq IDENTIFIED BY aq;
    GRANT CONNECT, RESOURCE TO aq;
    GRANT EXECUTE ON dbms_aq TO aq;
    GRANT EXECUTE ON dbms_aqadm TO aq;


    ************************************************** *
    Create a Prioritized Message Queue Table and Queue
    ************************************************** *

    connect aq/aq

    /* create the following in the session of user AQ */

    EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE (
    queue_table => 'aq.priority_msg',
    sort_list => 'PRIORITY,ENQ_TIME',
    queue_payload_type => 'aq.Message_typ');

    EXECUTE DBMS_AQADM.CREATE_QUEUE (
    queue_name => 'priority_msg_queue',
    queue_table => 'aq.priority_msg');

    EXECUTE DBMS_AQADM.START_QUEUE (
    queue_name => 'priority_msg_queue');


    ************************************************** *******
    Enqueue and Dequeue of Messages by Priority Using PL/SQL
    ************************************************** *******

    ENQUEUE OF MESSAGE BY AQ AND DEQUEUE OF MESSAGES BY ANOTHER USER SYS
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~

    /* Enqueue of messages by AQ */

    /* Enqueue two messages with priority 30 and 5: */

    DECLARE
    enqueue_options dbms_aq.enqueue_options_t;
    message_properties dbms_aq.message_properties_t;
    message_handle RAW(16);
    message aq.message_typ;

    BEGIN
    message := message_typ('PRIORITY MESSAGE',
    'enqued at priority 30.');

    message_properties.priority := 30;

    DBMS_AQ.ENQUEUE(queue_name => 'priority_msg_queue',
    enqueue_options => enqueue_options,
    message_properties => message_properties,
    payload => message,
    msgid => message_handle);

    message := message_typ('PRIORITY MESSAGE',
    'Enqueued at priority 5.');

    message_properties.priority := 5;

    DBMS_AQ.ENQUEUE(queue_name => 'priority_msg_queue',
    enqueue_options => enqueue_options,
    message_properties => message_properties,
    payload => message,
    msgid => message_handle);
    END;


    connect sys/pwd;

    /* Dequeue of messages by SYS */

    /* Dequeue from priority queue: */

    DECLARE
    dequeue_options DBMS_AQ.dequeue_options_t;
    message_properties DBMS_AQ.message_properties_t;
    message_handle RAW(16);
    message aq.message_typ;

    BEGIN
    DBMS_AQ.DEQUEUE(queue_name => 'aq.priority_msg_queue',
    dequeue_options => dequeue_options,
    message_properties => message_properties,
    payload => message,
    msgid => message_handle);

    DBMS_OUTPUT.PUT_LINE ('Message: ' || message.subject ||
    ' ... ' || message.text );

    COMMIT;

    DBMS_AQ.DEQUEUE(queue_name => 'aq.priority_msg_queue',
    dequeue_options => dequeue_options,
    message_properties => message_properties,
    payload => message,
    msgid => message_handle);

    DBMS_OUTPUT.PUT_LINE ('Message: ' || message.subject ||
    ' ... ' || message.text );
    COMMIT;
    END;


    Can anybody please help in identifying the problem?

    Thanks & regards,
    San.
    Last edited by skdas; 10-13-03 at 08:29.

Posting Permissions

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