Some explorations around Java Stored Procedures in the Oracle Database

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.

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.

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.

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:

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.

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).

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:

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:

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

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.

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!