“The views expressed in this article are the author’s own and do not necessarily reflect the views of Oracle.”

(For more resources on Oracle, see here.)

Introduction

This article is better understood by people who have some familiarity with Oracle database, SQL, PL/SQL, and of course Java (including JDBC). Beginners can also understand the article to some extent, because it does not contain many specifics/details. The article can be useful to software developers, designers and architects working with Java.

Oracle database provides a Java runtime in its database server process. Because of this, it is possible not only to store Java sources and Java classes in an Oracle database, but also to run the Java classes within the database server. Such Java classes will be ‘executed’ by the Java Virtual Machine embedded in the database server. The Java platform provided is J2SE-compliant, and in addition to the JVM, it includes all the Java system classes. So, conceptually, whatever Java code that can be run using the JREs (like Sun’s JRE) on the operating system, can be run within the Oracle database too.

Java stored procedure

The key unit of the Java support inside the Oracle database is the ‘Java Stored Procedure’ (that may be referred to as JSP, as long as it is not confused with JavaServer Pages). A Java stored procedure is an executable unit stored inside the Oracle database, and whose implementation is in Java. It is similar to PL/SQL stored procedures and functions.

Creation

Let us see an example of how to create a simple Java stored procedure. We will create a Java stored procedure that adds two given numbers and returns the sum.

The first step is to create a Java class that looks like the following:

public class Math
{
 public static int add(int x, int y)
 {
  return x + y;
 }
}

This is a very simple Java class that just contains one static method that returns the sum of two given numbers. Let us put this code in a file called Math.java, and compile it (say, by doing ‘javac Math.java‘) to get Math.class file.

The next step is to ‘load’ Math.class into the Oracle database. That is, we have to put the class file located in some directory into the database, so that the class file gets stored in the database. There are a few ways to do this, and one of them is to use the command-line tool called loadjava provided by Oracle, as follows:

loadjava -v -u scott/tiger Math.class

Generally, in Oracle database, things are always stored in some ‘schema’ (also known as ‘user’). Java classes are no exception. So, while loading a Java class file into the database, we need to specify the schema where the Java class should be stored. Here, we have given ‘scott’ (along with the password). There are a lot of other things that can be done using loadjava, but we will not go into them here.

Next, we have to create a ‘PL/SQL wrapper’ as follows:

SQL> connect scott/tiger
Connected.
SQL>
SQL> create or replace function addition(a IN number, b IN number) return number
 2 as language java name 'Math.add(int, int) return int';
 3 /

Function created.

SQL>

We have created the PL/SQL wrapper called ‘addition’, for the Java method Math.add(). The syntax is same as the one used to create a PL/SQL function/procedure, but here we have specified that the implementation of the function is in the Java method Math.add(). And that’s it. We’ve created a Java stored procedure! Basically, what we have done is, implemented our requirement in Java, and then exposed the Java implementation via PL/SQL.

Using Jdeveloper, an IDE from Oracle, all these steps (creating the Java source, compiling it, loading it into the database, and creating the PL/SQL wrapper) can be done easily from within the IDE.

One thing to remember is that, we can create Java stored procedures for Java static methods only, but not for instance methods. This is not a big disadvantage, and in fact makes sense, because even the main() method, which is the entry point for a Java program, is also ‘static’. Here, since Math.add() is the entry point, it has to be ‘static’. So, we can write as many static methods in our Java code as needed and make them entry points by creating the PL/SQL wrappers for them.

Invocation

We can call the Java stored procedure we have just created, just like any PL/SQL procedure/function is called, either from SQL or PL/SQL:

SQL> select addition(10, 20) from dual;

ADDITION(10,20)
---------------
       30
SQL>
SQL> declare
 2 s number;
 3 begin
 4 s := addition(10, 20);
 5 dbms_output.put_line('SUM = ' || s);
 6 end;
 7 /
SUM = 30

PL/SQL procedure successfully completed.

SQL>

Here, the ‘select’ query, as well as the PL/SQL block, invoked the PL/SQL function addition(), which in turn invoked the underlying Java method Math.add().

A main feature of the Java stored procedure is that, the caller (like the ‘select’ query above) has no idea that the procedure is indeed implemented in Java. Thus, the stored procedures implemented in PL/SQL and Java can be called alike, without requiring to know the language in which the underlying implementation is. So, in general, whatever Java code we have, can be seamlessly integrated into the PL/SQL code via the PL/SQL wrappers. Putting in other words, we now have more than one language option to implement a stored procedure – PL/SQL and Java. If we have any project where stored procedures are to be implemented, then Java is a good option, because today it is relatively easier to find a Java programmer.