DBA_QUEUES and the little mistery of the blanks

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

I just found out that columns ENQUEUE_ENABLED and DEQUEUE_ENABLED in views DBA_QUEUES, ALL_QUEUES and USER_QUEUES, return a couple of (unnecessary?) leading and trailing blank characters.

select name, queue_type, translate(dequeue_enabled, ' ', '.') dequeue
from dba_queues;

NAME            QUEUE_TYPE       DEQUEUE
--------------  ---------------  -------
AQ$_ALERT_QT_E  EXCEPTION_QUEUE  ..NO..
ALERT_QUE       NORMAL_QUEUE     ..YES..
...

I wonder if this is a whim of Oracle XE for linux only or if it is some "backward compatibility" feature that occurs also on other versions.

It's not a big deal, but it's something odd enough to cause a trivial query with a WHERE clause on these columns to fail until you realize that Oracle is returning such unusual values for some reason.

ORA-00942 when querying USER_QUEUE_SUBSCRIBERS

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

I'm not going to make this longer than necessary, for the simple reason that i could not find a way to scientifically re-create the conditions that led to this error, although i managed to see it twice.
This happened on Oracle XE running on Xubuntu linux.

select * from USER_QUEUE_SUBSCRIBERS;

ORA-00942: table or view does not exist
ORA-06512: at "SYS.AQ$_GET_SUBSCRIBERS", line 54

Basically it means that you managed to corrupt the DBMS_AQ environment.
Interestingly, in order to do so, you are not required to perform any fancy operations, it could be enough to drop some definition, sometime, somehow.

The first time i saw this message, i attempted to find out which table or view was missing, but after half an hour spent rummaging in the oracle dictionary i gave up and restored the db from a backup. As the DBMS_AQ environment is made up of several tables that are created and deleted on the fly when you run procedures like DBMS_AQADM.CREATE_QUEUE_TABLE and so forth, it's very likely that something went awry when i dropped a queue before dropping the subscribers or something like that.

A few days later i corrupted it a second time and i noticed that if i dropped and recreated all the queue related objects again, the error disappeared.

Interestingly enough, it seems that the other view ALL_QUEUE_SUBSCRIBERS continues to work without problems even when USER_QUEUE_SUBSCRIBERS raises an exception.
Don't ask me why.

See message translations for ORA-00942 and search additional resources.

The strange case of DBMS_AQ.DEQUEUE_ARRAY returning zero messages

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

After a fortnight spent working with DBMS_AQ and DBMS_AQADM packages, i'd be ready to feed oraclequirks with interesting situations for days, if not weeks if only i had enough time to do so.
Unfortunately i am rather busy sorting out some important stuff before leaving or OOW 2009, so i decided to pick one of my favorite AQ (Advanced Queuing) quirks concerning bulk dequeuing of messages, via DBMS_AQ.DEQUEUE_ARRAY in Oracle 10gR2 (XE on Xubuntu linux), as this situation may easily drive you mad.

create or replace
procedure MESSAGE_READER as
dequeue_options        dbms_aq.dequeue_options_t;
msg_prop_in_array      dbms_aq.message_properties_array_t;
msgid_in_array         dbms_aq.msgid_array_t;
type payload_array  is varray(30) of payload_type;
msg_in_array           payload_array;
l_msgs_in              pls_integer;
begin
msg_prop_in_array  := dbms_aq.message_properties_array_t();
msg_in_array       := payload_array();
msgid_in_array     := dbms_aq.msgid_array_t();

dequeue_options.navigation    := DBMS_AQ.FIRST_MESSAGE;
dequeue_options.consumer_name := 'MESSAGE_READER';
dequeue_options.wait          := DBMS_AQ.NO_WAIT;

l_msgs_in := dbms_aq.dequeue_array(
queue_name                 =>     'INBOX',
dequeue_options            =>     dequeue_options,
array_size                 =>     30,
message_properties_array   =>     msg_prop_in_array,
payload_array              =>     msg_in_array,
msgid_array                =>     msgid_in_array);

...
end;

The most interesting property of this procedure is that it runs without raising any errors, but simply doesn't dequeue any messages from the queue.
So, you see you queue full of messages in READY state, but the procedure doesn't pull out any data.
The value returned by DBMS_AQ.DEQUEUE_ARRAY function and stored into variable l_msgs_in remains zero and the collection remains empty, however no exceptions are raised.

While i felt that something weird must be going on behind the scenes, i did not figure out the solution until i read this thread on OTN.
As soon as i declared the type as a stand-alone SQL type, in contrast with the PL/SQL declaration above (in red color), the procedure started working properly.

create type type payload_array as varray(30) of payload_type;

create or replace
procedure MESSAGE_READER as
dequeue_options        dbms_aq.dequeue_options_t;
msg_prop_in_array      dbms_aq.message_properties_array_t;
msgid_in_array         dbms_aq.msgid_array_t;
msg_in_array           payload_array;
l_msgs_in              pls_integer;
begin
msg_prop_in_array  := dbms_aq.message_properties_array_t();
msg_in_array       := payload_array();
msgid_in_array     := dbms_aq.msgid_array_t();

dequeue_options.navigation    := DBMS_AQ.FIRST_MESSAGE;
dequeue_options.consumer_name := 'MESSAGE_READER';
dequeue_options.wait          := DBMS_AQ.NO_WAIT;

l_msgs_in := dbms_aq.dequeue_array(
queue_name                 =>     'INBOX',
dequeue_options            =>     dequeue_options,
array_size                 =>     30,
message_properties_array   =>     msg_prop_in_array,
payload_array              =>     msg_in_array,
msgid_array                =>     msgid_in_array);

...
end;

Don't ask me why.
I ignore if this problem or bug, whatever we wanna call it, has been fixed in subsequent releases and/or it affects all platforms.