Oracle and Java

While Oracle's PL/SQL is quite powerful, and the obvious choice for accessing databases, sometimes you need to go outside the box. Perhaps you need to communicate with a daemon or a webserver. Maybe you have to perform some complex calculations which cannot be readily coded in PL/SQL. In the past you would have probably used native functions written in C. Today, though, you can import functions written in the readily portable Java language (see the bottom of the page for another approach.)

So how do you actually go about using Java functions from Oracle's PL/SQL? I suppose you could take an expensive course and learn to use expensive tools to accomplish the goal. You could also spend a lot of time digging through poorly organized documentation to discover for yourself. Or, you could read this document and learn exactly the steps you need to take.

I've done the documentation mining and the information in this document has come from a variety of sources. Despite the existance of numerous promising-sounding documents on the Oracle Technology Network web-site, I wasn't able to collect all this information in one place. There was also a fair bit of trial-and-error involved, even though some of the things here would have been ancient knowledge to Oracle gurus.

First of all, Oracle has done a good job of mapping Java classes to PL/SQL variables. Many of the converions are performed in the background, totally transparently. I won't re-hash the details here because I'm only interested in the simplest types. Suffice it to say the the PL/SQL VARCHAR2 type maps to the Java String class and the PL/SQL INTEGER type maps to the Java int type. Consideration of other types is left as an exercise for the reader.

For our purposes, we're going to create an extremely simple Java class containing a single method. It's essential that the method be defined as static, i.e. a class rather than an instance method. While there are some exceptions to this rule, for now just accept that the static requirement is in place. Here's what our simple example looks like:

public class Greetings {
	public static String SayHello( String name ) {
		return( new String( "Hello, " + name + "!" ) );
	}
}

$ javac Greetings.java

$ loadjava -user username/password Greetings.class

SQL> CREATE OR REPLACE FUNCTION greetings (name VARCHAR2) RETURN VARCHAR2
  2  AS LANGUAGE JAVA NAME 'Greetings.SayHello(java.lang.string) return
  3  java.lang.String';
  4  /

Note the the function is defined in PL/SQL and is resolved through the specification in the NAME string. The PL/SQL portion of the definition takes the general form:

funcname ([varname vartype[,...]]) [ RETURN returntype ]

class.method([argclass[,...]]) [ return returnclass ]

SQL> SET SERVEROUTPUT ON
SQL> CALL DBMS_JAVA.SET_OUTPUT(10000);

I find it easier to put programs I plan to run more than once in their own files. Create a file called greetings.sql containing the following:

SET SERVEROUTPUT ON
DECLARE
        message VARCHAR2(2500);
        person VARCHAR2(20);
BEGIN
        person := 'Fred';
        message := greetings ( person );
        DBMS_OUTPUT.PUT_LINE( message );
END;
/

SQL> @greetings
Hello, Fred!

PL/SQL procedure successfully completed.

SQL>

Well, not quite. Depending on what your Java code is trying to do, you might need to set user permissions accordingly. This table shows the various arguments for calls to the dbms_java.grant_permission procedure. The general format of the call (as an administrator, of course) is as follows:

call dbms_java.grant_permission( 'user', 'class', 'name', 'value' );

where user is the database user name and the other fields are as defined in the following table.

Where filename is either the name of a file or a directory. If it's a directory then adding /* provides access to all files in the directory while a /- suffix gives access to the entire tree from the named directory on down. Specifying '/-' would give complete access to the file system, probably not such a good idea. An example is in order here. If I created a Java method which wrote to a log file in /var/tmp then I might give myself the appropriate permissions as follows:

SQL> call dbms_java.grant_permission( 'PSELBY', 'java.io.FilePermission', '/var/tmp/-', 'read,write,delete' );
SQL> call dbms_java.grant_permission( 'PSELBY', 'java.lang.RuntimePermission', 'setFileDescriptor', '' );
SQL> call dbms_java.grant_permission( 'PSELBY', 'java.lang.RuntimePermission', 'readFileDescriptor', '' );
SQL> call dbms_java.grant_permission( 'PSELBY', 'java.lang.RuntimePermission', 'writeFileDescriptor', '' );

SQL> call dbms_java.grant_permission( 'PSELBY', 'java.net.SocketPermission', '*', 'connect,resolve' );

By way of example, following is the source code for a Java module which does logging. Note that it will be defined in sqlplus thusly:

SQL> CREATE OR REPLACE PROCEDURE logit (text VARCHAR2)
  2  AS LANGUAGE JAVA NAME 'LogDemo.add(java.lang.string)';
  3  /

Here's the code:

import  java.io.*;

public class LogDemo {

        private static String   logFileName = "/var/tmp/java.log";

        public static void add( String entry ) {
                RandomAccessFile        logFile = null;
                PrintWriter             pw = null;

                try {
                        logFile = new RandomAccessFile( logFileName, "rw" );
                        logFile.seek( logFile.length() );
                        pw = new PrintWriter( new OutputStreamWriter(
                          new FileOutputStream( logFile.getFD() ) ) );
                        pw.println( entry );
                        pw.flush();
                        pw.close();
                        logFile.close();
                }
                catch( Exception e ) {
                        e.printStackTrace();
                }
        }
}

Here's a more typical application, namely a function to send an XML document to a webserver and return the response to the caller. This introduces an additional level of complexity since I need to return both a numeric return code and the received document. Oracle normally passes arguments by value, so how do we define the function such that we can return the document? Like this:

SQL> CREATE OR REPLACE FUNCTION XMLsend (dest IN VARCHAR2,doc IN OUT VARCHAR2 )
  2> RETURN NUMBER AS LANGUAGE JAVA NAME 'XML.transfer(java.lang.String,
  3> java.lang.String[]) return int';
  4> /

Note how the second argument has been defined as IN OUT and that the corresponding argument in the Java function has been defined as being an array of String. Element 0 of the array contains the data we send to the method but the method has the ability to assign a new value to the element. Here's the actual code:

import	java.io.*;
import	java.net.*;
import	java.util.*;

public class XML {

	public static int transfer( String dest, String doc[] ) {
		URL			url = null;
		HttpURLConnection	conn = null;
		BufferedReader		br = null;
		PrintWriter		pw = null;
		String			lineIn = null;
		StringBuffer		sb = new StringBuffer();
		OutputStream		os = null;
		int			rc = 0;

		try {
			url = new URL( dest );
			conn = (HttpURLConnection) url.openConnection();
			conn.setDoOutput( true );
			os = conn.getOutputStream();
			os.write( doc[0].getBytes() );
			os.flush();
			os.close();
			try {
				rc = conn.getResponseCode();
			}
			catch( Exception e ) {
			}
			if( rc < 0 )
				rc = conn.getResponseCode();
			if( rc == 200 ) {
				br = new BufferedReader( new InputStreamReader(
				  conn.getInputStream() ) );
				while( ( lineIn = br.readLine() ) != null )
					sb.append( lineIn + "\n" );
				br.close();
			}
			else {
				sb.append( conn.getResponseMessage() );
			}
			if( sb.toString().length() > 0 )
				doc[0] = sb.toString();
		}
		catch( Exception e ) {
			rc = -1;
			doc[0] = e.toString();
		}
		return( rc );
	}
}

Note that we access the original document as doc[0] and set doc[0] to a new String value before returning the integer return code. All we have to do is compile the Java code, use loadjava to save the class in the database and call the function. Here's an example:

SET SERVEROUTPUT ON

DECLARE
        request VARCHAR2(4096);
        rc      NUMBER;
BEGIN

        request := 'this is a test';
        rc := XMLsend( 'http://localhost:9080/servlet/echo2', request );
        dbms_output.put_line( request );
END;
/

Having covered all of this, what if you just want to be able to access a webserver directly from PL/SQL? It can be done and it's not exactly rocket science. For the sake of conversation, we'll assume that we want to exchange XML documents with a webserver, which will require the HTTP POST method. Oracle 9i has a nice utility class expressly designed for these kinds of interactions (UTL_HTTP) but we have to "roll our own" with Oracle 8i. We're going to use the UTL_TCP class and layer HTTP on top. It's not necessarily elegant, but it's a straightforward approach. Here's a session log:

Script started on Fri Nov 30 18:22:57 2001
neptune$ sqlplus

SQL*Plus: Release 8.1.7.0.0 - Production on Fri Nov 30 18:23:00 2001

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Enter user-name: freddie
Enter password: 

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SQL> set serveroutput on
SQL> set echo on
SQL> @echoTest
SQL> DECLARE
  2          conn    utl_tcp.connection;
  3          value   VARCHAR2(4096);
  4          rc      BINARY_INTEGER;
  5  BEGIN
  6  
  7          conn := utl_tcp.open_connection( 'neptune', 9080 );
  8          rc := utl_tcp.write_line( conn, 'POST /servlet/echo2 HTTP/1.0' );
  9          rc := utl_tcp.write_line( conn, 'Host: neptune.premtec.com' );
 10          rc := utl_tcp.write_line( conn, 'Accept: text/*' );
 11          rc := utl_tcp.write_line( conn, 'Accept-Charset: *' );
 12          rc := utl_tcp.write_line( conn, 'Accept-Encoding:' );
 13          rc := utl_tcp.write_line( conn, 'Content-Encoding: identity' );
 14          rc := utl_tcp.write_line( conn, 'Content-Type: text/xml' );
 15          rc := utl_tcp.write_line( conn, 'Content-Length: 16' );
 16          rc := utl_tcp.write_line( conn, '' );
 17          rc := utl_tcp.write_line( conn, 'echo this back' );
 18          utl_tcp.flush( conn );
 19          LOOP
 20                  rc := utl_tcp.read_line( conn, value );
 21                  dbms_output.put( value );
 22          END LOOP;
 23          utl_tcp.close_connection( conn );
 24  
 25  EXCEPTION
 26  
 27          WHEN utl_tcp.end_of_input THEN
 28                  utl_tcp.close_connection( conn );
 29                  dbms_output.new_line();
 30  
 31  END;
 32  /
HTTP/1.1 200 OK
Date: Fri, 30 Nov 2001 23:23:16 GMT
Server: Apache/1.3.9
(Unix) tomcat/1.0 mod_ssl/2.4.5 OpenSSL/0.9.4
Connection: close
Content-Type:
text/html

echo this back


PL/SQL procedure successfully completed.

SQL> quit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
neptune$ exit

script done on Fri Nov 30 18:23:20 2001

I turn on serveroutput and echo (I want to get the program listing) and then execute the echoTest.sql code. On line 2 we declare a connection of type utl_tcp.connection. We'll use a 4K buffer to hold the response. Please note that there are a number of limitations in Oracle, including the maximum number of characters you can output. Once you get close to two thousand characters, keep this warning in mind. Finally, we have to declare the return code even though, programmatically, we ignore it.

We make the connection to the webserver in line 7 then start sending the HTTP headers. Please note that the HTTP/1.0 at the end of line 8 is absolutely vital. Remember this if you cut and paste the code and change the URI. I set the content type to text/xml in line 14 and the content length in line 15. Keep in mind that each line is terminated with a CR/LF combination. The string in line 17 is only 14 characters in length but we add 2 to each output line to account for the line termination characters. As with many other Internet protocols, the blank like generated in line 16 of the code separates the headers from the body.

We read input lines on code line 20. The utl_tcp.read_line method has an optional third argument which, if set to TRUE, can strip the line termination characters. The problem with that is detecting the split between the headers and the body of the reply. That's also why we use dbms_output.put instead of put_line on code line 21. Finally, we have to properly terminate our collected lines of output and we do so on line 29 with the dbms_output.new_line call. As you can see from this session log, you will have to extract the status code and message as well as do something with the body in your own programs. This is just are bare-bones demonstration of what is possible directly from Oracle PL/SQL.

NOTE: The author is available for short- or long-term contract work. While we are unable to guarantee a reply due to the volume of e-mail received, you are most welcome to posit additional questions to pselby@selbyinc.com.