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?