Java Stored Procedures

Since Oracle 8i, Oracle has included a Java Virtual Machine (JVM) inside the database. In Oracle 9i Release 2, the JVM supports JDK 1.3 and conforms fully to the Java Language Specification. Having a JVM inside the database allows Java methods to run inside the database. To do this, we need to write a PL/SQL function or procedure call specification that, instead of including an implementation in PL/SQL, includes a LANGUAGE JAVA clause that maps a Java method's parameters and return types to PL/SQL types.

There are few restrictions on what a Java method can do when it runs inside the database the most obvious one being that we can't have a user interface. For example, standard input (from the keyboard) and standard output (to the screen) are not available. Because these calls are ignored by the Oracle JVM, we can use calls to System.out.println() for debugging outside the database and simply leave them in our code when we load it into the database.

Java stored procedures offer the advantage of speed when compared with Java programs that run outside the database, especially those that involve a lot of interaction with the database. Java stored procedures offer nearly the same performance as PL/SQL stored procedures. If speed is critical, Java stored procedures can be compiled to native executable code using Oracle's ncomp utility. The advantage that Java stored procedures have over PL/SQL is that they are portable.

Depending on the application design and performance requirements, a Java program can be deployed in a client application, in a middle tier, or inside the database. This flexibility can make development much easier. Further, assuming that no proprietary extensions are used, a Java stored procedure might even be portable to other vendor's databases Java is quickly becoming the de facto standard language for database stored procedures.

The Java Class and Stored Procedure Method

A Java stored procedure is a method, but one of the requirements of Java is that everything must belong to a class. For our example, we'll create a class, Procedures, to contain our method, myMethod(). If we were to create other Java stored procedures, we could implement them as in methods in this class too. We can also include a main() method, which will allow us to run the stored procedures outside the database for testing and debugging purposes.

To interact with the database, a Java method needs to obtain a connection. When the method is running inside the database as a stored procedure, there are two ways to do this. The first is to call the DriverManager's defaultConnection() method:

 DriverManager.defaultConnection(); 

The second is to call the getConnection() method:

 DriverManager.getConnection("jdbc:default:connection:"); 

These differ in that the first one always returns the same Connection object, but the second returns a new object each time. Unless you have a specific reason for wanting a new object each time, Oracle recommends using the defaultConnection() method.

Regardless of which way you get a connection, because it is associated with an implicit connection, you should not close it.To test and debug Java stored procedures outside the database, as a standalone program, you can obtain a connection in the main() method using the Oracle JDBC driver and store it in a class variable. (We'll disregard the details of this for now.) The main() method can then call the stored procedure method myMethod().

If myMethod() is called by main() it will already have a connection provided by main(). If it doesn't have a connection, it will know that it is running as a stored procedure inside the database and that it should obtain the server-side connection instead.

In addition to the code for obtaining a connection, myMethod() uses JDBC to query the database. JDBC will be formally introduced in Chapter 8, "Introduction to JDBC." Here, we'll just point out some of the highlights.

myMethod() creates an SQL statement by concatenating a fixed string with the method's name parameter.

 String sql = "SELECT LAST_NAME FROM NAMES WHERE FIRST_NAME = '"               + name + "'"; 

This SQL statement is executed by calling a Statement object's executeQuery() method, which returns a ResultSet object.

 ResultSet rs = stmt.executeQuery(sql); 

After locating the first row by calling the ResultSet's next() method, we retrieve a String value from a ResultSet with the ResultSet's getString() method. The parameter in this call to getString() refers to the first column in the ResultSet:

 fullname = name + " " + rs.getString(1); 

Here is the complete class, which can be compiled and run at a command prompt or loaded into the database so myMethod() can be used as a Java stored procedure.

 import java.sql.*; import oracle.jdbc.*; public class Procedures {   static Connection conn = null;   public static void main(String [] args)   {     try     {        DriverManager.registerDriver(        new oracle.jdbc.OracleDriver());        conn = DriverManager.getConnection(                "jdbc:oracle:thin:@noizmaker:1521:osiris",                "david", "bigcat");        String [] sa = new String [1];        System.out.println("procedure returned: "          + myMethod("John"));        conn.close();     }     catch(SQLException e)     {        System.out.println("Caught " + e);     }   }   public static String myMethod(String name)   throws SQLException   {     String fullname = "Unknown";      try      {         if(conn==null)         {           conn = DriverManager.getConnection(             "jdbc:default:connection:");         }         else         {            System.out.println("Executing at command prompt...");         }         Statement stmt = conn.createStatement();         String sql = "SELECT LAST_NAME FROM NAMES WHERE FIRST_NAME = '"                       + name + "'";         System.out.println(sql);         ResultSet rs = stmt.executeQuery(sql);         if(rs.next())         {           fullname = name + " " + rs.getString(1);         }     }     catch(SQLException e)     {         System.out.println("Caught " + e);     }     return "Hello " + fullname;   } } 

The other important thing to notice is that, in order to be used as a stored procedure or function, a Java method must be static inside the database there is no enclosing context available that is able to instantiate an object.

Loading and Publishing a Java Stored Procedure

Oracle provides a command line utility, loadjava, that will load a Java source, class, or .jar (or .zip) file into the database. If we load a source file, by default, it will not be compiled until the first time it is called at runtime which can cause, at best, a delay and, at worst, an error, should compilation fail. Perhaps the best option is to load the source file and use the -resolve option to compile it immediately; this has the advantage that it will load the source code into the database where it will remain available for future maintenance.

We can take the sample program above and, once we're sure it compiles and runs as expected at a command prompt, we can load and compile it with the following command, which includes the -resolve option, username and password, and Java source file:

 loadjava -resolve -user david/bigcat Procedures.java 

With the loadjava utility, no news means good news: It normally only produces output if there are errors.

Publishing the Stored Procedure

To use the stored procedure we need to publish it by creating a call specification that maps its parameters and return type to PL/SQL types. This is similar to how we define PL/SQL functions and procedures except that, instead of providing a body that includes PL/SQL executable code, we substitute a LANGUAGE JAVA clause.

The basic syntax for a function is

 CREATE [OR REPLACE] FUNCTION function_name [(parameter_list)]   RETURN sql_type {AS|IS} LANGUAGE JAVA   NAME 'class_name.method_name([full_java_type [,...]])   return full_java_type'; 

The basic syntax for a procedure is

 CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_list)]   {AS|IS} LANGUAGE JAVA   NAME 'class_name.method_name([full_java_type [,...]])'; 

A parameter in the optional parameter list has the following format:

 parameter_name [IN | OUT |  IN OUT] sql_type 

We can publish the method myMethod() in the example above as a function with the following command in SQL*Plus:

 SQL> CREATE FUNCTION HELLO(NAME VARCHAR2)   2    RETURN VARCHAR2 AS   3  LANGUAGE JAVA   4    NAME 'Procedures.myMethod(java.lang.String)   5    return java.lang.String';   6  / Function created. 

To try out the function, we'll need to create a NAMES table and insert a couple of rows if it doesn't exist already from a previous example.

 CREATE TABLE NAMES(FIRST_NAME VARCHAR2(25),   LAST_NAME VARCHAR2(25)); INSERT INTO NAMES VALUES('John', 'Smith'); INSERT INTO NAMES VALUES('Maria', 'Garcia'); 

We can call the function from an SQL statement. For example, we can include it in the select list with a literal and query the dummy table DUAL.

 SQL> SELECT HELLO('John') FROM DUAL; HELLO('JOHN') ------------------------------------ Hello John Smith 

IN and IN OUT Parameters

The call specification allows us to specify whether a parameter is for input, output, or both IN, OUT, or IN OUT. If not specified, the default is IN. Although it is allowed, it is considered poor practice for a function to have OUT or IN OUT parameters. In Java, OUT or IN OUT parameters must be array types typically, a single element array of the appropriate type.

Let's add a method to our Procedures class that returns a string, using a parameter. We'll overload the existing myMethod() and create a new one that returns the results in a parameter instead of as a return value.

 public static void myMethod(String name, String [] retval) throws SQLException {    retval[0] = myMethod(name); } 

We'll publish it as a procedure as follows:

 SQL> CREATE PROCEDURE HELLO_PROC(NAME VARCHAR2, RETVAL OUT VARCHAR2)   2    AS   3  LANGUAGE JAVA   4    NAME 'Procedures.myMethod(java.lang.String,   5            java.lang.String[])';   6   7  / Procedure created. 

We can test this interactively in PL/SQL. We'll need to create the OUT variable.

 SQL> VARIABLE OUTVAR VARCHAR2(25); 

We call the procedure with the CALL command. Notice that we need to prefix the host variable with a semicolon.

 SQL> CALL HELLO_PROC('Maria', :OUTVAR); 

We print the output variable with a PRINT command.

 SQL> PRINT OUTVAR OUTVAR -------------------------------- Maria Garcia 

For More Information on Java Stored Procedures…

Java stored procedures are a sort of hybrid of PL/SQL and Java. The emphasis in this chapter on PL/SQL has been on using call specifications to make Java methods appear like PL/SQL procedures and functions to the database. Apart from a few restrictions described earlier (including, in particular, the fact that they must be static methods), there is nothing special about the Java methods used as stored procedures. To learn more about writing Java stored procedures you'll need to learn more about JDBC, which Chapters 8 and 9 cover in much more depth than what's been touched on here. Although the emphasis there is on using JDBC in standalone applications, the lessons learned there also apply to developing Java stored procedures.