Skip Headers

Oracle® Database Java Developer's Guide
10g Release 1 (10.1)

Part Number B12021-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

3 Invoking Java in the Database

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

3.1 Overview

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

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

  1. 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.

  2. 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 two standard APIs for accomplishing this is JDBC and SQLJ. Because JDBC forms the foundation for SQLJ, you should understand how the two work together, even though you might be using only SQLJ in your code.

Java is a simple, general purpose language for writing stored procedures. JDBC and SQLJ 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 and SQLJ, 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 and SQLJ 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.

3.2 Invoking Java Methods


3.2.1 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.


  1. Write the Java class.


    public class Hello
       public static String world ()
          return "Hello world";
  2. Compile the class on your client system. Using the Sun Microsystems JDK, for example, invoke the Java compiler, javac, as follows:


    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.




  3. 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.

  4. Load the class on the Oracle Database server using loadjava. You must specify the user name and password.

    loadjava -user scott/tiger Hello.class
  5. Publish the stored procedure through a call specification.



    SQL> connect scott/tiger
    SQL> create or replace function HELLOWORLD return VARCHAR2 as
      2  language java name ' () return java.lang.String';
      3  /
    Function created.
  6. Invoke the stored procedure.

    SQL> variable myString varchar2(20);
    SQL> call HELLOWORLD() into :myString;
    Call completed.
    SQL> print myString;
    Hello world

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".

3.2.2 Utilizing Java Native Interface (JNI) Support


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.

3.2.3 Utilizing SQLJ and JDBC for Querying the Database

You can use one of twoJDBC protocols for querying the database from a Java client. Both protocols establishThis establishes a session with a given user name and password to the database and executes SQL queries against the database.

Protocol Description



  2. The JDBC OCI drivers, which you use for client-side applications and requires an Oracle client installation.

  3. The server-side JDBC driver embedded in the Oracle Database server.


3.2.4 An Example Comparing JDBC and SQLJ




// Define Java variables

String name;

int id=37115;

float salary=20000;

// Set up JDBC prepared statement.

PreparedStatement pstmt = conn.prepareStatement


pstmt.setInt(1, id);

pstmt.setFloat(2, salary);


ResultSet rs = pstmt.executeQuery();

while ( {


   System.out.println("Name is: " + name);


// Close result set and statement objects.





    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).



3.3 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 (, 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.

3.4 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, JDBCand SQLJ 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:

3.5 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> 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> 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.

3.6 Support for Calling Java Stored Procedures Directly

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


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:

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