tags:

views:

296

answers:

0

I have problem with sending long messages to Oracle JMS. My queue was created with:

dbms_aqadm.CREATE_queue_table(
  queue_table => 'QUEUE_TABLE_TEST',
  queue_payload_type => 'SYS.AQ$_JMS_MESSAGE');

sys.dbms_aqadm.create_queue(
  queue_name     => 'QTEST_OUT'
, queue_table    => 'QUEUE_TABLE_TEST'
, queue_type     => sys.dbms_aqadm.NORMAL_QUEUE
, max_retries    => '2147483647'
, retry_delay    => '0'
, retention_time => '0'
, comment        => 'Test queue');

I created function to send message:

create or replace function gc_aq_test(msg_txt in clob) return integer
as
  message SYS.AQ$_JMS_MESSAGE;
  enqueue_options dbms_aq.enqueue_options_t;
  message_properties dbms_aq.message_properties_t;
  msgid raw(16);
begin
  message := SYS.AQ$_JMS_MESSAGE.CONSTRUCT(DBMS_AQ.JMS_TEXT_MESSAGE);
  message.set_string_property('FROM', 'TEST');
  message.set_text(xmltype(msg_txt).getclobval());
  dbms_aq.enqueue(queue_name => 'QTEST_OUT',
      enqueue_options => enqueue_options,
      message_properties => message_properties,
      payload => message,
      msgid => msgid);
 return 0;
end;

It works if msg_txt is smaller than 4 kb. For longer texts Oracle reports:

ORA-01461: can bind a LONG value only for insert into a LONG column

I checked that my client (JDBC) can insert CLOB value into the database (I filled test table with CLOB column).

I work with Oracle9i Enterprise Edition Release 9.2.0.1.0

What should I do to create message with long text?