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;