Calling/Using JMS from PL/SQL

Question

Is it possible to call/use JAVA Messaging Service (JMS) from PL/SQL?

I know we can call java from pl/SQL, but calling java is different from calling JMS Queues or JMS Topics, because JMS depends upon JNDI-resource naming and when we use JNDI based resources we first have to deploy them in some J2EE container and then use them. So calling JMS always involves deploying on some J2EE container and then utilizing its functionalities.

Coming back to my question as i mentioned earlier, i want to use JMS from PL/SQL and how it would handle the deployment & JNDI-based resources stuff..?

Answer 1

There are two issues in your question that need to be addressed separately:

JNDI

No, calling a JMS service does not depend on having a JNDI-resource nor you need to have the JMS client deployed in a container. The reason for using JNDI within a container is to avoid having configuration parameters hard-coded in your application code (by using a "directory" of named "things".)

For example, we use JNDI to get a connection pool from which to get a jdbc connection, but I could equally create a jdbc connection directly. The later is fine for testing or for a command-line utility, but it is certainly not fine for a general case (which is why we typically opt for the former, jndi-based option.)

With JMS, yep, you indeed need JNDI, but that doesn't mean your client needs to be in a EE container. Take a look at the JMS tutorial at the Oracle/Sun site, and check the simple examples section:

http://download.oracle.com/javaee/1.3/jms/tutorial/1_3_1-fcs/doc/client.html

IIRC, every example shows clients that can be run from the command line and where you simply pass the queue name and other parameters from the command line. It should be easy to retrofit that code so that you can load them up from a property file or as parameters in a function call.

Java in Store Procedures

Once you have a command-line client that can access the JMS queue you want to access to, you can retrofit that code so that it runs as a stored procedure. Yes, you can use Java to write stored procedures with Oracle...

... now, I think that is a horrible feature, one that is way too open to abuse. But, if you have a legitimate need to access a JMS provider from PL/SQL, this would be one way to go.

First, convert your command-line jms client into a stored procedure. Check the existing documentation on how to create java-based stored procedures with Oracle.

http://www.stanford.edu/dept/itss/docs/oracle/10g/java.101/b12021/storproc.htm

http://download.oracle.com/docs/cd/B10501_01/java.920/a96659.pdf

Then have your PL/SQL code call the stored procedure just as they would call any other stored proc or SQL statement. And voila.

Parting Thoughts

I've never done any of this, and there might be problems along the way. However, at least conceptually, it should be possible. At the very least you should be able to create a jms command-line utility that you can then convert into a java-based stored proc.

edit

Apparently Oracle has something called "Oracle Advanced Queueing" where you can access a JMS provider directly via PL/SQL.

http://www.akadia.com/services/ora_advanced_queueing.html

http://technology.amis.nl/blog/2384/enqueuing-aq-jms-text-message-from-plsql-on-oracle-xe

http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96587/qintro.htm

Looks like a lot of reading and elbow grease involved, but it is certainly feasible (assuming you are using the right Oracle version.)