Some explorations around Java Stored Procedures in the Oracle Database

3

While working on the challenge to publish a message to a JMS Queue in a remote WebLogic Server from within the Oracle Database – using a Java Stored Procedure – I came across a few things that I would like to record for future reference. Note that unfortunately I have not [yet?] succeeded in making that JMS publication work.

The things I would like to record here are:

  • using JDeveloper to developer and load Java Classes and create PL/SQL Wrappers for them
  • using the loadjava command line utility to load (the classes in) JAR files into the database
  • using the ojvmjava command line utility to try out the classes that have been loaded into the Oracle database directly (which allows us to get access to the console output and the stacktraces when exceptions occur)

If anyone has pointers for my main challenge: publishing a message onto a WebLogic Queue, I would be most interested because even with a lot of help from a lot of (virtual, cyber if not imaginary) friends I have not been able to pull it off. In fact, at this point I am not even able to initialize a JNDI context in a Java Stored Procedure (JSP) through a connection with a remote WLS server.

The class I want to load into the Oracle Database so it can be invoked from PL/SQL is as simple as:

package nl.amis;

import java.util.Properties;
import javax.naming.InitialContext;
import javax.naming.NamingException;

public class InitializeContext {

    protected static final String NAMING_PROVIDER_URL =
        "t3://192.167.131.136:7101";
    protected static final String NAMING_PRINCIPAL = "weblogic";
    protected static final String NAMING_CREDENTIAL = "weblogic1";

    public static void initContext() {
        InitialContext jndiContext = null;
        Properties jndiProperties = new Properties();

        // set JNDI properties
        jndiProperties.put("java.naming.factory.initial",
                           "weblogic.jndi.WLInitialContextFactory");
        jndiProperties.put("java.naming.provider.url", NAMING_PROVIDER_URL);
        jndiProperties.put("java.naming.security.principal", NAMING_PRINCIPAL);
        jndiProperties.put("java.naming.security.credentials", NAMING_CREDENTIAL);
        try {
            jndiContext = new InitialContext(jndiProperties);
        } catch (NamingException e) {
            e.printStackTrace();

        }
    }

    public static void main(String[] args) {
        System.out.println("go initialize");
        InitializeContext.initContext();
        System.out.println("done initializing");
    }
}

In order for this class to be compiled and run, the WebLogic jar file wlclient.jar needs to be available on the classpath. In order to go beyond this and actually do JMS things, the jar file wljmsclient.jar is required too. These files can be found in the WL_HOME/server/lib directory of any WebLogic Server installation.

In order to load this class into the database, it is most convenient to create a JDeveloper deployment profile, from the New Gallery.

Image

Next, when the profile is created, it can be edited, to specify which project artifacts should be part of. In this case: just a single class or everything from the Project Output paths – which amounts to same single class.

Image

The context menu on the deployment profile contains the option to add a PL/SQL package. This is to be the wrapper package that exposes a PL/SQL interface for methods in the Java class(es) that are loaded into the database as Java Stored Procedures.

Image

Once a PL/SQL [wrapper]package has been created, static methods in the classes in the project and deployment profile can be selected to be exposed through a PL/SQL procedure or function:

Image

In this particular case, a single PL/SQL procedure is to be published, to wrap and link to the static initContext() on the InitializeContext class.

Image

In order for this Java Stored Procedure to be used inside the database JVM at runtime, the wlclient.jar file needs to be loaded into the database too.

A convenient way for uploading such a jar file is through the use of the loadjava command line utility (see http://download.oracle.com/docs/cd/B28359_01/java.111/b31225/cheleven.htm#CACFHDJE for documentation).

Image

Note how I uploaded everything into the SYS schema (of my local database, managed by me, myself and I) in order to first create a working solution and only then start to worry about the exact security privileges to hand out. Also note that when I want the individual classes from these jars to be uploaded, I can simply leave out the -jarsasdbobjects and -prependjarnames options.

When I tested the Java Stored Procedure through the PL/SQL wrapper package, I received a result that was not promising, and cryptic as well:

Image

This does not provide insight in any original Java Stacktraces nor does it show any of the console output that may have been created using simple sys.output.println calls.

Using the OVJMJAVA tool (see: http://download.oracle.com/docs/cd/B28359_01/java.111/b31225/cheleven.htm#CACDIEGD for details), it is possible to directly invoke the Java Classes loaded into the Oracle Database, without going through the PL/SQL wrapper package.

The command line tool is run like this:

Image

A particular class can subsequently be run from the command prompt – and both console output and stacktraces show up:

Image

When I try to execute the InitializeContext class, I keep running into a nasty NullPointerException in a VersionHelper class that is somewhere under javax.naming.InitialContext.

Image

This is helpful information and certainly much better than invoking the PL/SQL wrapper packages produces. Unfortunately, I have no idea what is going on here – and how to fix it!

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

3 Comments

  1. Hi Edwin,

    Thanks for thinking along. I had reached that same conclusion with regard to using AQ exposed as JMS to hook into a JEE server. The only drawback for my particular scenario is that this requires additional work in the application server – where I was hoping for a way to configure the JMS Queue to publish onto in the  database and completely stay out of the app server.

    However, I concluded earlier on that it will not be possible to create a single, generic solution in PL/SQL and JSP that support any JMS server; it turns out that every JMS implementation (JBoss, GlassFish, WebSphere, WebLogic) requires its own, special client implementation – using dedicated libraries. So instead of being able to create a single JSP with a single set of associated JARs, we will have to create and load a JSP+ JARs for every JMS implementation that needs to be supported.

    The best approach seems to be now configure an Advanced Queue in the database expose itself as a JMS Queue that Java Clients then can register to. The following approach:
    * during installation of the application, create an Advanced Queue and expose it as JMS Queue (or Topic)
    * have the database application publishe to this AQ
    * create a MDB (Message Driven Bean) that registers to the AQ based JMS Queue and publish onwards to any desired target JMS Queue or Topic. This MDB can be configured through a properties file or through JMX to target it to the correct target JMS Queue. By adopting this approach, your database implementation stays clean – no complex JSP and JAR installation, no complex configuration of security settings and privileges for the JSP, no special support for every type of JMS implementation that you want/need to support and the full flexibility and decoupling of JMS is still available: your customer sets up the target queue that you should publish to [from the database, directly or indirectly] and the MDB is configured to listen to the AQ based JMS and publish to the ‘real’ JMS Queue. The only complexity introduced is a (standard) Java class (MDB) that should be deployed on the application server used by the customer to run their JMS environment
    With WebLogic we could adopt the approach you suggest, with the Foreign JMS Provider. I am not sure how that works out on other application servers that need to be supported.

    Lucas

     

  2. I think loading java inside is not the best practice, too much objects , think about the public synonyms, hard to remove and restoring, exporting is hard.
    Personally the only good way to communicate to the outside world  from the database is AQ. And in that case expose the queue with jms object type as a foreign jms server in WebLogic , this works great on the WebLogic jvm , else build a mdb which reads the aq queue and publish it to a jms queue and uses XA .

Leave a Reply