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 found that even though I was trying to grant permission as SYS, and the policy belonged to SYS, then SYS didn't have permission to grant that policy. So this worked for me:

exec dbms_java.grant_policy_permission('SYS','SYS','java.net.SocketPermission','*');
exec dbms_java.grant_permission( 'MY_SCHEMA',  'SYS:java.net.SocketPermission', '*', 'connect,resolve' );

Answer 2

I've finally got this all working, thanks in large part to the help offered by Cody. I'm just putting this answer up in case someone else comes across the same problems and wants to know exactly how I solved it.

The statements I ran to grant the necessary permissions to oracle were:

GRANT JAVASYSPRIV to NTIS
EXEC dbms_java.grant_permission ('JAVASYSPRIV', 'SYS:java.net.SocketPermission', '*', 'accept,connect,listen,resolve');
EXEC DBMS_JAVA.GRANT_PERMISSION('YOUR SCHEMA', 'SYS:java.net.SocketPermission', '*', 'connect,accept,resolve');

You have to been logged in as 'SYSTEM' user when executing these commands.

I had some further problems with the stored procedure which were down to issues with the libraries I loaded into oracle. The jars I used were:

 jms
 jbossmq-client 
 jboss-client 

The jbossmq-client and jboss-client jars must be the same versions as those used in the application server itself. I was using JBoss 4.2.3, so I copied these jars from the 'client' folder within the JBoss directory.

I also had to put the ipaddress referenced in the java class/stored procedure in the hosts file as it appears oracle does a reverse lookup on the ipaddress. If you don't do this you get an unknown host exception. In the hosts file, just put your computers name and the ipaddress.

After doing all this is, its now working correctly for me! Hope this helps someone if they come across the same problem.

Answer 3

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');