enqueue clob

From: prunoki <hegyvari_at_ardents.hu>
Date: 14 Jul 2006 03:58:54 -0700
Message-ID: <1152874734.111082.67190@m73g2000cwd.googlegroups.com>

Hello,

I try to enqueue a clob into a queue (Oracle 8174), but I get an ORA-22275 when

dbms_lob.write (c_loc, length(p_msg), 1, p_msg);

is executed.

Any ideas how to do this? I tried several things to no avail. I think the problem is that user_data.payload is never initialized to an empty_clob. I tried to do

• set the clob locator of the payload before submitting to empty_clob
• dbms_lob.createtemporary(ms.payload) before submitting
• update the clob locator in the queue table to empty_clob() after submitting

Thanks,

Hegyvari Krisztian

CREATE OR REPLACE procedure enqueue_msg( p_queue in varchar2,p_msg in varchar2 )
as

 enqueue_options dbms_aq.enqueue_options_t;
 message_properties dbms_aq.message_properties_t;
 message_handle RAW(16);

 ms aq_clob_type;
 c_loc clob;
BEGIN    dbms_aq.enqueue(queue_name => p_queue,

                   enqueue_options => enqueue_options,
                   message_properties => message_properties,
                   payload => ms,
                   msgid => message_handle);

   select t.user_data.payload into c_loc from MERKINT_CLOB_QUEUE_TABLE t

                     where msgid=message_handle for update;

   dbms_lob.write (c_loc, length(p_msg), 1, p_msg);       

end;
/ Received on Fri Jul 14 2006 - 05:58:54 CDT