*********************************************
*********************************************
WORKING STANDALONE WITHOUT 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 := 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;
/
*****************************
*******************************************
NOT WORKING CALLBACK PROCEDURE
*******************************************
*******************************************
CREATE OR REPLACE PROCEDURE ESTAR.P_GOM_CORP_ACTION_DEQUEUE(ontext  raw,
                                                  reginfo  sys.aq$_reg_info,
                                                  descr    sys.aq$_descriptor,
                                                  payload  varchar2,
                                                  payloadl number)
as
v_dequeue_options dbms_aq.dequeue_options_t ;
nt_msg_props dbms_aq.message_properties_array_t;
nt_payloads 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);
  v_continue boolean := TRUE;
BEGIN
p_debug_code('Inside the dequeue procedure');

   v_dequeue_options.msgid         := descr.msg_id;
   v_dequeue_options.consumer_name := descr.consumer_name;

nt_msg_props := dbms_aq.message_properties_array_t();
nt_msg_props.extend(cnt);

nt_payloads := ETF_CA_ANNOUNCEMENT_REQ_Tab() ;
nt_payloads.extend(cnt);

v_dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE_MULTI_GROUP;


while (v_continue) loop
begin
retval := dbms_aq.dequeue_array( queue_name => descr.queue_name,
dequeue_options => v_dequeue_options ,
array_size => cnt,
message_properties_array => nt_msg_props,
payload_array => nt_payloads,
msgid_array => nt_msg_ids ) ;
p_debug_code('Got ' || TO_CHAR(retval) || ' entries');

if (retval = 0)
then
        p_debug_code('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;     
p_debug_code(sql%rowcount);
v_continue := (retval = cnt);

commit;
  EXCEPTION
                  
                          /* Handle exit scenario two from the notes above... */
WHEN x_timeout THEN
                              p_debug_code( 'No more messages to dequeue.');
                 --             EXIT;
 when no_messages THEN
                         p_debug_code('Finished processing a group of messages');
                               v_dequeue_options.navigation := DBMS_AQ.NEXT_MESSAGE_ONE_GROUP;
 when cur_error THEN
                         p_debug_code('fetch out of sequence');

 when others then
                         p_debug_code(sqlcode||sqlerrm);
end;
end loop;
END;
/