3
Invoking Java in the Database

This chapter gives you an overview and examples of how to invoke Java within the database.

Overview

In Oracle Database, you utilize Java in one of the following ways:

Invoking Java Methods--Invoke Java methods in classes that are loaded within the database, such as Java stored procedures.
Utilizing JDBC for Querying the Database--You can query the database from a Java client through utilizing JDBC.

We recommend that you approach Java development in Oracle Database incrementally, building on what you learn at each step.

You should master the process of writing simple Java stored procedures, as explained in "Preparing Java Class Methods for Execution". This includes writing the Java class, deciding on a resolver, loading the class into the database, and publishing the class.
You should understand how to access and manipulate SQL data from Java. Most Java server programs, and certainly Java programs executing on Oracle Database, interact with database-resident data. The standard API for accomplishing this is JDBC.

Java is a simple, general purpose language for writing stored procedures. JDBC allow Java to access SQL data. They support SQL operations and concepts, variable bindings between Java and SQL types, and classes that map Java classes to SQL types. You can write portable Java code that can execute on a client or a server without change. With JDBC, the dividing line between client and server is usually obvious--SQL operations happen in the server, and application program logic resides in the client.

As you write more complex Java programs, you can gain performance and scalability by controlling the location where the program logic executes. You can minimize network traffic and maximize locality of reference to SQL data. JDBC furnishes ways to accomplish these goals. However, as you tend to leverage the object model in your Java application, a more significant portion of time is spent in Java execution, as opposed to SQL data access and manipulation. It becomes more important to understand and specify where Java objects reside and execute in an Internet application.

Invoking Java Methods

The way your client calls a Java method depends on the type of Java application. The following sections discuss each of the Java APIs available for creating a Java class that can be loaded into the database and accessed by your client:

Utilizing Java Stored Procedures

You execute Java stored procedures similarly to PL/SQL. Normally, calling a Java stored procedure is a by-product of database manipulation, because it is usually the result of a trigger or SQL DML call.

To invoke a Java stored procedure, you must publish it through a call specification. The following example shows how to create, resolve, load, and publish a simple Java stored procedure that echoes "Hello world".

Write the Java class.

Define a class, Hello, with one method, Hello.world(), that returns the string "Hello world".

public class Hello
{
   public static String world ()
   {
      return "Hello world";
   }
}

Compile the class on your client system. Using the Sun Microsystems JDK, for example, invoke the Java compiler, javac, as follows:
```
javac Hello.java
```
Normally, it is a good idea to specify your CLASSPATH on the javac command line, especially when writing shell scripts or make files. The Java compiler produces a Java binary file--in this case, Hello.class.

Keep in mind where this Java code will execute. If you execute Hello.class on your client system, it searches the CLASSPATH for all supporting core classes it must execute. This search should result in locating the dependent class in one of the following:
- as an individual file in a directory, where the directory is specified in the CLASSPATH
- within a .jar or .zip file, where the directory is specified in the CLASSPATH
Decide on the resolver for your class.

In this case, you load Hello.class in the server, where it is stored in the database as a Java schema object. When you execute the world() method of the Hello.class on the server, it finds the necessary supporting classes, such as String, using a resolver--in this case, the default resolver. The default resolver looks for classes in the current schema first and then in PUBLIC. All core class libraries, including the java.lang package, are found in PUBLIC. You may need to specify different resolvers, and you can force resolution to occur when you use loadjava, to determine if there are any problems earlier, rather than at runtime. Refer to "Resolving Class Dependencies" or Chapter 11, "Schema Object Tools" for more details on resolvers and loadjava.
Load the class on the Oracle Database server using loadjava. You must specify the user name and password.
```
loadjava -user scott/tiger Hello.class
```
Publish the stored procedure through a call specification.

To invoke a Java static method with a SQL CALL, you must publish it with a call specification. A call specification defines for SQL which arguments the method takes and the SQL types it returns.

In SQL*Plus, connect to the database and define a top-level call specification for Hello.world():
```
SQL> connect scott/tiger
connected
SQL> create or replace function HELLOWORLD return VARCHAR2 as
  2  language java name 'Hello.world () return java.lang.String';
  3  /
Function created.
```
Invoke the stored procedure.
```
SQL> variable myString varchar2(20);
SQL> call HELLOWORLD() into :myString;
Call completed.
SQL> print myString;

MYSTRING
---------------------------------------
Hello world

SQL>
```
The call HELLOWORLD() into :myString statement performs a top-level call in Oracle Database. The Oracle-specific select HELLOWORLD from DUAL also works. Note that SQL and PL/SQL see no difference between a stored procedure that is written in Java, PL/SQL, or any other language. The call specification provides a means to tie inter-language calls together in a consistent manner. Call specifications are necessary only for entry points invoked with triggers or SQL and PL/SQL calls. Furthermore, JDeveloper can automate the task of writing call specifications.

For more information on Java stored procedures, using Java in triggers, call specifications, rights models, and inter-language calls, see Chapter 5, "Developing Java Stored Procedures".

Utilizing Java Native Interface (JNI) Support

The Java Native Interface (JNI) is a standard programming interface for writing Java native methods and embedding the Java virtual machine into native applications. The primary goal of JNI is to provide binary compatibility of Java applications that use platform-specific native libraries.

Oracle does not support the use of JNI in Oracle Database Java applications. If you use JNI, your application is not 100% pure Java, and the native methods require porting between platforms. Native methods have the potential for crashing the server, violating security, and corrupting data.

Utilizing JDBC for Querying the Database

You can use JDBC protocols for querying the database from a Java client. This establishes a session with a given user name and password to the database and executes SQL queries against the database.

Protocol	Description
JDBC	Use this protocol for more complex or dynamic SQL queries. JDBC requires you to establish the session, construct the query, and so on.

JDBC

JDBC is an industry-standard API developed by Sun Microsystems that allows you to embed SQL statements as Java method arguments. JDBC is based on the X/Open SQL Call Level Interface and complies with the SQL92 Entry Level standard. Each vendor, such as Oracle, creates its JDBC implementation by implementing the interfaces of the Sun Microsystems java.sql package. Oracle offers three JDBC drivers that implement these standard interfaces:

The JDBC Thin driver, a 100% pure Java solution you can use for either client-side applications or applets and requires no Oracle client installation.
The JDBC OCI drivers, which you use for client-side applications and requires an Oracle client installation.
The server-side JDBC driver embedded in the Oracle Database server.

For the developer, using JDBC is a step-by-step process of creating a statement object of some type for your desired SQL operation, assigning any local variables that you want to bind to the SQL operation, and then executing the operation. This process is sufficient for many applications but becomes cumbersome for any complicated statements. Dynamic SQL operations, where the operations are not known until runtime, require JDBC. In typical applications, however, this represents a minority of the SQL operations.

An Example

The following is an example of a simple operation in JDBC code.

JDBC:

// (Presume you already have a JDBC Connection object conn)
// Define Java variables
String name;
int id=37115;
float salary=20000;

// Set up JDBC prepared statement.
PreparedStatement pstmt = conn.prepareStatement
   ("select ename from emp where empno=? and sal>?");
pstmt.setInt(1, id);
pstmt.setFloat(2, salary);

// Execute query; retrieve name and assign it to Java variable.
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
   name=rs.getString(1);
   System.out.println("Name is: " + name);
}

// Close result set and statement objects.
rs.close()
pstmt.close();

Define the Java variables name, id, and salary.
Define a prepared statement (this presumes you have already established a connection to the database so that you can use the prepareStatement() method of the connection object).

You can use a prepared statement whenever values within the SQL statement must be dynamically set. You can use the same prepared statement repeatedly with different variable values. The question marks in the prepared statement are placeholders for Java variables and are given values in the pstmt.setInt() and pstmt.setFloat() lines of code. The first "?" is set to the int variable id (with a value of 37115). The second "?" is set to the float variable salary (with a value of 20000).
Execute the query and return the data into a JDBC result set object. (You can use result sets to gather query data.)
Retrieve the data of interest (the name) from the result set and print it. A result set usually contains multiple rows of data, although this example has only one row.

Debugging Server Applications

Oracle Database furnishes a debugging capability that is useful for developers who use the JDK's jdb debugger. The interfaces that is provided is the Java Debug Wire Protocol, which is supported by JDK 1.3 and later versions of the Sun Microsystems JDB debugger (http://java.sun.com/j2se/1.3/docs/guide/jpda/, http://java.sun.com/j2se/1.4/docs/guide/jpda/.) The use of this interface is documented on OTN. The JDWP protocol supports many new features, including the ability to listen for connections (no more DebugProxy), change the values of variables while debugging, and evaluate arbitrary Java expressions, including method evaluation.

Oracle's JDeveloper provides a user-friendly integration with these debugging features. See the JDeveloper documentation for more information on how to debug your Java application through JDeveloper. Other independent IDE vendors will be able to integrate their own debuggers with Oracle Database.

How To Tell You Are Executing in the Server

You might want to write Java code that executes in a certain way in the server and another way on the client. In general, Oracle does not recommend this. In fact, JDBC goes to some trouble to enable you to write portable code that avoids this problem, even though the drivers used in the server and client are different.

If you must determine whether your code is executing in the server, use the System.getProperty method, as follows:

System.getProperty ("oracle.jserver.version")

The getProperty method returns the following:

If executing in the server, it returns a String that represents the Oracle Database release.
If executing on the client, it returns null.

Redirecting Output on the Server

System.out and System.err print to the current trace files. To redirect output to the SQL*Plus text buffer, use this workaround:

SQL> SET SERVEROUTPUT ON
SQL> CALL dbms_java.set_output(2000);

The minimum (and default) buffer size is 2,000 bytes; the maximum size is 1,000,000 bytes. In the following example, the buffer size is increased to 5,000 bytes:

SQL> SET SERVEROUTPUT ON SIZE 5000
SQL> CALL dbms_java.set_output(5000);

Output prints at the end of the call.

For more information about SQL*Plus, see the SQL*Plus User's Guide and Reference.

Support for Calling Java Stored Procedures Directly

Oracle Database 10g introduces new convenience features for calling Java stored procedures and functions.

In previous releases, calling Java stored procedures and functions from a database client required JDBC calls to associated PL/SQL wrappers. Each wrapper had to be manually published with a SQL signature and a Java implementation. This had the following disadvantages:

A separate step was required for publishing the SQL signatures for Java methods.
The signatures permitted only Java types with SQL equivalents.
Exceptions issued in Java were not properly returned.
Only a single method invocation could be performed for each database round trip.

To remedy these deficiencies, a simple API has been implemented for direct invocation of static Java stored procedures and functions. The new functionality is useful for Web services, but is more generally useful as well.

Classes for the simple API are located in the package oracle.jpub.reflect, so you must import this into the client-side code.

Here is the Java interface for the API:

public class Client
{
   public static String getSignature(Class[]);
   public static Object invoke(Connection, String, String, 
                               String, Object[]);
   public static Object invoke(Connection, String, String, 
                               Class[], Object[]);
}

As an example, consider a call to the following method in the server:

public String oracle.sqlj.checker.JdbcVersion.to_string();

You can now accomplish this as follows:

Connection conn = ...;
String serverSqljVersion = (String)
           Client.invoke(conn, "oracle.sqlj.checker.JdbcVersion",
           "to_string", new Class[]{}, new Object[]{});

The Class[] array is for the method parameter types and the Object[] array is for the parameter values. In this case, because to_string has no parameters, the arrays are empty.

Note the following:

Any serializable type (such as int[] and String[], for example) can be passed as an argument.

As an optimization, parameter values can be represented in a string:

String sig = oracle.jpub.reflect.Client.getSignature(new Class[]{});
... 
Client.invoke(conn, "oracle.sqlj.checker.JdbcVersion", "to_string",
             sig, new Object[]{});

(This is offered as a general note; in this example it is a moot point because to_string has no parameters.)

The semantics of this API are different than semantics for invoking stored procedures or functions through a PL/SQL wrapper, in the following ways:
- Arguments cannot be OUT or IN OUT. Returned values must all be part of the function result.
- Exceptions are properly returned.
- The method invocation uses invoker's rights. (There is no tuning to obtain definer's rights.)

3 Invoking Java in the Database