Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Question Unanswered: Dequeue BLOB from Ora AQ

    Hi all,

    Does anyone know how to dequeue a BLOB from a queue table using PL/SQL?
    I keep getting the following error:
    ORA-25236: buffer too small for user data

    Here's my dequeue code:
    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;
    /

  2. #2
    Join Date
    Jan 2004
    Location
    Palm Harbor, FL
    Posts
    5

    BLOB Requires LOB Locator

    Your payload should be an aq.message type. Inside the type, you will have a lob locator. After extracting the lob locator, you will be able to populate the blob with dbms_lob.read.

    Lobs are somewhat of a pain in AQ. I prefer XML or LCR formats although I relize those are not always appropriate.

    LewisC

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •