Java Stored Procedure in Oracle, Database Interaction

In this article, we will examine working with Java Stored Procedures in Oracle database.
The entire code has been tested with only Oracle 10g with version 10.2. It would also support any successive versions after Oracle 8i. But, I didn’t personally check all of those versions.
Stored Procedures being written in Java?
I contributed a very long series (about 20 parts) that covered database interaction with PL/SQL. I received good feedback from several readers from all parts of the world. Indeed with their request, I am still extending the same series (but with different titles).
I already introduced an article which explains what a stored procedure is. We also examined how to create, execute and drop stored procedures in an Oracle database. Now, we will examine a similar concept but turn a bit to Java. To follow this article, one should have at least a basic idea of Java (at least its syntax, compilation, and so on) together with a bit of PL/SQL knowledge. For a complete list of my PL/SQL articles, try searching through “igrep.”
Even though I am working on Oracle 10g (v10.2) now, right from version 8i, the Oracle database includes a fully functional Java Virtual Machine called “Oracle JVM.” Oracle and Sun have a long-term relationship for their applications and platform interoperability. One of the important technologies which came out of their growing relationship is “Java stored procedures.”
With that flexibility, suddenly many Java developers became something like “Oracle Developers for Java” or “Java Oracle developers.” Not only Java stored procedures, but Oracle has further integrated very tightly with almost all of the latest J2EE specification (including, SQL, JSP, EJB and so on). In fact, Oracle developed its own application server (Oracle Application Server or simple “10gAS”) to integrate with their other suites.
With Java stored procedures, developers have the ability to harness the power of Java when building database applications. Almost the entire core Java API can be used to develop stored procedures. And further they will be served as if they are “native” Oracle stored procedures (generally written in PL/SQL).
In this article, we will examine how to create our own Java stored procedure, right from scratch.
One should remember that Java stored procedures are still Java classes, but stored as Oracle schema objects. They will be made accessible to Oracle SQL and PL/SQL through call specifications. Call specifications, as we will see, are simply PL/SQL declarations that ‘wrap’ methods of Java stored in the database. Call specifications, in other words, work as mediators (or interfaces).
Another important issue to consider is that Java methods must be “public” and they must be “static” if they are to be used as stored procedures inside an Oracle database.
JDeveloper is the product from Oracle, which seems to be a famous IDE for Java developers who need Oracle based Java stored procedures to develop and deploy very easily. You can write, compile, and even unit test your Java code before moving it into the Oracle database. But in this article, you work with a simple notepad by saving the following code as “Employee.java”.
The following listing displays a simple Java class called “Employee”. For now, it contains a single method to “insert” an employee record into the database.
import java.sql.*;
import oracle.jdbc.*;
public class Employee {
//Add an employee to the database.
public static void addEmp(int empno, String ename,
float sal, int deptno) {
System.out.println(“Creating new employee…”);
try {
Connection conn =
DriverManager.getConnection(“jdbc:default:connection:”);
String sql =
“INSERT INTO emp ” +
“(empno,ename,sal,deptno) ” +
“VALUES(?,?,?,?)”;
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,empno);
pstmt.setString(2,ename);
pstmt.setFloat(3,sal);
pstmt.setInt(4,deptno);
pstmt.executeUpdate();
pstmt.close();
}
catch(SQLException e) {
System.err.println(“ERROR! Adding Employee: “
+ e.getMessage());
}
}
}
There is nothing much new in the above program. In this method, the database connection URL is “jdbc:default:connection:”. When writing Java that will execute inside the Oracle database, you can take advantage of a special server-side JDBC driver. This driver uses the user’s default connection (which is also called a context connection) and provides the fastest access to the database.
Java Stored Procedure in Oracle, Database Interaction – Getting Java Classes into Oracle Database

The simple Java class we created in the previous section needs to become a full-fledged schema object before it could be accessed, as if it is a native stored procedure. Oracle provides a command-line utility called “loadjava” to accomplish this task. The “loadjava” utility essentially provides an interface for SQL based CREATE JAVA statements, which also may be used to migrate Java-related files to the database.
We need not compile the previous program using the traditional “javac” compiler; “loadjava” does even that for us during its loading process. This is achieved by specifying the -resolve attribute on the utility.
$ loadjava -u scott/tiger -v -resolve Employee.java
The following commands would help you to know the status of what you created.
SELECT object_name, object_type, status
FROM user_objects WHERE object_type LIKE ‘JAVA%’;
You should attain the “VALID” in the output. Otherwise something is wrong with your program. If you receive any errors, you should query “USER_ERRORS” to view more information and correct them accordingly.
Java Stored Procedure in Oracle, Database Interaction – How to make Classes available to Oracle

Making Java classes available to Oracle can also be called “publishing Java classes.” Any class that will be directly called from SQL or PL/SQL must be published. Now we shall proceed with publishing.
A Java class is published by creating and compiling a call specification for it. The call specification is often referred to as a call spec or even a PL/SQL wrapper. It maps a Java method’s parameters and return type to Oracle SQL types. Here’s the call specification for the “addEmp” method:
CREATE OR REPLACE PROCEDURE addEmp (empno NUMBER,ename VARCHAR2,
sal NUMBER, deptno NUMBER)
AS LANGUAGE JAVA
NAME ‘Employee.addEmp(int, java.lang.String,
float, int)’;
/
The “addEmp” procedure provides an SQL interface to the Java “Employee.addEmp method”. The Java method must be fully specified, including package name if relevant. Also, when developing a call specification, Java objects such as String must be fully qualified.
One should make sure that Java methods with “no return” values are wrapped as procedures, and those with return values are wrapped as functions. Consider a second Java method in “Employee” that obtains a count of employees for a given department:
//Get the total number of employees for a given department.
public static int getEmpCountByDept(int deptno) {
Connection conn =
DriverManager.getConnection(“jdbc:default:connection:”);
String sql = “SELECT COUNT(1) FROM emp WHERE deptno = ?”;
int c = 0; //containing the count
.
.
.

return c;
}
Its call specification specifies that it returns a NUMBER.
CREATE OR REPLACE FUNCTION getEmpCountByDept (deptno NUMBER)
RETURN NUMBER AS LANGUAGE JAVA
NAME ‘Employee.getEmpCountByDept(int) return int’;
/
Once executed, call specifications join the other files in the database as members of the SCOTT schema.

Java Stored Procedure in Oracle, Database Interaction – How to execute the Java Stored Procedures

We have developed, loaded, and published our Java classes. The final step is to execute them. The following statements would make you see the Java based output.
SQL> SET SERVEROUTPUT ON
SQL> CALL dbms_java.set_output(2000);
SQL> EXECUTE add_emp(1,’Jag’,40000.00,1);
Creating new employee…
PL/SQL procedure successfully completed.
The first two statements are necessary one time (after the first login), because it allows the redirection of tracing.
As you can see, from the caller’s perspective, there is no difference between calls made to Java stored procedures and calls to a PL/SQL procedure or function.
VARIABLE x NUMBER;
CALL getEmpCountByDept(1) INTO ;
Getting Number of Employees for Dept…
Call completed.
PRINT x
X
———-
1
The SQLException class has the getErrorCode() and getErrorMessage() methods to help report errors. Any uncaught exception in a Java Stored Procedure results in an ‘ORA-29532 Java call terminated by uncaught Java exception’ for the caller. How you choose to handle errors will vary by application. The addEmp method simply catches and displays the error. We receive an error message when we attempt to add an emp record with an invalid dept_id.
SQL> execute addEmp(2,’aaa’, 45000.00,2);
Creating new employee…
ERROR! Adding Employee : ORA-02291: integrity constraint
(OPS$AK4353.FK_DEPT_ID) violated -
parent key not found
Because there is a need to call Java from PL/SQL, it is reasonable to assume that we will also need a way to call PL/SQL from Java code. This is very easy to achieve by using a CallableStatement object in our Java methods.
CallableStatement cstmt = conn.prepareCall(“{your procedure}”);
Thus, it is possible to create a seamless environment of PL/SQL procedures calling Java and vice versa.
Summary
Even though I examined only a stored procedure in the above scenario, you can even embed those Java stored procedures in PL/SQL Packages. Don’t consider only Java stored procedures; there also exists the concept of .NET based stored procedures (primarily called .NET CLR stored procedures). .NET based stored procedures are supported by both SQL Server 2005 and Oracle 10g Version 10.2. Remember, it is possible to create a seamless environment of PL/SQL procedures calling Java and vice versa.