Home » Server Options » Streams & AQ » Using SQL statement to browse OJMS queue
Using SQL statement to browse OJMS queue [message #158572] Mon, 13 February 2006 04:43 Go to next message
sanju.sinha@gmail.com
Messages: 30
Registered: November 2005
Member

Hello,
I am using OJMS. I have created queue with payload type "AQ$_JMS_MESSAGE".

While sending messages I create a property that contains user defined message id.

Now, I would like to fire a sql select statement to get messages in order of this property.

Is it possible?

Also, in java, for the same payload type, how can I select user data from the queue table and display the values.

Or in PLSQL how to display the user defined properties of OJMS.

Thanks & regards,
Sanjeev.

Re: Using SQL statement to browse OJMS queue [message #164879 is a reply to message #158572] Mon, 27 March 2006 05:21 Go to previous message
hobbes
Messages: 173
Registered: January 2006
Senior Member
This is how you could browse through your OJMS queue in PL/SQL (replace "myQueue" with your AQ name, "myQueueTable" with the corresponding AQ table name):

set serverout on
prompt View Queue.......................
declare
    deqopt dbms_aq.dequeue_options_t;    
    mprop dbms_aq.message_properties_t;    msgid RAW(16);
    payload SYS.AQ$_JMS_MESSAGE;

   count_before  NUMBER(10);
   count_after   NUMBER(10);
    EmptyQueue   EXCEPTION;
PRAGMA EXCEPTION_INIT(EmptyQueue, -25228);

begin
    deqopt.navigation := DBMS_AQ.FIRST_MESSAGE; 
    deqopt.wait := DBMS_AQ.NO_WAIT; 
    deqopt.dequeue_mode := DBMS_AQ.BROWSE; 

    EXECUTE IMMEDIATE 'select count(*) from myQueueTable' into count_before;

    WHILE (TRUE) LOOP

    dbms_aq.dequeue(     queue_name => 'myQueue',
                          dequeue_options => deqopt,
                          message_properties => mprop,
                          payload => payload,
                          msgid => msgid    );

    dbms_output.put_line('---------');
    dbms_output.put_line(SUBSTR(payload.TEXT_VC, 1, 255));

    deqopt.navigation := DBMS_AQ.NEXT_MESSAGE;
    END LOOP;

    EXECUTE IMMEDIATE 'select count(*) from myQueueTable' into count_after; 

exception
  when EmptyQueue then 
    dbms_output.put_line('============');
    dbms_output.put_line('End Of Queue');
end;
/


Hope this helps!
Hobbes
Previous Topic: Streams: why the messages always spill into the queue table?
Next Topic: sqlca.sqlcode-1480
Goto Forum:
  


Current Time: Thu Mar 28 08:19:41 CDT 2024