"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.)
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.
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
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';
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.
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;
2 s number;
4 s := addition(10, 20);
5 dbms_output.put_line('SUM = ' || s);
SUM = 30
PL/SQL procedure successfully completed.
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.
(For more resources on Oracle, see here.)
Interaction among SQL, PL/SQL and Java
So far, we have seen how to create a stored procedure implemented in Java, and how to invoke it from SQL or PL/SQL. The integration among these languages is much more possible because we can do the other way round also - invoke SQL statements or PL/SQL code from Java. This can be done using JDBC. We don't have enough scope to discuss this further here, but those who are familiar with the basics of JDBC programming would know how to invoke a SQL statement or call a PL/SQL procedure/function from Java. Moreover, Oracle provides a server-side internal JDBC driver, which can be used by the Java programs running inside the database server, for faster access to the data within that database. So, the picture is:
The Java stored procedure example we have seen is simple but good enough to illustrate conceptually how these languages (SQL, PL/SQL and Java) can work together.
What can we do with Java in Database?
There are several things we can do by having our Java code inside the Oracle database. We will discuss a few ideas here conceptually, without going into the particulars.
Reduce round-trips: In general, we can reduce the number of round-trips between the middle-tier and the database (in a typical 3-tier architecture) by putting all the relevant Java code in the database itself. With one request from the middle-tier, the Java code in the database can process the request, derive the required data from existing data in the database, and then send it back to the middle-tier, perhaps in some required format.
Triggers: Another useful way of using Java in the database is to have a Java stored procedure underneath a trigger. For instance, if we want to send some message, perhaps encrypted, to external clients, whenever a new row is inserted into a particular table, or whenever a row is deleted from a particular table, then we can do this message sending part from Java, which is relatively easier.
Database as client: Oracle database provides a pure Java JDBC driver in the server, also called 'server-side thin driver'. Since we have such a driver inside the database, we can turn our Oracle database into a 'client' to another Oracle database, in the sense, the Java code residing in one Oracle database can use the database-resident JDBC driver to connect to, and access, another Oracle database, or possibly multiple Oracle databases. The Java code in an Oracle database can even access a non-Oracle database. All we have to do for this is, to load a pure Java JDBC driver for such a non-Oracle database into the Oracle database, and make the Java code in the Oracle database use that driver to access the non-Oracle database.
Web services: With the Java support inside the Oracle database, we can make the database a provider of web services as well as a consumer of web services. To make the database a web service consumer, we can load the web service Java client proxy, along with the Java code on top of it, into the database. To help achieve this, there are tools like Oracle’s JPublisher that generate the necessary web service client side code in Java, from a given WSDL URL. It is also possible to consume the web service from PL/SQL by means of the PL/SQL wrappers written on top of the Java client. We can also consume the web service by loading the web service Java clients, that use Dynamic Invocation Interface, into the database. In the other way round, the Oracle database can be used as the back-end for a web service implementation. For instance, we can implement a web service in Java, load it into the database, write a PL/SQL wrapper, and invoke the PL/SQL wrapper via JDBC from the Application server.
Features of the Java support in Oracle database
There are several features of the Java platform provided by (and inside) an Oracle database. Let us see briefly some of them here:
J2SE compliance: Every release of Oracle database comes with the support for a particular version of J2SE. Java Compatibility Kit (JCK) tests are run for compatibility checking. For instance, Oracle database 11g supports J2SE 5.0, and the compliance is verified using JCK 5.
Efficient session-based model: Every database session has its own JVM, however, all the JVMs share the common read-only metadata (like method byte codes, and other internal memory structures representing a class). In this sense, n database sessions having their own JVMs are better than n JVMs running on the operating system. Also, it is possible for Java 'execution' in an RDBMS call to gain advantage in performance from the Java execution that happened in earlier RDBMS calls in the same session.
Enhanced performance: From Oracle database 11g, the JVM embedded inside the database server does JIT compiling of selected methods at run time, thereby making the Java 'execution' much faster.
Internal Java compiler: Just as we can store Java classes inside the database, we can store Java sources also. For instance, we can load .java files into the database by using the loadjava tool mentioned earlier. Such Java sources can be compiled explicitly, or they get compiled implicitly when needed. In either case, an internal Java compiler is used for compiling the Java sources.
JMX support: We can start a JMX agent inside the database server, and connect to it using a JMX client like jconsole, and get various kinds of information, like what classes are currently loaded, what threads are running, what is the status of Java heap, etc.
Default security manager: The Java runtime in Oracle database server always has a security manager installed, which is consulted before certain operations are carried out, like writing to a file, reading from a socket, creating a class loader, etc.
In addition to the above, many other things that can usually be done on JREs running on the operating system can also be done inside the Oracle database. For instance, we can set various Java system properties. The PL/SQL package DBMS_JAVA that comes with the database server provides several such functionalities.
What is not supported?
Since the Java runtime exists inside the Oracle database server process, there are certain things that are inherently not supported.
JNI libraries: We cannot load JNI libraries by System.loadLibrary(), for security reasons. From the security perspective, allowing Java code to run inside the database server is quite different from allowing native code, because in case of Java, the JVM itself provides some safety for the runtime. For instance, the JVM simply throws a NullPointerException if some Java code accesses a null reference, whereas the database server process could crash if some native code (that is part of a JNI library) accesses a null pointer.
AWT: There is no full support for AWT. Instead, what is supported is Headless AWT. It does not make sense anyway, for instance, to open up some window in the database server. However, any computations regarding the graphics manipulation are allowed.
In this article, we have familiarized ourselves with various things that Oracle database offers by means of an embedded Java runtime, how Java stored procedures can be used to extend the PL/SQL world to Java, and how SQL, PL/SQL and Java can work with each other. There are many details that are not covered here. This article just serves as a portal, and anyone who is interested more in a particular feature or usage can continue from here.
- Oracle JRockit: The Definitive Guide [Book]
- Fine Tune the View layer of your Fusion Web Application [Article]
- Debugging Java Programs using JDB [Article]