Routing Oracle AQ messages using Apache Camel in ServiceMix: the XA option

I wrote in my last post about how to route messages between software based on Oracle Advanced Queuing (AQ) and Apache ActiveMQ, using Camel over ServiceMix. Today, I’d like to write about a different option than the ones I talked before: the XA option.

There are situations where, in case of any exception, you have to guarantee that in any way any message is lost and that messages are not received duplicated, these situations are where the X/Open XA standard comes into action.

I started my research on this issue by looking for documentation and examples. Two elements where very useful at this point: an Oracle White Paper about XA and Oracle controlled Distributed Transactions, that allowed me to review the XA topic, and a very good sample of a similar situation that shows how to use XA transactions with Camel across ActiveMQ and WebSphere MQ written by Torsten Mielke. You can find the result code of my XA test on my GitHub repository.

The test has a processor, copied from Torsten’s example, that simulates an exception and allows you to see how the exchange is recovered. But, the interesting issue here is trying to recover after an Oracle database crash or a network failure in the middle of a message exchange. I’ve made many test, for the first ones I used an Oracle 11.2 test database over a Microsoft Windows operative system and Apache ServiceMix 6.0, also over Windows. In this environment, I submitted shutdown abort and shutdown immediate commands to the database and the Geronimo recovery manager didn’t recovered the Oracle branches in prepared state, so I had to commit force the in-doubt transactions.

I wasn’t happy with the previous solution, which it is not recommended by Oracle (read the previously linked white paper) and it isn’t transparent for the user. My final environment wasn’t Windows bases, but Linux, so I ran other tests using another Oracle 11.2 database installed over Linux Red-Hat and Apache ServiceMix 6.0, also over Linux. In this case, the Geronimo recovery manager was capable of recovering prepared transactions and I couldn’t reproduce the error. An Oracle database configuration problem? an operative system issue? That’s I suppose.

Routing Oracle AQ messages using Apache Camel in ServiceMix

I’ve been working with Apache ServiceMix lately and I expect to carry on doing in next months. One of the issues I’ve faced is how to route messages between software based on Oracle Advanced Queuing (AQ) and other systems, using one of the main components of Apache ServiceMix: Camel. In this post, I’d like to start talking about the approach I’ve selected. I’m still working with XA, so this is just the beginning! The code of my proof of concept is on github.

The first topic I had to study was how to deploy the libraries needed to work with Oracle AQ: aqapi.jar and ojdbc6.jar I started embedding the jar files on the bundle, but this approach implies that the libraries have to be attached to each bundle, so I finally decided to create a feature that wraps the files and converts them to OSGI bundles. This is the first module of my code repository, called feature.

Then, I started to work in a basic connection, using the Camel JMS component, by injecting an Oracle AQ connection factory bean. You have my code on the second module of the project, called basic-test. But this solution is not scalable, because the component creates a JMS connection, which implies a JDBC one, each time a message is sent and this is an expensive process, so I investigate the use of connection pools.

The question I had to study at this point was if I just had to create a JDBC connection pool or a JMS one, I selected the second option because a JMS connection not only implies a JDBC connection, as I said before, but also other stuff of JMS api. A very, very basic benchmark showed me that my test environment (Apache Service Mix 6.0.0, Oracle 11.2.0 database on virtual machines) took 3 seconds in sending 100 messages on the performance test, versus 14 seconds on the basic one.

On the other hand, receiving messages test results weren’t so impressive, I tested competing consumers and asynchronous parameters (which cannot be uses in all use cases). My configuration is in the module performance-test of the github code repository, where you can play with the configuration parameters of the bundle and get your own conclusions.

Setting up a JMS bridge between Weblogic and ActiveMQ

Almost four years ago, I wrote about how to setup a JMS bridge between Weblogic and HornetQ. Lately, I’ve had to research how to do the same work with ActiveMQ. Here you have my findings. As it was for HornetQ, the first step was to copy the client libraries to a folder residing in a filesystem of my Weblogic server, in the case of ActiveMQ these files are:

  • activemq-client-5.10.0.jar
  • hawtbuf-1.10.jar
  • slf4j-api-1.7.5.jar

Later on, I set the PRE_CLASSPATH variable pointing it to these libraries into the script setDomainEnv. Creating messaging destinations is more or less the same job as it was for HornetQ, you just have to change the initial context factory and connection url parameters:

  <name>JMS Bridge Destination-Target</name>

But the former configuration is not enough, because ActiveMQ hasn’t his own JNDI provider (I had ActiveMQ as a JMS provider for a ServiceMix ESB) and requires a file with the mappings between physical destinations and tha jndi ones, but, how to configure the properties file in the context of a Weblogic messaging bridge? Here is the trick: create a JAR archive containing the file and put the JAR in the CLASSPATH (the same way at it was described before for activemq-client-5.10.0.jar). The steps to create the JAR archive are:

  • Create a file called with the following entries:

connectionFactoryNames=ConnectionFactory,XAConnectionFactory queue.TestQueue=TestQueue

  • Create the archive with the following command:

jar cvf jndi.jar

I found this trick in this post, after have tested many different configurations, including the setup of a Weblogic Foreign JMS Server, without success. Finally, I’d like to point out that I am not completely happy with this setup because it has an obvious drawback: you have modify and redeploy the jndi.jar archive every time you add a new queue or topic, so suggestions are welcome!!!


Oracle AQ: working with PL/SQL asynchronous notifications

I like Oracle Streams Advanced Queuing (AQ), it’s reliable and fast. I’ve been working with this technology for the last four years, with 10g and 11g database versions; most of the time, I’ve had to interact with Java EE systems, through Java Message Service (JMS), which it’s fully supported by Oracle AQ. JMS has Message Driven Beans (MDB) as the standard way to consume messages in Java EE, as its counter part on the Oracle database you can register asynchronous notifications to PL/SQL procedures. To be perfectly honest, I’ve always considered that the configuration of this functionality is a bit tricky, because sometimes you don’t get an error, it simply doesn’t work. That’s why I’m writing this post, to present a simple example of PLSQL asynchronous notifications that you can download from github.

The first point that I’d like to deal is the sign of the callback procedure, standalone or belonging to a package, that will consume the messages:

procedure receive_message_callback (
   context  raw,
   payload  raw,
   payloadl number

I think that the key here is the type of the argument payload: raw or varchar2, depending on the type of the message. I’ve prepared my sample with my personal Oracle Database Express Edition 11g Release 2, where I couldn’t work with Oracle AQ JMS types, so I’ve used a custom data type as the payload of the messages, which implies that the payload argument type has to be raw, but if you work, for example, with JMS Text Messages, SYS.AQ$_JMS_TEXT_MESSAGE in Oracle AQ, the payload argument type has to be varchar2.

The second issue is that the configuration needed varies depending if the destination is a queue or a topic.
In a queue each message is consumed by just one consumer, so you simply has to register the callback procedure. Here you have an excerpt of my code:

   dbms_aqadm.create_queue_table (queue_table => 'queues_qt',
                                  queue_payload_type => 'TESTAQ.MESSAGES_T');

   dbms_aqadm.create_queue (queue_name => 'test_queue',
                            queue_table => 'queues_qt');

   dbms_aqadm.start_queue (queue_name => 'test_queue');


The tip to remember here is not to forget the schema name prefix before queue and callback procedure names.

In a topic each message can be consumed by one or several subscriptors and each of them can process the message in a different way, for example sending an email, instead of processing by a PL/SQL procedure. So, you first have to register a subscriber, an agent in Oracle AQ terminology, and then register the PLSQL consumer. Here you have an excerpt of my code:

   -- It's a topic, so multiple_consumers parameter is specified.
   dbms_aqadm.create_queue_table (queue_table => 'topics_qt',
                                  queue_payload_type => 'TESTAQ.MESSAGES_T',
                                  multiple_consumers => true);

   dbms_aqadm.create_queue (queue_name => 'test_topic',
                            queue_table => 'topics_qt');

   dbms_aqadm.start_queue (queue_name => 'test_topic');

   dbms_aqadm.add_subscriber (queue_name => 'test_topic',
                              subscriber =>$_agent(
                                               name => 'demo_subscriber',
                                               address => null,
                                               protocol => 0));


The tip to remember here is not to forget to put the name of the subscriber after the name of the topic, when you’re registering the callback procedure.


Changing the datatype of a column from VARCHAR2 to CLOB

Some days ago, I realized I hadn’t estimated correctly the size of a column of a table that receives data from an external source. I defined a VARCHAR2(4000 CHAR) datatype but it came a longer message¬† (I got an ORA-01461 error), so I decided to move the column to a CLOB datatype.

At first sight, it seems a simple matter. For example, if you have the following table definition:

   create table test_messages
   (message_id number             primary key,
    content   varchar2(4000 char) not null);

You would think to issue the following statement:

   alter table test_messages
   modify (content clob not null);

But, if you do that, you get an ORA-22296 error.

I had several options to solve the problem (please review the reference of this post), but I finally selected the following one:

   alter table test_messages
   add (temp_content clob);

   update test_messages
   set temp_content = content;

   alter table test_messages
   drop column content;

   alter table test_messages
   rename column temp_content to content;

   alter table test_messages
   modify (content not null);

Anyway, it’s very important to review all the application code affected by the change, because, for example, if you use the substr function, you have to change it to dbms_lob.substr. On the other hand, if you use JPA and you have an entity like:

   @Table(name = "TEST_MESSAGES")
   public class TestMessages implements Serializable {

       @Column(name = "MESSAGE_ID")
       private Long messageId;

       @Column(name = "CONTENT", length = 4000, nullable = false)
       private String content;

       public Long getMessageId() {
           return messageId;

       public void setMessageId(Long messageId) {
           this.messageId = messageId;

       public String getContent() {
           return content;

       public void setContent(String content) {
           this.content = content;


You have to change the definition of content to:

   @Column(name = "CONTENT", nullable = false)
   private String content;

Finally, I’d like to point out two issues that could affect to performance. The first one is the possibility of defer the removing of the initial column, running the following statement:

   alter table test_messages
   set unused column content;

instead of:

   alter table test_messages
   drop column content;

Later, in a period of low use of the database, you can issue:

   alter table test_messages
   drop unused columns;

The other thing regarding to performance is that it’s convenient reorganizing the storage of the table by running the following command:

   alter table test_messages move;

Statements like alter index <index_name> rebuild could also be needed.


Working with Oracle XMLType and JPA over Weblogic

I’ve been working with the object type of Oracle databases for XML for a long time, but always in the context of PL/SQL programming. Last week,¬† I developed a Java EE module that made use of JPA entities, supported by tables with SYS.XMLType columns, so I’d like to share my findings with an example.

First of all, here you have the creation sentence of a table that offers persistence to a simple messaging interface:

create table messages
    message_id     number  constraint message_pk primary key,
    reception_date date    not null,
    msg_content    xmltype not null 
                           constraint message_msg_content_ck check(
                              xmlisvalid(msg_content) = 1)
  xmltype column msg_content store as clob xmlschema
  element "Messages";

This is an excerpt of a JPA entity that could represent the former table:

package com.wordpress.fcosfc.test.messaging;


import org.eclipse.persistence.annotations.Customizer;

@Table(name = "MESSAGES")
public class Messages implements Serializable {

    @Column(name = "MESSAGE_ID", nullable = false)
    private BigDecimal messageId;

    @Column(name = "RECEPTION_DATE", nullable = false)
    private Date receptionDate;

    @Column(nullable = false, name ="msg_content")    
    private String msgContent;


I’m sure you’ve realized that there is an special annotation Customizer, which makes reference to this class:

package com.wordpress.fcosfc.test.messaging;

import org.eclipse.persistence.config.DescriptorCustomizer;
import org.eclipse.persistence.descriptors.ClassDescriptor;
import org.eclipse.persistence.mappings.xdb.DirectToXMLTypeMapping;

public class XMLTypeAttributeCustomizer implements DescriptorCustomizer {

    public void customize(final ClassDescriptor descriptor) throws Exception {
        final DirectToXMLTypeMapping mapping = new DirectToXMLTypeMapping();


This class has a method that changes the default mapping of the EclipseLink JPA provider in order to use the proper type from Oracle: SYS.XMLType.

Finally, I’d like to point out that the first time I deployed my module over an Oracle Weblogic 10.3.5 application server I got a ClassNotFoundException: oracle/xdb/xmltype , so I had to extend my Weblogic domain in order to support Oracle XMType. Here you have my reference about how to do it, I just followed the instructions to support Oracle XDB.


Weblogic: Interoperating with Oracle AQ JMS. A tip for sending messages

I usually work with Oracle Streams Advanced Queuing (AQ) integrated with Weblogic. In PL/SQL programs you can set the exception queue where you want to move the messages that couldn’t be delivered to their destinations, when you send a message, for example:

  enq_msgid raw(16) ;
  eopt      dbms_aq.enqueue_options_t;
  mprop     dbms_aq.message_properties_t;
  mprop.priority        := 1;
  mprop.exception_queue := 'test_exception_q';
  message               :=$_jms_text_message.construct() ;
  message.set_text('This is a Test') ;
  dbms_aq.enqueue(queue_name => 'test_q', 
                  enqueue_options => eopt,
                  message_properties => mprop, 
                  payload => message, 
                  msgid => enq_msgid) ;


But, how to set the exception queue when you’re programming in Java, in the context of an integration between Oracle AQ and Weblogic? The solution I’ve found is to set the string property called JMS_OracleExcpQ to the message with the name of the Oracle AQ exception queue, for example:

javax.jms.TextMessage msg = jmsSession.createTextMessage();
msg.setStringProperty("JMS_OracleExcpQ", "TEST.TEST_EXCEPTION_Q");
msg.setText("This is a Test");

An important advice: put the name in uppercase and preceded by the name of the schema.

Finally, I’d like to point out that this is a good solution in the context of an integration between Oracle AQ and Weblogic, but makes the code non portable, if you want to change the messaging provider of your Weblogic Application Server.