Re: dbms_aq.dequeue - FIFO when using Correlation ID

I'd just like to confirm that Mathias' suggestion works very well. There was a bit of work to manage the pool but the impact on my existing code was small. 

Thanks again Mathias !!

Steve

On 04/06/2009, at 7:53 PM, Steve Baldwin wrote:
Thanks Mathias. The queue 'pooling' sounds promising. I'll give it a go and report back. 

Cheers,

Steve

On 04/06/2009, at 6:15 PM, Mathias Magnusson wrote:


Steve,
When you specify queue name, you can prefix it with the owner of the queue. Would that work for your situation?
Another way I've seen was a place where we needed lots and lots of queues, and they were precreated and then linked in a table with function to queue. It wasn't my design, but I can se how it may solve some problems in some cases.
I didn't want to spam the list in case this doesn't address your situation at all. If it helps, please reply to the list so we get this to the list for future reference. 

Mathias
On Thu, Jun 4, 2009 at 9:53 AM, Steve Baldwin <stbaldwin@xxxxxxxxxxxxxxxx > wrote: 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@xxxxxxxxxxxxxxxx > 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.




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.



------------------------
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.

Other related posts: