Hi, I am trying to enqueue an array of 7 objects into a queue and what I see being enqueued is 7 individual messages versus (what I would expect) 1 message with 7 elements.
To be more specific, here is my configuration and what I'm enqueueing:
CREATE OR REPLACE TYPE SYNCTABLEDATA_OTYPE
IS OBJECT
(
NAME VARCHAR2(30),
VALUE VARCHAR2(32767)
);
CREATE TYPE SYNCTABLEDATA_ARR AS VARRAY(50) OF SYNCTABLEDATA_OTYPE;
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE(
Queue_table => 'AQ_SYNCTABLEDATA',
Queue_payload_type => 'SYNCTABLEDATA_OTYPE',
Sort_list => 'PRIORITY,ENQ_TIME');
END;
BEGIN
DBMS_AQADM.CREATE_QUEUE(
Queue_name => 'AQ_SYNCTABLEDATA',
Queue_table => ‘AQ_SYNCTABLEDATA’);
END;
DECLARE
enqueue_options DBMS_AQ.enqueue_options_t;
msg_prop_array DBMS_AQ.message_properties_array_t := dbms_aq.message_properties_array_t();
msg_prop DBMS_AQ.message_properties_t;
msgid_array DBMS_AQ.msgid_array_t;
retval PLS_INTEGER;
payload_array synctabledata_arr := synctabledata_arr(synctabledata_otype('OPERATION', 'INSERT')
,synctabledata_otype('TABLENAME', 'NEW_GB_SIMBOOK')
,synctabledata_otype('ACCTID', '79')
,synctabledata_otype('GRADEID', '576557')
,synctabledata_otype('LESSONTAKENID', '43787')
,synctabledata_otype('BASIS_ATTEMPT', '1')
,synctabledata_otype('BASIS_GRADE', '8')
);
BEGIN
for i in 1 .. payload_array.COUNT loop
msg_prop_array.EXTEND;
msg_prop_array(msg_prop_array.LAST) := msg_prop;
end loop;
retval := dbms_aq.enqueue_array(queue_name => 'AQ_SYNCTABLEDATA',
enqueue_options => enqueue_options,
array_size => payload_array.COUNT,
message_properties_array => msg_prop_array,
payload_array => payload_array,
msgid_array => msgid_array);
commit;
END;
/
What is enqueued is this:
USER_DATA(NAME, VALUE)
---------------------------------------------------------------------
SYNCTABLEDATA_OTYPE('OPERATION', 'INSERT')
SYNCTABLEDATA_OTYPE('TABLENAME', 'NEW_GB_SIMBOOK')
SYNCTABLEDATA_OTYPE('ACCTID', '79')
SYNCTABLEDATA_OTYPE('GRADEID', '576557')
SYNCTABLEDATA_OTYPE('LESSONTAKENID', '43787')
SYNCTABLEDATA_OTYPE('BASIS_ATTEMPT', '1')
SYNCTABLEDATA_OTYPE('BASIS_GRADE', '8')
What I'd like to see is 1 message:
SYNCTABLEDATA_ARR(SYNCTABLEDATA_OTYPE('OPERATION','INSERT'), SYNCTABLEDATA_OTYPE('TABLENAME','NEW_GB_SIMBOOK'), SYNCTABLEDATA_OTYPE('ACCTID','79'), SYNCTABLEDATA_OTYPE('GRADEID','576557'), SYNCTABLEDATA_OTYPE('LESSONTAKENID','43787'), SYNCTABLEDATA_OTYPE('BASIS_ATTEMPT','1'), SYNCTABLEDATA_OTYPE('BASIS_GRADE','8'))
I have tried to create the queue table with Queue_payload_type => ‘SYNCTABLEDATA_ARR’, but when I do I get inconsistent data types.
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE(
Queue_table => 'AQ_SYNCTABLEDATA',
Queue_payload_type => 'SYNCTABLEDATA_ARR',
Sort_list => 'PRIORITY,ENQ_TIME');
END;
ORA-25326: Array enqueue operation failed for message at index 1
ORA-00932: inconsistent datatypes: expected SYNCTABLEDATA_ARR got SYNCTABLEDATA_OTYPE
I know if I insert an array of objects into a table I see the expected outcome. I'm not clear why I'm not seeing the same when enqueueing an array of objects
Thanks!