Re: Blocking stored procedure leaves an opened session

From: Raj Jamadagni <rjamadagni_at_hotmail.com>
Date: 20 Jan 2004 12:27:01 -0800
Message-ID: <76772c3f.0401201227.7be30026@posting.google.com>

No, actually what you want is to set the wait parameter to something real rather than dbms_aq.no_wait ... seconds when you dequeue ... see here for an example ... I have a wait of 2 seconds ...

unnecessary stuff is deleted to save space ...

<example>

    ....

     dequeue_options          DBMS_AQ.dequeue_options_t;
     no_messages              EXCEPTION;
     PRAGMA EXCEPTION_INIT (no_messages, -25228);

   --
   BEGIN

     gv_feed_name := UPPER (subscribername);


     -- this will allow us to identify the tracefile quickly ... 

     EXECUTE IMMEDIATE 'alter session set tracefile_identifier="' ||
                       UPPER (subscribername) || '"';


     -- 

     gv_identifier := LOWER('stw:' || SUBSTR(gv_feed_name,1,3));
     p('Entered start_feed.');
     dbms_application_info.set_module('XXXX_PKG',gv_identifier ||
                                      ' feed started.');


     -- Set AQ Options

     -- Changed by Raj ... we should timeout every 2 seconds ...

     p('De-queue timeout set to 2 seconds, complaint threshold is 5
minutes.');
     dequeue_options.WAIT          := 2; -- dbms_aq.no_wait;
     dequeue_options.navigation    := DBMS_AQ.first_message;
     dequeue_options.dequeue_mode  := DBMS_AQ.remove;
     dequeue_options.consumer_name := subscribername;
     --
     p('Awaiting dequeue.');
     WHILE (1 = 1)
     LOOP
       -- main message retreival
       BEGIN
         DBMS_AQ.dequeue ( 'XXXX.YYYY'
                          ,dequeue_options
                          ,message_properties
                          ,dequeue_message
                          ,dequeue_message_handle);
         ....
       EXCEPTION
         WHEN no_messages THEN
           this is raised when my code doesn't get any message in 2
seconds ...
            you can handle it any way you like.
           ...
       end;

    end loop;
</example>

HTH
Raj Received on Tue Jan 20 2004 - 14:27:01 CST