I'm trying to enqueue a message and keep getting this.
{"ORA-21525: attribute number or (collection element at index) %s violated its constraints"}
I found that it was a problem with the commodity1/2. Even though I specified no more than 2 characters it still through the error. When I changed the type to varchar2(20) it worked fine. Seems like your code is sending the wrong number of characters.
CREATE OR REPLACE TYPE ohlc_msg_type AS OBJECT (
sessionDt date,
contract varchar2(20),
xml_qry varchar2(20),
exchCode varchar2(10),
commodity1 varchar2(5),
commodity2 varchar2(5),
year1 number,
year2 number,
month1 number,
month2 number
);
/
execute DBMS_AQADM.create_queue_table( queue_table => 'ohlc_queue_tab', queue_payload_type => 'ohlc_msg_type', multiple_consumers=>true );
execute DBMS_AQADM.create_queue( queue_name => 'ohlc_queue', queue_table => 'ohlc_queue_tab');
execute DBMS_AQADM.start_queue( queue_name => 'ohlc_queue', enqueue => TRUE);
declare
subsc_t sys.aq$_agent;
begin
subsc_t := sys.aq$_agent('ohlc_listener', 'ohlc_queue', 0);
dbms_aqadm.add_subscriber(queue_name => 'ohlc_queue', subscriber => subsc_t);
end;
/