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;