Given:
create TYPE things IS VARRAY(1) OF THING_TYP NOT NULL;
and an Oracle AQ queue table, and a queue FOO_BAR, calling DBMS_AQ.ENQUEUE_ARRAY using a JDBC CallableStatement results in an ArrayIndexOutOfBoundsException deep inside the proprietary Oracle code inside the ojdbc.jar. The next query results in a protocol error - it seems that the previous error caused the protocol reading code in the driver to get out of sync, because it didn't complete.
However, this type of call initially did work in SQL Developer! So maybe the queue or the queue table has got into a bad state?
A simple, unparameterised example of the PL/SQL follows. Running this - which is the same call that initially worked - also seems to produce the same symptoms in SQL Developer. Although SQL Developer doesn't show the ArrayIndexOutOfBoundsException to the user - it just doesn't show any output - there is a fragment of the exception stack trace on the Logging Page, and it does show the subsequent protocol error when I try to refresh the list of queues in the tree view on the left.
DECLARE
enqueue_options DBMS_AQ.enqueue_options_t;
msg_prop_array DBMS_AQ.message_properties_array_t;
msg_prop DBMS_AQ.message_properties_t;
payload_array things := things(THING_TYP(IDENTIFIERVALUE => 'abc'));
msgid_array DBMS_AQ.msgid_array_t;
retval PLS_INTEGER;
BEGIN
msg_prop_array := DBMS_AQ.message_properties_array_t(msg_prop);
retval := DBMS_AQ.ENQUEUE_ARRAY(
queue_name => 'FOO_BAR',
enqueue_options => enqueue_options,
array_size => 1,
message_properties_array => msg_prop_array,
payload_array => payload_array,
msgid_array => msgid_array);
END;
How could I attempt to diagnose what might be wrong with the queue?
(A possible clue is that I created and deleted some temporary VARRAY types previously in previous attempts to call this same stored procedure - prompted by an error message stating that the size of the message array must be the same as the size of the message properties array. Given that my arrays were going to be of different sizes, I decided to create temporary VARRAY types each time and delete them after loading the data. But this might be irrelevant, because the VARRAYs should only be used as temporary types in the stored procedure to transfer data into the queue - they shouldn't be used as part of the queue or the queue table itself.)
UPDATE: Looks like it didn't initially work, it just seemed like it did. Creating a new queue table and a new queue inside that queue table, and then running that PL/SQL against the fresh queue, results in the same problem - no output, and then a protocol error when I try to refresh the list of queues.
The queue was a multi-consumer queue, and somehow I had failed to set up the subscription - and then I forgot to set up the subscription again when I created a new queue to see if I could reproduce the error.
Setting up the subscription on the new queue made the PL/SQL work:
PL/SQL procedure successfully completed.