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;
I am trying to communicate with Tibco which is built on native JMS. I was frustrated with the lack of documentation. I resorted to reading JMS messages from Tibco and feeding back User Property parameters until we got it working. Does anyone know the significance of the 2nd and 5th parameters in sys.aq$_jms_userproparray? Apparently they have to be set to 100 and 27 respectively, but I have no idea what their significance is.  I also get AQ$_JMS_USERPROPERTY(‘JMS_OracleTimestamp, 200, null, 1265911910083, 24) from Tibco, so these two parameters change with the parameter type. This might be Tibco-specific but my Tibco developers say they only provide the NAME/VALUE pair, no other parameters.
Thanks! This is very helpful.. only thing is do you know how to set JMS properties on the message – like set_long_property() for example?
I am very confused – why are the JMS objects different in XE and then why are they ‘wrapped’ ??? I can’t find any full documentation and I can’t see they methods on the objects in XE..
Hi Guido! I knew there had to be an easier way to accomplish this, thanks for sharing this!!
Hi Peter,
Thanks for your answer on the Oracle Forums (i had never checked back at that thread).
There is another alternative I found out that works for me by running the script “/rdbms/admin/prvtaqal.plb” as SYS.
king regards, Guido
Thanks for sharing this code! I did not obtain my code through decompilation, though. I put a message in the queue from Java, and then did “select user_data from my_queue_table”, which provides a pretty detailed view of the data structure. Then I wrote my PL/SQL to create the exact same data structure.
Here is a piece of our code – we use for queuing to XE
procedure enqueue_requested_document(p_id in number)
is
l_message_xml varchar2(500);
l_agent sys.aq$_agent := sys.aq$_agent(‘ ‘,null,0);
l_oracledeliverymode sys.aq$_jms_userproperty := sys.aq$_jms_userproperty(‘jms_oracledeliverymode’,100,’2′,null,27);
l_jms_properties sys.aq$_jms_userproparray := sys.aq$_jms_userproparray(l_oracledeliverymode);
l_header sys.aq$_jms_header := sys.aq$_jms_header(l_agent,null,’cxd’,null,null,null,l_jms_properties);
l_mes raw(16) ;
l_parameter_xml varchar2(1000);
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
….
begin
….
dbms_aq.enqueue (‘cxd_pdf_queue’,enqueue_options,message_properties,sys.aq$_jms_text_message(l_header,length(l_message_xml),l_message_xml,null),l_mes);
….
end enqueue_requested_document;
Looks like we’re using the same resource for this one… – decompiled AQJMS code 🙂