Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

enqueue an array of objects

Doug ChaseJan 30 2025 — edited Jan 30 2025

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!

This post has been answered by Solomon Yakobson on Jan 30 2025
Jump to Answer

Comments

chonewell Nov 12 2024

My Oracle Cloud tenant, cloud account, and secure email have no issues. Why haven't I received my password reset email for Oracle Cloud? This is very strange, and our attempts have not been able to solve the problem. May I ask who I should turn to for help?

L. Fernigrini Nov 12 2024

If your account is a paid one, open a Support ticket.

If it is a Free Tier then you will have to rely on help from the community. Most probable cause that you did not receive the password reset email is that your account has been stolen and the email has been changed.

chonewell Nov 13 2024

Thank you for your reply!
But when I chatted with the online customer service, they told me that my Oracle Cloud tenant, account, and email were all fine. So, there shouldn't be a problem of theft.
I have a free account, but who can I contact on the forum? I can only post, but no one on the forum can view my account permissions, right. I am currently trying to reset MFA, I don't know if it works.
It's quite ridiculous that I have a free account and can't enjoy any services, but how can I become a paid user if I can't log in to my account.

1 - 3

Post Details

Added on Jan 30 2025
2 comments
100 views