Enqueuing AQ JMS Text Message from PL/SQL on Oracle XE americas cup win 2682133k1

Enqueuing AQ JMS Text Message from PL/SQL on Oracle XE

Today I was configuring a message driven bean to listen to an AQ queue. The tutorials and examples I found had me create the queue like this:

dbms_aqadm.create_queue_table( queue_table=>'MY_QUEUE_TABLE'
                             , queue_payload_type=>'SYS.AQ$_JMS_TEXT_MESSAGE'
                             , multiple_consumers=>false);

With this queue type, I managed to configure the Message Driven Bean successfully on an OC4J 10.1.2 without needing to use the JCA AQ Adapter, i.e. with just plain JMS. I also wrote a Java class to enqueue a test JMS message on this queue. But then, I needed to write PL/SQL code to put a message on this queue as well. Not being very fluent in PL/SQL programming, I tried googling for an example, but all the examples I found used code like this:

create or replace procedure testmessage AS
  msg SYS.AQ$_JMS_TEXT_MESSAGE;
  queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
  msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
  msg_id RAW(16);
begin
  msg := SYS.AQ$_JMS_TEXT_MESSAGE.CONSTRUCT();
  msg.set_text('Hello World from PL/SQL');
  DBMS_AQ.ENQUEUE( queue_name => 'MyQueue'
                 , enqueue_options => queue_options
                 , message_properties => msg_props
                 , payload => msg
                 , msgid => msg_id);
end;

Unfortunately, this code does not compile on the Oracle XE database that I was using, resulting in a “PLS-00302: component ‘CONSTRUCT’ must be declared” error. Further examination taught me that the JMS types on the XE database are “crippled’ because there is no JVM present. The advice I repeatedly came across was to use a queue with an ADT payload rather than a JMS type payload, at the cost of more complex dequeuing logic in the middle tier.

 

But I knew the JMS-type queue was working fine when enqueuing from Java, so I kept trying until I came up with some PL/SQL that enqueued the exact same messages as the ones I created from Java. Maybe (hopefully)  this is not the easiest way to do it but I found several (unanswered) forum posts from people struggling with the very same problem, so at least this is _a_ solution. I welcome suggestions and improvements!

create or replace procedure testmessage AS
  msg SYS.AQ$_JMS_TEXT_MESSAGE;
  msg_hdr SYS.AQ$_JMS_HEADER;
  msg_agent SYS.AQ$_AGENT;
  msg_proparray SYS.AQ$_JMS_USERPROPARRAY;
  msg_property SYS.AQ$_JMS_USERPROPERTY;
  queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
  msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
  msg_id RAW(16);
  dummy VARCHAR2(4000);
begin
  msg_agent := SYS.AQ$_AGENT(' ', null, 0);
  msg_proparray := SYS.AQ$_JMS_USERPROPARRAY();
  msg_proparray.EXTEND(1);
  msg_property := SYS.AQ$_JMS_USERPROPERTY('JMS_OracleDeliveryMode', 100, '2', NULL, 27);
  msg_proparray(1) := msg_property;

  msg_hdr := SYS.AQ$_JMS_HEADER(msg_agent,null,'<USERNAME>',null,null,null,msg_proparray);
  msg := SYS.AQ$_JMS_TEXT_MESSAGE(msg_hdr,null,null,null);
  msg.text_vc := 'Hello from PL/SQL on XE';
  msg.text_len := length(msg.text_vc);
  DBMS_AQ.ENQUEUE( queue_name => 'MyQueue'
                 , enqueue_options => queue_options
                 , message_properties => msg_props
                 , payload => msg
                 , msgid => msg_id);
end;


															

6 Comments

  1. Craig Anderson March 4, 2010
  2. Drew December 10, 2008
  3. Peter Ebell February 5, 2008
  4. Guido February 4, 2008
  5. Peter Ebell August 31, 2007
  6. Jan August 30, 2007