Calling / using JMS from PL / SQL

Question

Can I call / use the JAVA Messaging Service (JMS) from PL / SQL?

I know that we can call java from pl / SQL, but calling java differs from calling JMS queues or JMS in that JMS depends on the name of the JNDI resource and when we use resources based on JNDI, we first need to deploy them to J2EE and then use them. Therefore, a JMS call always involves deploying to some kind of J2EE container, and then using its functions.

Returning to my question, as mentioned earlier, I want to use JMS from PL / SQL and how will it handle resources for deployment and JNDI resources ..?

Answer 1

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

Jndi

No, a JMS service call is independent of the presence of a JNDI resource, and you do not need the JMS client to be deployed in the container. The reason for using JNDI in the container is to avoid having the configuration parameters hardcoded in the application code (using the "directory of" named "things".)

For example, we use JNDI to get a connection pool from which you can get a jdbc connection, but I could also create a jdbc connection directly. A later version is suitable for testing or for the command line utility, but this, of course, is not suitable for the general case (which is why we usually choose the option based on jndi.)

With JMS, yes, you really need JNDI, but that does not mean that your client must be in an EE container. Take a look at the JMS tutorial on the Oracle / Sun website and check out the simple examples section:

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

IIRC, each 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. You need to easily modify this code so that you can load them from the properties file or as parameters in a function call.

Java in storage procedures

Once you have a command line client that can access the JMS queue that you want to access, you can modify this code to run as a stored procedure. Yes, you can use Java to write stored procedures with Oracle ...

... now, I think this is a terrible function that is too open for abuse. But if you have a legitimate need to access a JMS provider from PL / SQL, this will be one way.

First, convert your command line command-line client to a stored procedure. Check out the existing documentation on how to create Java stored procedures using 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 your PL / SQL code calls the stored procedure in the same way that they will call any other stored proc or SQL statements. And voila.

Thoughts Thoughts

I never did this, and there could be problems. However, at least conceptually, this should be possible. At the very least, you should be able to create a jms command-line utility that can then be converted to a java-based stored procedure.

change

Apparently, Oracle has something called "Oracle Advanced Queuing," where you can directly contact the JMS provider through 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

It seems like there is a lot of grease for reading and elbow, but it is certainly possible (assuming you are using the correct version of Oracle.)

Answer 2

I could update the old thread, but I just used JMS to send messages from the PLJava trigger function. The only requirement that I have never written anywhere is to upload jar files to a jms broker (I used activemq) in your database via the pljava installation function. Other procedures are the same as this example.