Home » SQL & PL/SQL » SQL & PL/SQL » dequeue_array not working with array_size > 1 [merged 2 by jd] (Oracle 11gR2)
icon5.gif  dequeue_array not working with array_size > 1 [merged 2 by jd] [message #570781] Thu, 15 November 2012 13:09 Go to next message
oraqqestions
Messages: 2
Registered: November 2012
Junior Member
Hey,

We need to dequeue in bulk and so we are using dbms_aq.dequeue_array. If i am dequeueing the messages by calling a standalone procedure having dequeue_array, its working perfectly. The moment i register it for callback (automatic dequeue) and i increase the size of the array to > 1, then the messages arent getting dequeued.

I have attached the scripts:
"working_standalone" in this dequeue_array works perfectly.
"not_working_callback" in this the dequeue_array isnt working.

One article says that the dequeue_array works only for array_size one if its a buffered queue. But i checked that my queues are Persistent queues.

Has anyone faced similar issues? Please advise.

Thanks.
Re: dequeue_array not working with array_size > 1 [message #570783 is a reply to message #570781] Thu, 15 November 2012 13:19 Go to previous messageGo to next message
BlackSwan
Messages: 23162
Registered: January 2009
Senior Member
*********************************************
*********************************************
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;
/


Re: dequeue_array not working with array_size > 1 [merged 2 by jd] [message #570784 is a reply to message #570781] Thu, 15 November 2012 13:55 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
WHEN OTHERS

Regards
Michel
Re: dequeue_array not working with array_size > 1 [merged 2 by jd] [message #570854 is a reply to message #570784] Fri, 16 November 2012 09:12 Go to previous message
oraqqestions
Messages: 2
Registered: November 2012
Junior Member
Hi Michael,

Once i run the Enqueue Procedure, the dbms_scheduler is triggered and it calls the dequeue procedure (NOT WORKING CALLBACK PROCEDURE in the attachment). But this does not dequeue not does it go to the "When Others" part or any other Exception part. It just hangs (probably keeps waiting)..

Previous Topic: explose rows
Next Topic: How to pass parameter in a View?
Goto Forum:
  


Current Time: Mon Dec 22 21:29:32 CST 2014

Total time taken to generate the page: 0.08743 seconds