Re: enqueue clob

From: prunoki <hegyvari_at_ardents.hu>
Date: 14 Jul 2006 04:02:11 -0700
Message-ID: <1152874931.525922.63030@h48g2000cwc.googlegroups.com>

prunoki wrote:
> 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;
> /

Never mind. The following did the trick:

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);

   update merkint_clob_queue_table t set t.user_data=aq_clob_type(empty_clob()) where

                     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 - 06:02:11 CDT