Sending JMS from OracleDB to external ActiveMQ Broker

After taking over a legacy application of which a huge part of the business logic is formed by triggers and procedures inside an Oracle DB, we faced the task of a step-by-step migration of that logic to Java code. Due to the complete lack of a defined and sophisticated service layer and having other systems connected using several autonomous interfaces which directly access the underlying database this migration is quite complicated.
An idea popped up. As a intermediate step on the way to move the business logic back to the Java side, why not port the trigger PL/SQL to Java (behind a new created service layer) and let the trigger fire JMS messages to trigger the execution of that Java code?

Prerequisites

Since Oracle 9i it is possible to use Java code in the Oracle DB. However the provided (not replaceable) JDK lacks behind the current standards. On top of that there are some specifics to be considered. Details on that topic can be found here.
Our setup consists of the following components:

• Oracle 11gR2 (providing JDK 1.5)
• Active MQ 5.4.2 (the last version that can be build using JDK 1.5)
• Spring 3.0.5

Preparation of user privileges

The following privileges must be granted to the database user (in our demonstration: TEST) from which the sending of JMS messages shall occur.
Direct access to the class loader and system properties:

call dbms_java.grant_permission( 'TEST', 'SYS:java.lang.RuntimePermission', 'getClassLoader', '' );
call dbms_java.grant_permission( 'TEST', 'SYS:java.util.PropertyPermission', '*', 'read,write' );

Creating a socket connection and full access to it:

call dbms_java.grant_permission( 'TEST', 'SYS:java.net.SocketPermission', '*', 'listen,resolve');
call dbms_java.grant_permission( 'TEST', 'SYS:java.net.SocketPermission', '*', 'accept,resolve');
call dbms_java.grant_permission( 'TEST', 'SYS:java.net.SocketPermission', '*', 'connect,resolve');

Note: The placeholder '*' allows the creation of socket connections to any target host and port. In production systems that shall be reduced to the IP range needed.

A small prototype

For demonstration purposes we use Spring to fire up an ActiveMQ broker, initialize a test queue and add a simple listener. Here’s the corresponding part of the application context configuration.

<!-- create message broker - instead of using a somewhere centralized activemq server -->
<bean id="broker" class="org.apache.activemq.xbean.BrokerFactoryBean">
    <property name="config" value="classpath:META-INF/activemq.xml" />
    <property name="start" value="true" />
</bean>
<!-- a simple test queue - the queue name is specified by the bean id -->
<bean id="testQueue" class="org.apache.activemq.command.ActiveMQQueue" />
<!-- JMS connection factory (wrapped into a pooling connection factory) -->
<bean id="jmsFactory" class="org.apache.activemq.pool.PooledConnectionFactory" destroy-method="stop">
    <property name="connectionFactory">
        <bean class="org.apache.activemq.ActiveMQConnectionFactory">
            <property name="brokerURL" value="tcp://localhost:61616" />
        </bean>
    </property>
</bean>
<!-- simple message listener just logging received message to stdout -->
<bean id="simpleListener" class="com.synyx.prototype.jms.SimpleMessageListener" />
<!-- listener container delegating to listener instances and wiring them to their destinations -->
<jms:listener-container concurrency="10" connection-factory="jmsFactory">
    <jms:listener id="queueListener" destination="testQueue" ref="simpleListener" />
</jms:listener-container>

On the side of the message producer a QueueConnectionFactory implementation provides the connectivity to the ActiveMQ broker. For our prototype it lacks any authentication mechanisms.

public class ActiveMQQueueConnectionFactory implements QueueConnectionFactory {
    private ConnectionFactory connectionFactory = null;
    public ActiveMQQueueConnectionFactory(String brokerUrl) {
        this.connectionFactory = new ActiveMQConnectionFactory(brokerUrl);
    }
    public QueueConnection createQueueConnection() throws JMSException {
        return (QueueConnection) createConnection();
    }
    public QueueConnection createQueueConnection(String username, String password) throws JMSException {
        return createQueueConnection();
    }
    public Connection createConnection() throws JMSException {
        return this.connectionFactory.createConnection();
    }
    public Connection createConnection(String username, String password) throws JMSException {
        return createConnection();
    }
}

The class QueueMessageSender implements a simple text message producer. Note that the JMSException isn’t caught but propageted to the caller. Exceptions are finally handled by the global Oracle VM Exception Handler. That way, in case of an exception, the exceptions message ends up in the ORA-XXXX error designation and the full stack trace is stated in the user’s session log.

public class QueueMessageSender {
    private QueueConnectionFactory connectionFactory = null;
    public QueueMessageSender(QueueConnectionFactory connectionFactory) {
        this.connectionFactory = connectionFactory;
    }
    public void sendMessage(String destination, String message) throws JMSException {
        QueueConnection connection = null;
        try {
            connection = this.connectionFactory.createQueueConnection();
            QueueSession session = connection.createQueueSession(false, Session.AUTO_ACKNOWLEDGE);
            Queue queue = session.createQueue(destination);
            QueueSender sender = session.createSender(queue);
            TextMessage textMessage = session.createTextMessage(message);
            sender.send(textMessage);
        } finally {
            if (null != connection) {
                connection.close();
            }
        }
    }
}

The following class provides the entry point to be called from PL/SQL (i.e. sending a message triggering the service call replacing the legacy trigger code). For demonstration purposes it is kept simple like the rest of the code examples. Note that the method acting as entry point must be static for beeing callable from PL/SQL.

public class JMSFromOracleTest {
    private static final String BROKER_URL = "tcp://192.168.x.x:61616";
    private static final String QUEUE_NAME = "testQueue";
    public static void sendMessage(String message) throws JMSException {
        QueueConnectionFactory connectionFactory = new ActiveMQQueueConnectionFactory(BROKER_URL);
        QueueMessageSender sender = new QueueMessageSender(connectionFactory);
        sender.sendMessage(QUEUE_NAME, message);
    }
}

Packaging and Deployment to the Oracle DB

To simplify the deployment we created a small Maven project covering the producer code and used the Maven Assembly plugin for packaging the producer classes and all dependencies into a single JAR file. Again, to keep things simple we added the activemq-all distribution as the only dependency.

Note: All classes (the producer classes and all dependencies) need to be compiled using/for JDK 1.5 (class version <= 49.0).

Oracle keeps all Java class files and resources in the database. As the name implies, the command line tool “loadjava” is used to load Java resources into the db. This command must be issued on the Oracle DB server itself. For that, the environment variable ORACLE_HOME must be correctly set. In reverse, the tool “dropjava” provides an easy way to unload Java resources from the DB.

Issuing the following command loads all resources contained in our JAR file into the Oracle DB.

loadjava -v -r -u test/12345 -resolver "((* TEST) (* PUBLIC) (* -))" JMSSender-1.0-SNAPSHOT-jar-with-dependencies.jar

The switch -r enables the resolving of all classes references by the loaded classes. If any reference made by a class could not be resolved, that class is marked INVALID. Classes referencing invalid classes are also marked INVALID. Note that we declared our own resolver using the -resolver parameter. Using the parameter as stated above all classes in any package declared in the SCHEMA TEST and PUBLIC are allowed to reference unresolved dependencies. That way features and connectors of ActiveMQ not used (and therefore lack the required dependencies) do not invalidate the core classes. The parameter -u is followed by the user credentials (username/password) of the user to whose default schema the resources are deployed.

After all resources contained in the JAR file are deployed (this may take a while – but keeps you entertained because of the -v parameter), we need to create a stored procedure usable from PL/SQL that directs the call to the entry point method of our Java implementation. For general information on calling Java Methods from PL/SQL (i.e. referencing parameters and return values) see here.

CREATE OR REPLACE PROCEDURE sendJmsMessage(message IN VARCHAR2)
AS LANGUAGE JAVA NAME 'com.synyx.prototype.jms.JMSFromOracleTest.sendMessage(java.lang.String)';

Finally, we are done. Calling the procedure from PL/SQL will invoke our Java method and a text message containing the given text will be posted.

call sendjmsmessage('hello from oracle');