Hi all,
Does anyone have any experience with enqueue and dequeue of BLOB messages? I have PDF files that I would like to send via aq. I can enqueue them, but not dequeue them. When I attempt to
deque, I get the following error:
ORA-25236: buffer too small for user data
Here is my enqueue code:
set serveroutput on size 1000000
set timing on
/* Enqueue to msg_queue: */
DECLARE
Enqueue_options DBMS_AQ.enqueue_options_t;
Message_properties DBMS_AQ.message_properties_t;
Message_handle RAW(16);
Message blob;
TmpMsg blob := empty_blob;
cnt number;
BEGIN
DBMS_AQ.ENQUEUE(queue_name => 'JOOSTENBERGVLAKTE',
Enqueue_options => enqueue_options,
Message_properties => message_properties,
Payload => tmpmsg,
Msgid => message_handle);
select t.user_data into message from test_queue t where t.msgid = message_handle;
select blob_content into tmpmsg from tbl;
cnt := length(message) + 1;
dbms_lob.erase(message, cnt);
dbms_lob.trim(message, 0);
dbms_lob.append(message, tmpmsg);
dbms_output.put_line('Erased ' || cnt || ' characters');
dbms_output.put_line('Message length ' || length(message));
COMMIT;
END;
/
And here is my deque code:
set serveroutput on 1000000;
declare
l_options dbms_aq.dequeue_options_t;
l_properties dbms_aq.message_properties_t;
l_hnd RAW(16);
l_msg blob := empty_blob;
begin
dbms_aq.dequeue(
queue_name => 'JOOSTENBERGVLAKTE',
dequeue_options => l_options,
message_properties => l_properties,
payload => l_msg,
msgid => l_hnd
);
commit;
exception
when others then
rollback;
raise;
end;
/