OraFAQ Forum: Streams & AQ » Using SQL statement to browse OJMS queue

Home » Server Options » Streams & AQ » Using SQL statement to browse OJMS queue

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Using SQL statement to browse OJMS queue [message #158572]

Mon, 13 February 2006 04:43

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.

Report message to a moderator

Re: Using SQL statement to browse OJMS queue [message #164879 is a reply to message #158572]

Mon, 27 March 2006 05:21

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

Report message to a moderator

Previous Topic:	Streams: why the messages always spill into the queue table?
Next Topic:	sqlca.sqlcode-1480

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 04 12:23:40 CST 2024