Friday, July 28, 2006

Oracle Advanced Queue pl/sql Subscriptions

Here is a quick pointer on Oracle Advanced Queues and pl/sql registrations. In the documenation it tells you that the queue name must be less than or equal to 30 characters for pl/sql subscriptions to work. It doesn't mention that schema.queue_name must be <= 30. But that is the case--at least in 9i. Once you get to 10g (or 10.2.0.1 anyway) it is even more difficult. Same rule applies, but what they don't tell you is that in 10g it is "schema"."queue_name" must be <= 30. So really, the length of the queue name plus the length of the schema must be <= 25. This can be tricky if you do an upgrade and suddenly your plsql queue stops working.

1 comment:

Anonymous said...

If you wrap your code which does the enqueue/dequeue in stored procedure that is created in the schema which owns the queue, then you can do away with the schema name. Of course, this method is assuming that you can call pl/sql from the environment that you're queuing from.