Home » Server Options » Streams & AQ » DBMS_AQ.dequeue_array always returns 0 messages (Oracle, 10g, HP-UX)
icon5.gif  DBMS_AQ.dequeue_array always returns 0 messages [message #289627] Mon, 24 December 2007 08:42 Go to next message
pintovit
Messages: 2
Registered: December 2007
Junior Member
Hi all,

first of all, thanks for your time reading this...

I have to dequeue messages from a Oracle queue and for performance reasons, I must dequeue batches of messages and not one by one.

I have a logmessage object that it is what is queued:
CREATE OR REPLACE
type logmessage as object (
wpTRID number(9),
wpRuleID number(9),
wpCompType Number(4),
wpComponent varchar2(40),
wpEvent varchar2(80),
wpStartTimestamp timestamp,
wpEndTimestamp timestamp,
wpSuccess varchar2(20)
)
/

The queue is created with the following statement:
DBMS_AQADM.CREATE_QUEUE (
queue_name => 'Log_Queue',
queue_table => 'Log_Table',
queue_type => DBMS_AQADM.NORMAL_QUEUE,
max_retries => 3,
comment => 'Log Queue',
auto_commit => FALSE);

The messages are enqueued with this function:

PROCEDURE LOGENQUEUE(wpTRID in number, wpRuleID in number, wpCompType in number, wpComponent in varchar2, wpEvent in varchar2, wpSuccess in varchar2, wpStartTimestamp in timestamp, wpEndTimestamp in timestamp)
IS

queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
message_prop DBMS_AQ.MESSAGE_PROPERTIES_T;
message_id RAW(16);
my_msg LogMessage;
seq Number(9);

BEGIN
IF(wpTRID=-1)
THEN
select wp_queue_seq.nextval into seq from dual;
my_msg:=LogMessage(seq,wpRuleID,wpCompType,wpComponent,wpEvent, wpStartTimestamp, wpEndTimestamp, wpSuccess);
message_prop.correlation:=seq;
ELSE
my_msg:=LogMessage(wpTRID,wpRuleID,wpCompType,wpComponent,wpEvent, wpStartTimestamp, wpEndTimestamp, wpSuccess);
message_prop.correlation:=wpTRID;
END IF;

message_prop.exception_queue:='Log_ExcepQueue';

DBMS_AQ.ENQUEUE( queue_name => 'Log_Queue', enqueue_options => queue_options, message_properties => message_prop, payload => my_msg, msgid => message_id);

END LOGENQUEUE;


Now, this dequeue procedure works (but it gets one message at a time, so no good):

PROCEDURE DEQUEUE_LOG_TABLE_2
IS
options DBMS_AQ.dequeue_options_t;
properties DBMS_AQ.message_properties_t;
v_message_handle RAW(16);
log_message_a logmessage;

TYPE log_dump_a_t IS TABLE OF log_dump%ROWTYPE
INDEX BY BINARY_INTEGER;

log_dump_a log_dump_a_t;
total NUMBER := 0;
n NUMBER;
BEGIN
options.navigation := DBMS_AQ.first_message;
options.WAIT := DBMS_AQ.no_wait;

FOR rec IN (SELECT wpi_id
FROM jm_wp_info_history jm, log_table l
WHERE jm.wpi_state = 'DONE'
AND jm.wpi_id = l.user_data.wptrid
AND ROWNUM < 2
GROUP BY jm.wpi_id) LOOP
total := total + 1;
options.correlation := rec.wpi_id;

SELECT COUNT(1)
INTO n
FROM log_table
WHERE corrid = rec.wpi_id;

FOR i IN 1 .. n LOOP
DBMS_AQ.dequeue(queue_name => 'Log_Queue',
dequeue_options => options,
message_properties => properties,
payload => log_message_a,
msgid => v_message_handle
);
log_dump_a(NVL(log_dump_a.LAST + 1, 1)).wptrid := log_message_a.wptrid;
log_dump_a(log_dump_a.LAST).wpruleid := log_message_a.wpruleid;
log_dump_a(log_dump_a.LAST).wpcomptype := log_message_a.wpcomptype;
log_dump_a(log_dump_a.LAST).wpcomponent := log_message_a.wpcomponent;
log_dump_a(log_dump_a.LAST).wpevent := log_message_a.wpevent;
log_dump_a(log_dump_a.LAST).wpstarttimestamp := log_message_a.wpstarttimestamp;
log_dump_a(log_dump_a.LAST).wpendtimestamp := log_message_a.wpendtimestamp;
log_dump_a(log_dump_a.LAST).wpsuccess := log_message_a.wpsuccess;
END LOOP;


IF ( log_dump_a IS NOT NULL
AND log_dump_a.COUNT > 0) THEN
FORALL i IN log_dump_a.FIRST .. log_dump_a.LAST
INSERT INTO log_dump
VALUES log_dump_a(i);

log_dump_a.DELETE;
END IF;

COMMIT;
END LOOP;
END dequeue_log_table_2;

And this one doesn't (it does, doesn't return any exception, just doesn't dequeue any message):

PROCEDURE DEQUEUE_LOG_TABLE
IS
TYPE log_message_a_t IS TABLE OF logmessage;

payloadarr log_message_a_t := log_message_a_t();
deqopt DBMS_AQ.dequeue_options_t;
msgproparr DBMS_AQ.message_properties_array_t := DBMS_AQ.message_properties_array_t();
msgidarr DBMS_AQ.msgid_array_t := DBMS_AQ.msgid_array_t();
retval PLS_INTEGER;

TYPE log_dump_a_t IS TABLE OF log_dump%ROWTYPE
INDEX BY BINARY_INTEGER;

log_dump_a log_dump_a_t;
total NUMBER := 0;
BEGIN
payloadarr.EXTEND(100);
msgproparr.EXTEND(100);


deqopt.WAIT := DBMS_AQ.no_wait;
deqopt.navigation := DBMS_AQ.first_message_multi_group;

FOR rec IN (SELECT wpi_id
FROM jm_wp_info_history jm, log_table l
WHERE jm.wpi_state = 'DONE'
AND jm.wpi_id = l.user_data.wptrid
AND ROWNUM < 2
GROUP BY jm.wpi_id) LOOP
total := total + 1;
deqopt.correlation := TO_CHAR(rec.wpi_id);

payloadarr.DELETE;
msgidarr.DELETE;
msgproparr.DELETE;
retval :=
DBMS_AQ.dequeue_array(queue_name => 'Log_Queue',
dequeue_options => deqopt,
array_size => payloadarr.COUNT,
message_properties_array => msgproparr,
payload_array => payloadarr,
msgid_array => msgidarr
);

FOR i IN 1 .. retval LOOP
log_dump_a(NVL(log_dump_a.LAST + 1, 1)).wptrid := payloadarr(i).wptrid;
log_dump_a(log_dump_a.LAST).wpruleid := payloadarr(i).wpruleid;
log_dump_a(log_dump_a.LAST).wpcomptype := payloadarr(i).wpcomptype;
log_dump_a(log_dump_a.LAST).wpcomponent := payloadarr(i).wpcomponent;
log_dump_a(log_dump_a.LAST).wpevent := payloadarr(i).wpevent;
log_dump_a(log_dump_a.LAST).wpstarttimestamp := payloadarr(i).wpstarttimestamp;
log_dump_a(log_dump_a.LAST).wpendtimestamp := payloadarr(i).wpendtimestamp;
log_dump_a(log_dump_a.LAST).wpsuccess := payloadarr(i).wpsuccess;
END LOOP;

IF ( log_dump_a IS NOT NULL
AND log_dump_a.COUNT > 0) THEN
FORALL i IN log_dump_a.FIRST .. log_dump_a.LAST
INSERT INTO log_dump
VALUES log_dump_a(i);

log_dump_a.DELETE;
END IF;

COMMIT;
END LOOP;

END dequeue_log_table;

And I assure the queue has plenty of messages at the moment!
SELECT state, COUNT(1)
FROM log_table
GROUP BY state;

State count(1)
0 1218578

I already tried everything I could think off... From different (ALL) options.navigation settings available to removing the correlation use, etc, etc... I simply don't know what else to try.

Can anyone share some insight on this?!

Sorry for the long post. Better have all the information to start with, I guess.

Thanks a lot!

Best regards,
Vítor Pinto

[Mod-edit: Frank added code-tags]

[Updated on: Mon, 24 December 2007 09:09] by Moderator

Report message to a moderator

Re: DBMS_AQ.dequeue_array always returns 0 messages [message #291174 is a reply to message #289627] Thu, 03 January 2008 04:51 Go to previous messageGo to next message
pintovit
Messages: 2
Registered: December 2007
Junior Member
Meanwhile, I got this reply on another forum that solved my problem.

I'll post it here just for future reference:

"don't define log_message_a_t in proc. Create an explicit type on SQL prompt and see it will work..

CREATE OR REPLACE TYPE log_message_a_t AS TABLE OF <schema_name>.logmessage; "

Thanks anyway to everyone that spent his time looking at this!

Best regards,
Vítor
Re: DBMS_AQ.dequeue_array always returns 0 messages [message #291183 is a reply to message #291174] Thu, 03 January 2008 05:23 Go to previous message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: Streams Synchronization
Next Topic: Delete queue_table
Goto Forum:
  


Current Time: Fri Aug 29 21:25:57 CDT 2014

Total time taken to generate the page: 0.11215 seconds