Oracle: Java stored procedure sending JMS Message

Question

I am attempting to send a Point-to-Point JMS message from an oracle database stored procedure to a java application. The two 'points' sit on different machines, which I've confirmed can talk to each other via ping.

I've created a java application able to successfully take messages off a queue within the application server. The application is running within a JBoss v4.2.3 server. I've been able to successfully send a JMS message from a remote java application, so I'm sure the code running within the server is ok.

I've taken code from the working remote java application and loaded this successfully into an oracle stored procedure. I've also managed to (I believe!) load into oracle the required jar files using the loadjava utility. The three jar files I've loaded in are:

   * jms-1.1 
   * jbossmq-3.2.3
   * jboss-client-4.0.2

The three jars are used within the working remote java application and appear to be all that's required. The code contained loaded into the stored procedure is as follows:

    package com.base.jms.client;

    import java.util.Hashtable;

    import javax.jms.JMSException;
    import javax.jms.Queue;
    import javax.jms.QueueConnection;
    import javax.jms.QueueConnectionFactory;
    import javax.jms.QueueSender;
    import javax.jms.QueueSession;
    import javax.jms.TextMessage;
    import javax.naming.Context;
    import javax.naming.InitialContext;

    public class StandAloneClient {

        public static String send() throws Exception {

            String result = "Starting -> ";

            try {

                Hashtable env = new Hashtable();
                env.put(Context.INITIAL_CONTEXT_FACTORY, "org.jnp.interfaces.NamingContextFactory");
                env.put(Context.PROVIDER_URL, "192.168.111.242:1099");
                env.put(Context.URL_PKG_PREFIXES, "org.jboss.naming:org.jnp.interfaces");

                result = result + "Environment -> ";

                // set up stuff
                Context ic = new InitialContext(env);
                result = result + "Context -> ";

                QueueConnectionFactory connectionFactory = (QueueConnectionFactory) ic.lookup("ConnectionFactory");
                result = result + "Factory -> ";

                Queue queue = (Queue) ic.lookup("queue/A");
                result = result + "Queue -> ";

                QueueConnection connection = connectionFactory.createQueueConnection();
                result = result + "Connection -> ";

                QueueSession session = connection.createQueueSession(false, QueueSession.AUTO_ACKNOWLEDGE);
                result = result + "Session -> ";

                QueueSender sender = session.createSender(queue);
                connection.start();

                result = result + "Sender -> ";

                TextMessage myMessage = session.createTextMessage();
                myMessage.setText(result);
                sender.send(myMessage);

                result = result + "Sending Message -> ";

                sender.close();
                session.close();
                connection.close();

                result = result + "Close";

            } catch (JMSException e) {
                result = result + "JMS Exception";

                /*
                if(e.getMessage() != null) {
                    result = result + ":" + e.getMessage();
                }*/

            } catch (Exception e) {
                result = result + "Exception";

                /*
                if(e.getMessage() != null) {
                    result = result + ":" + e.getMessage();
                }*/

            }

            return result;
        }

    }

I've added the result string in so I can try and determine where in the code it's falling over. To create and test this procedure, I'm executing the following commands in sqlplus:

create or replace function send_jms return VARCHAR2 as language java name 'com.base.jms.client.StandAloneClient.send() return java.lang.String';

variable myString varchar2(20);
call send_jms() into :myString;
Call completed.
print myString;

Everything seems to be loaded and compiling correctly, however the message is not being sent. The result string returned implicates it's falling over when attempting to retrieve the QueueConnectionFactory class from the InitialContext. The result string returned is:

Starting -> Environment -> Context -> Exception

I'm at a loss as to why this is not working, and have been unable to glean more from the Exception thrown. Can anyone confirm that I am doing this correctly, and if I am, see what I am doing wrong?

Apologies for the long post but thank you in advance for looking at it!

Answer 1

I'm not exactly an expert about running Java and JMS within the Oracle database (though I know each of the three components separately). But from your description it seems you haven't taken the Oracle security model for Java into consideration.

Oracle will not let any component access the network (or the file system etc.) without having explicitly being granted the right to. So start reading about Oracle JVM security to learn how you might need to configure Oracle for letting you connect to a remote machine.

Granting the permissions could involve the following statement:

EXEC DBMS_JAVA.GRANT_PERMISSION('YOUR_SCHEMA', 'SYS:java.net.SocketPermission', '192.168.111.242', 'connect,accept,resolve');