Re: dbms_aq.dequeue - FIFO when using Correlation ID
Date: Thu, 4 Jun 2009 17:53:47 +1000
Message-ID: <429582A0-03A6-4A9A-9E9D-DA1A90AE75B0_at_multiservice.com>
Thanks for the reply Mathias. The reason I use CorrelID to specify the queue is that I ran into problems trying to dynamically create queues. Your suggestion was how I originally coded it but I couldn't get this approach to work. Here's why ...
As I'm sure you know, queue's in the dbms_pipe world can be created dynamically and in fact can be implicitly created by just sending a message to a queue.
In our dbms_pipe replacement, I didn't want to duplicate the new package in every schema that was previously using dbms_pipe so I implemented it in a central schema and granted execute on the new package to the other schemas. The other schemas then create a private synonym called dbms_pipe to central_schema.pipe_replacement_pkg and no other code changes are required.
However, dbms_aq rather nastily specifies AUTHID CURRENT_USER so when schema x executed my new package and attempted to dynamically create a queue, it was trying to do so in its own schema. Of course that is not the way dbms_pipe works (where queues are 'global' to the instance) so if I had schema x issuing the enqueue and schema y issuing the dequeue, it just isn't going to work. I tried creating a central table (in the same schema owning the new package), but even if that worked (and I can't remember whether it did or not), it means I need to make it publicly accessible which of course introduces security issues.
Hence my decision to have a single queue table and implement the logical queues by using CorrelID's.
If you can think of a better way to do this I'd love to hear it.
Thanks,
Steve
On 04/06/2009, at 4:23 PM, Mathias Magnusson wrote:
> My memory on this is foggy. But isn't the recommended (by Oracle)
> solution to put the message id last in the corr id to make it unique
> and in the oder of enqueuing? I'm not sure hot to get that value at
> the time of enqueuing though. So, I may be wrong on how this
> typically is solved.
>
> Why are you using corr id to specify a queue? Why not just use
> different queues?
>
> I was under the impression that ordered would still be honored
> within the set of messages a corr id is valid for. I guess I missed
> the same passage as you just found.
>
> Mathias
>
> On Thu, Jun 4, 2009 at 7:16 AM, Steve Baldwin <stbaldwin_at_multiservice.com
> > wrote:
> Hi,
>
> I've implemented a dbms_pipe clone using AQ (we're using RAC which
> doesn't support dbms_pipe - long story). Basically I've simulated
> dbms_pipe message queues by using CorrelationID's.
>
> Unfortunately, it seems that I missed the 'gotcha' in the Oracle
> documentation - (when retrieving a message by CorrelationID) "If
> more than one message satisfies the pattern, then the order of
> dequeuing is undetermined". It is critical in my scenario that
> messages for a given 'queue' (Correlation ID) are dequeued in the
> order they were enqueued.
>
> Does anyone have a trick or suggestion to solve my dilemma. We're
> using 11.1.0.7 in case it matters.
>
> Thanks,
>
> Steve
>
> ------------------------
> This email is intended solely for the use of the addressee and may
> contain information that is confidential, proprietary, or both.
> If you receive this email in error please immediately notify the
> sender and delete the email.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
This email is intended solely for the use of the addressee and may contain information that is confidential, proprietary, or both. If you receive this email in error please immediately notify the sender and delete the email.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 04 2009 - 02:53:47 CDT