This chapter provides an overview and examples of calling Java methods that reside in Oracle Database. It contains the following sections:
The type of the Java application determines how the client calls a Java method. The following sections discuss each of the Java application programming interfaces (APIs) available for creating a Java class, which can be loaded into the database and accessed by a client:
You can run Java stored procedures in the same way as PL/SQL stored procedures. Normally, in Oracle Database, a call to a Java stored procedure is a result of database manipulation, because the call is usually the result of a trigger or SQL data manipulation language (DML) call.
To call 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 returns a string:
Define a class, Hello
, as follows:
public class Hello { public static String world() { return "Hello world"; } }
Save the file as a Hello.java
file.
Compile the class on your client system using the standard Java compiler, as follows:
javac Hello.java
It is a good idea to specify the CLASSPATH
on the command line with the javac
command, especially when writing shell scripts or make files. The Java compiler produces a Java binary file, in this case, Hello.class
.
You need to determine the location at which this Java code must run. If you run Hello.class
on your client system, then it searches the CLASSPATH
for all the supporting core classes that Hello.class
needs for running. This search should result in locating the dependent classes in one of the following:
As individual files in one or more directories, where the directories are specified in the CLASSPATH
Within .jar
or .zip
files, where the directories containing these files are specified in the CLASSPATH
Decide on the resolver for the Hello
class.
In this case, load Hello.class
on the server, where it is stored in the database as a Java schema object. When you call the world()
method, the Oracle Java virtual machine (JVM) locates the necessary supporting classes, such as String
, using a resolver. In this case, the Oracle JVM uses the default resolver. The default resolver looks for these classes, first in the current schema, and then in PUBLIC
. All core class libraries, including the java.lang
package, are found in PUBLIC
. You may need to specify different resolvers. You can trace problems earlier, rather than at run time, by forcing resolution to occur when you use loadjava
.
Load the class on the server using loadjava
. You must specify the user name and password. Run the loadjava
command as follows:
loadjava -user scott/tiger Hello.class
Publish the stored procedure through a call specification.
To call a Java static
method with a SQL call, you need to publish the method with a call specification. A call specification defines the arguments that the method takes and the SQL types that it returns.
In SQL*Plus, connect to the database and define a top-level call specification for Hello.world()
as follows:
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.
Call the stored procedure, as follows:
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. 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 that are called with triggers or SQL and PL/SQL calls. Furthermore, JDeveloper can automate the task of writing call specifications.
The Java Native Interface (JNI) is a standard programming interface for writing Java native methods and embedding the JVM into native applications. The primary goal of JNI is to provide binary compatibility of Java applications that use platform-specific native libraries.
Oracle Database does not support the use of JNI in Java applications. If you use JNI, then your application is not 100 percent pure Java and the native methods require porting between platforms. Native methods can cause server failure, violate security, and corrupt data.
You can use SQLJ and Java Database Connectivity (JDBC) protocols to query the database from a Java client. Both protocols establish a session with a given user name and password on the database and run SQL queries against the database. The following table lists the protocols and their description:
This section covers the following topics:
JDBC is an industry-standard API developed by Sun Microsystems that lets you embed SQL statements as Java method arguments. JDBC is based on the X/Open SQL Call Level Interface (CLI) and complies with the SQL92 Entry Level standard. Each vendor, such as Oracle, creates its JDBC implementation by implementing the interfaces of the standard java.sql
package. Oracle provides the following JDBC drivers that implement these standard interfaces:
The JDBC Thin driver, a 100 percent pure Java solution that you can use for either client-side applications or applets and requires no Oracle client installation.
The JDBC Oracle Call Interface (OCI) driver, which you use for client-side applications and requires an Oracle client installation.
The server-side JDBC driver embedded in Oracle Database.
Using JDBC is a step-by-step process of performing the following tasks:
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
Carrying out 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 run time, require JDBC. However, in typical applications, this represents a minority of the SQL operations.
SQLJ offers an industry-standard way to embed any static SQL operation directly into the Java source code in one simple step, without requiring the multiple steps of JDBC. Oracle SQLJ complies with the X3H2-98-320 American National Standards Institute (ANSI) standard.
SQLJ consists of a translator, which is a precompiler that supports standard SQLJ programming syntax, and a run-time component. After creating your SQLJ source code in a .sqlj
file, you process it with the translator. The translator translates the SQLJ source code to standard Java source code, with SQL operations converted to calls to the SQLJ run time. In the Oracle Database SQLJ implementation, the translator calls a Java compiler to compile the Java source code. When your SQLJ application runs, the SQLJ run time calls JDBC to communicate with the database.
SQLJ also enables you to catch errors in your SQL statements before run time. JDBC code, being pure Java, is compiled directly. The compiler cannot detect SQL errors. On the other hand, when you translate SQLJ code, the translator analyzes the embedded SQL statements semantically and syntactically, catching SQL errors during development, instead of allowing an end user to catch them when running the application.
The following is an example of a JDBC code and a SQLJ code that perform a simple operation:
// Assume 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();
Assume that you have established a JDBC connection, conn
. Next, you need to do the following:
Define the Java variables, name
, id
, and salary
.
Create a PreparedStatement
instance.
You can use a prepared statement whenever values in 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. In the preceding example, these variables are assigned values using the pstmt.setInt()
and pstmt.setFloat()
methods. The first ? refers to the int
variable id
and is set to a value of 37115
. The second ? refers to the float
variable salary
and is set to a value of 20000
.
Run the query and return the data into a ResultSet
object.
Retrieve the data of interest from the ResultSet
object and print it. In this case, the ename
column. A result set usually contains multiple rows of data, although this example has only one row.
String name; int id=37115; float salary=20000; #sql {SELECT ename INTO :name FROM emp WHERE empno=:id AND sal>:salary}; System.out.println("Name is: " + name);
In addition to allowing SQL statements to be directly embedded in Java code, SQLJ supports Java host expressions, also known as bind expressions, to be used directly in the SQL statements. In the simplest case, a host expression is a simple variable, as in this example. However, more complex expressions are allowed as well. Each host expression is preceded by colon (:). This example uses Java host expressions, name
, id
, and salary
. In SQLJ, because of its host expression support, you do not need a result set or equivalent when you are returning only a single row of data.
Note:
All SQLJ statements, including declarations, start with the#sql
token.This section presents a complete example of a simple SQLJ program:
import java.sql.*; import sqlj.runtime.ref.DefaultContext; import oracle.sqlj.runtime.Oracle; #sql ITERATOR MyIter (String ename, int empno, float sal); public class MyExample { public static void main (String args[]) throws SQLException { Oracle.connect("jdbc:oracle:thin:@oow11:5521:sol2", "scott", "tiger"); #sql { INSERT INTO emp (ename, empno, sal) VALUES ('SALMAN', 32, 20000) }; MyIter iter; #sql iter={ SELECT ename, empno, sal FROM emp }; while (iter.next()) { System.out.println(iter.ename()+" "+iter.empno()+" "+iter.sal()); } } }
In the preceding example, you do the following:
Declare your iterators.
SQLJ uses a strongly-typed version of JDBC result sets, known as iterators. An iterator has a specific number of columns of specific data types. You must define your iterator types before using them, as in this example.
#sql ITERATOR MyIter (String ename, int empno, float sal);
This declaration results in SQLJ creating an iterator class, MyIter
. Iterators of type MyIter
can store results whose first column maps to a Java String
, second column maps to a Java int
, and third column maps to a Java float
. This definition also names the three columns as ename
, empno
, and sal
, to match the column names of the referenced table in the database. MyIter
is a named iterator.
Connect to the database.
Oracle.connect("jdbc:oracle:thin:@oow11:5521:sol2","scott", "tiger");
SQLJ provides the Oracle
class and its connect()
method accomplishes the following important tasks:
Registers the Oracle JDBC drivers that SQLJ uses to access the database, in this case, the JDBC Thin driver.
Opens a database connection for the specified schema, in this case, user scott
with password tiger
, at the specified URL. In this case, the URL points to host oow11
, port 5521
, and SID so12
.
Establishes this connection as the default connection for the SQLJ statements. Although each JDBC statement must explicitly specify a connection object, a SQLJ statement can either implicitly use a default connection or optionally specify a different connection.
Process a SQL statement. The following is accomplished:
Insert a row into the emp
table:
#sql {INSERT INTO emp (ename, empno, sal) VALUES ('SALMAN', 32, 20000)};
Instantiate and populate the iterator:
MyIter iter; #sql iter={SELECT ename, empno, sal FROM emp};
Access the data that was populated within the iterator.
while (iter.next()) { System.out.println(iter.ename()+" "+iter.empno()+" "+iter.sal()); }
The next()
method is common to all iterators and plays the same role as the next()
method of a JDBC result set, returning true
and moving to the next row of data, if any rows remain. You can access the data in each row by calling iterator accessor methods whose names match the column names. This is a characteristic of all named iterators. In this example, you access the data using the methods ename()
, empno()
, and sal()
.
SQLJ uses strong typing, such as iterators, instead of result sets. This enables the SQL instructions to be checked against the database during translation. For example, SQLJ can connect to a database and check your iterators against the database tables that will be queried. The translator will verify that they match, enabling you to catch SQL errors during translation that would otherwise not be caught until a user runs your application. Furthermore, if changes are subsequently made to the schema, then you can determine if these changes affect the application by rerunning the translator.
Integrated development environments (IDEs), such as Oracle JDeveloper, can translate, compile, and customize your SQLJ program as you build it. Oracle JDeveloper is a Microsoft Windows-based visual development environment for Java programming. If you are not using an IDE, then use the front-end SQLJ utility, sqlj
. You can run it as follows:
%sqlj MyExample.sqlj
The SQLJ translator checks the syntax and semantics of your SQL operations. You can enable online checking to check your operations against the database. If you choose to do this, then you must specify an example database schema in your translator option settings. It is not necessary for the schema to have data identical to the one that the program will eventually run against. However, the tables must have columns with corresponding names and data types. Use the user option to enable online checking and specify the user name, password, and URL of your schema, as in the following example:
%sqlj -user=scott/tiger@jdbc:oracle:thin:@oow11:5521:sol2 MyExample.sqlj
Many SQLJ applications run on a client. However, SQLJ offers an advantage in programming stored procedures, which are usually SQL-intensive, to run on the server.
There is almost no difference between writing a client-side SQLJ program and a server-side SQLJ program. The SQLJ run-time packages are automatically available on the server. However, you need to consider the following:
There are no explicit database connections for code running on the server. There is only a single implicit connection. You do not need the usual connection code. If you are porting an existing client-side application, then you do not have to remove the connection code, because it will be ignored.
The JDBC server-side internal driver does not support auto-commit functionality. Use SQLJ syntax for manual commits and rollbacks of your transactions.
On the server, the default output device is a trace file, not the user screen. This is, normally, an issue only for development, because you would not write to System.out
in a deployed server application.
To run a SQLJ program on the server, presuming you developed the code on a client, you have two options:
Translate your SQLJ source code on the client and load the individual components, such as the Java classes and resources, on the server. In this case, it is easy to bundle them into a .jar
file first and then load them on the server.
Load your SQLJ source code on the server for the embedded translator to translate.
In either case, use the loadjava
utility to load the file or files to the server.
To convert an existing SQLJ client-side application to run on the server, after the application has already been translated on the client, perform the following steps:
Create a .jar
file for your application components.
Use the loadjava
utility to load the .jar
file on the server.
Create a SQL wrapper in the server for your application. For example, to run the preceding MyExample
application on the server, run the following statement:
CREATE OR REPLACE PROCEDURE sqlj_myexample AS LANGUAGE JAVA NAME `MyExample.main(java.lang.String[])';
You can then run sqlj_myexample
, similar to any other stored procedure.
All the Oracle JDBC drivers communicate seamlessly with Oracle SQL and PL/SQL, and it is important to note that SQLJ interoperates with PL/SQL. You can start using SQLJ without having to rewrite any PL/SQL stored procedures. Oracle SQLJ includes syntax for calling PL/SQL stored procedures and also lets you embed anonymous PL/SQL blocks in SQLJ statements.
Oracle Database furnishes a debugging capability that is useful for developers who use the jdb
debugger. The interface that is provided is the Java Debug Wire Protocol (JDWP), which is supported by Java Development Kit (JDK) 1.4 and later versions.
Some of the new features that the JDWP protocol supports are:
Listening for connections
Changing the values of variables while debugging
Evaluating arbitrary Java expressions, including method evaluation
Oracle JDeveloper provides a user-friendly integration with these debugging features. Other independent IDE vendors will be able to integrate their own debuggers with Oracle Database.
You may want to write Java code that runs in a certain way on the server and in another way on the client. In general, Oracle does not recommend this. In fact, JDBC and SQLJ enable you to write portable code that avoids this problem, even though the drivers used in the server and client are different.
If you want to determine whether your code is running on the server, then use the System.getProperty()
method, as follows:
System.getProperty ("oracle.jserver.version")
The getProperty() method returns the following:
A String
that represents the Oracle Database release, if running on the server
null
, if running on the client
System.out
and System.err
print to the current trace files. To redirect the output to the SQL*Plus text buffer, use the following workaround:
SQL> SET SERVEROUTPUT ON SQL> CALL dbms_java.set_output(2000);
The minimum and default buffer size is 2,000 bytes and 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);
The output is printed at the end of the call.
Oracle Database 10g introduces new features for calling Java stored procedures and functions. In releases prior to Oracle Database 10g, calling Java stored procedures and functions from a database client required JDBC calls to the 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 to directly call static
Java stored procedures and functions. This new functionality is useful for general applications, but is particularly useful for Web services.
Classes for the simple API are located in the oracle.jpub.reflect
package. Import this package into the client-side code.
The following 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 call the method, 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[]
, can be passed as an argument.
As an optimization, parameter values can be represented as String
:
String sig = oracle.jpub.reflect.Client.getSignature(new Class[]{}); ... Client.invoke(conn, "oracle.sqlj.checker.JdbcVersion", "to_string",sig, new Object[]{});
The semantics of this API are different from the semantics for calling 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 the definer's rights.
Oracle Database 10g introduces the native Java interface, a new feature for calls to server-side Java code. It is a simplified application integration. Client-side and middle-tier Java applications can directly call Java in the database without defining a PL/SQL wrapper. The native Java interface uses the server-side Java class reflection capability.
In previous releases, calling Java stored procedures and functions from a database client required Java Database Connectivity (JDBC) calls to the associated PL/SQL wrappers. Each wrapper had to be manually published with a SQL signature and a Java implementation. This had the following disadvantages:
The signatures permitted only Java types that had direct SQL equivalents
Exceptions issued in Java were not properly returned
The JPublisher -java
option provides functionality to overcome these disadvantages. To remedy the deficiencies of JDBC calls to associated PL/SQL wrappers, the -java
option uses an API for direct invocation of static
Java methods. This functionality is also useful for Web services.
The functionality of the -java
option mirrors that of the -sql
option, creating a client-side Java stub class to access a server-side Java class, as opposed to creating a client-side Java class to access a server-side SQL object or PL/SQL package. The client-side stub class uses JPublisher code that mirrors the server-side class and includes the following features:
Methods corresponding to the public
static
methods of the server class
Two constructors, one that takes a JDBC connection and one that takes the JPublisher default connection context instance
At run time, the stub class is instantiated with a JDBC connection. Calls to the methods of the stub class result in calls to the corresponding methods of the server-side class. Any Java types used in these published methods must be primitive or serializable.
Figure 3-1 demonstrates a client-side stub API for direct invocation of static
server-side Java methods. JPublisher transparently takes care of stub generation.
You can use the -java
option to publish a server-side Java class, as follows:
-java=className
Consider the oracle.sqlj.checker.JdbcVersion
server-side Java class, with the following APIs:
public class oracle.sqlj.checker.JdbcVersion { ... public java.lang.String toString(); public static java.lang.String to_string(); ... }
As an example, assume that you want to call the following method on the server:
public String oracle.sqlj.checker.JdbcVersion.to_string();
Use the following command to publish JdbcVersion
for client-side invocation, using JPublisher:
% jpub -sql=scott/tiger -java=oracle.sqlj.checker.JdbcVersion:JdbcVersion Client
This command generates the client-side Java class, JdbcVersionClient
, which contains the following APIs:
public class JdbcVersionClient { ... public java.lang.String toString(long _handle); public java.lang.String to_string(); ... }
All static methods are mapped to instance methods in the client-side code. A instance method in the server-side class, toString()
for example, is mapped to a method with an extra handle. A handle represents an instance of oracle.sqlj.checker.JdbcVersion
in the server. The handle is used to call the instance method on the server-side.
See Also:
Oracle Database JPublisher User's Guide