DECLARE v_dequeue_options dbms_aq.dequeue_options_t ; nt_msg_props dbms_aq.message_properties_array_t := dbms_aq.message_properties_array_t(); nt_payloads ETF_CA_ANNOUNCEMENT_REQ_Tab := ETF_CA_ANNOUNCEMENT_REQ_Tab() ; nt_msg_ids dbms_aq.msgid_array_t ; retval pls_integer ; cnt PLS_INTEGER := 16; x_timeout EXCEPTION; no_messages EXCEPTION; cur_error EXCEPTION; PRAGMA EXCEPTION_INIT(x_timeout, -25228); PRAGMA EXCEPTION_INIT(no_messages, -25235); PRAGMA EXCEPTION_INIT(cur_error, -01002); BEGIN v_dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE_MULTI_GROUP; nt_payloads.extend(cnt); nt_msg_props.extend(cnt); --loop begin retval := dbms_aq.dequeue_array( queue_name => 'ETF_CA_ANNCMNT_REQ_Q', dequeue_options => v_dequeue_options , array_size => cnt, message_properties_array => nt_msg_props, payload_array => nt_payloads, msgid_array => nt_msg_ids ) ; DBMS_OUTPUT.PUT_LINE('Got ' || TO_CHAR(retval) || ' entries'); insert into ESTAR.ESTAR_ETF_REQ_CORP_ACTION select REQUEST_ID, BATCH_ID, SECURITY_ALIAS, EFFECTIVE_DATE, MINIMUM_DATE, MAXIMUM_DATE, REQUEST_TIME, STATUS, LAST_UPDATE_USER, LAST_UPDATE_TIME, 1, 123 FROM TABLE(nt_payloads) nt; DBMS_OUTPUT.PUT_LINE(sql%rowcount); commit; EXCEPTION /* Handle exit scenario two from the notes above... */ WHEN x_timeout THEN DBMS_OUTPUT.PUT_LINE( 'No more messages to dequeue.'); -- EXIT; when no_messages THEN DBMS_OUTPUT.PUT_LINE ('Finished processing a group of messages'); v_dequeue_options.navigation := DBMS_AQ.NEXT_MESSAGE_ONE_GROUP; when cur_error THEN DBMS_OUTPUT.PUT_LINE ('fetch out of sequence'); when others then p_debug_code(sqlcode||sqlerrm); end; END; / ***************** ************** working standalone with a loop ************* ********** DECLARE v_dequeue_options dbms_aq.dequeue_options_t ; nt_msg_props dbms_aq.message_properties_array_t := dbms_aq.message_properties_array_t(); nt_payloads ETF_CA_ANNOUNCEMENT_REQ_Tab := ETF_CA_ANNOUNCEMENT_REQ_Tab() ; nt_msg_ids dbms_aq.msgid_array_t ; retval pls_integer ; cnt PLS_INTEGER := 100; x_timeout EXCEPTION; no_messages EXCEPTION; cur_error EXCEPTION; PRAGMA EXCEPTION_INIT(x_timeout, -25228); PRAGMA EXCEPTION_INIT(no_messages, -25235); PRAGMA EXCEPTION_INIT(cur_error, -01002); v_continue boolean := TRUE; BEGIN v_dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE_MULTI_GROUP; nt_payloads.extend(cnt); nt_msg_props.extend(cnt); while (v_continue) loop begin retval := dbms_aq.dequeue_array( queue_name => 'ETF_CA_ANNCMNT_REQ_Q', dequeue_options => v_dequeue_options , array_size => cnt, message_properties_array => nt_msg_props, payload_array => nt_payloads, msgid_array => nt_msg_ids ) ; DBMS_OUTPUT.PUT_LINE('Got ' || TO_CHAR(retval) || ' entries'); if (retval = 0) then DBMS_OUTPUT.PUT_LINE('its 0'); end if; insert into ESTAR.ESTAR_ETF_REQ_CORP_ACTION select REQUEST_ID, BATCH_ID, SECURITY_ALIAS, EFFECTIVE_DATE, MINIMUM_DATE, MAXIMUM_DATE, REQUEST_TIME, STATUS, LAST_UPDATE_USER, LAST_UPDATE_TIME, 1, 123 FROM TABLE(nt_payloads) nt; DBMS_OUTPUT.PUT_LINE(sql%rowcount); v_continue := (retval = cnt); commit; EXCEPTION /* Handle exit scenario two from the notes above... */ WHEN x_timeout THEN DBMS_OUTPUT.PUT_LINE( 'No more messages to dequeue.'); -- EXIT; when no_messages THEN DBMS_OUTPUT.PUT_LINE ('Finished processing a group of messages'); v_dequeue_options.navigation := DBMS_AQ.NEXT_MESSAGE_ONE_GROUP; when cur_error THEN DBMS_OUTPUT.PUT_LINE ('fetch out of sequence'); when others then p_debug_code(sqlcode||sqlerrm); end; end loop; END; /