July 28, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Oracle and Java Stored Procedures

  • April 8, 2004
  • By Michael Klaene
  • Send Email »
  • More Articles »

Beginning with version 8i, the Oracle database includes a fully functional Java Virtual Machine, Oracle JVM. Out of this close relationship between Oracle and Java emerges an important technology for Oracle developers: Java stored procedures. With Java stored procedures, developers have the ability to harness the power of Java when building database applications. This article provides an overview of this increasingly popular technology. Its intended audience is Java developers new to Oracle, and Oracle PL/SQL developers with a basic understanding of Java. After highlighting the benefits of Java stored procedures, I will demonstrate how to create and use them in your applications.

Benefits of Java Stored Procedures

There are a number of scenarios where it makes sense to use Java stored procedures. Given Java's popularity today, it is certainly possible that members of a development team are more proficient in Java than PL/SQL. Java stored procedures give Java programmers the ability to code in their preferred language. For experienced PL/SQL developers, using Java allows you to take advantage of the Java language to extend the functionality of database applications. Also, Java makes it possible to write database-neutral code. Better yet, it allows you to reuse existing code and dramatically increase productivity.

As you'll see, PL/SQL and Java can coexist quite nicely in the same application so there is no need to pick one or the other. PL/SQL is an excellent procedural language, highly optimized to work with an Oracle database. Java applications that execute in the Oracle database are also highly scalable. In addition, Java executed by the Oracle JVM benefits from efficient garbage collection and the server's thread management capabilities.

Java Stored Procedures, Step by Step

In a nutshell, Java stored procedures are Java classes, stored as Oracle schema objects, 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. There are four necessary steps when developing a Java stored procedure. We will consider each of these steps now.

#1. Writing the Java Class

The beauty of this first step is that it really has little to do with the Oracle database. You simply develop your Java classes using your favorite IDE, such as Oracle's JDeveloper. Java methods must be public and they must be static if they are to be used as stored procedures.

You can write, compile, and even unit test your Java code before moving it into the Oracle database. In fact, for all but trivial applications, this is the preferred method because it will allow you to take advantage of your IDE's features, such as debugging and code generation. If you would prefer to compile your Java classes with Oracle's JVM, the loadjava utility, discussed later, can do this for you.

The following listing displays a simple Java class called EmpManager. For now, it contains a single method to insert an emp record into the database.

import java.sql.*;
import oracle.jdbc.*;

public class EmpManager {

   //Add an employee to the database.
   public static void addEmp(int emp_id, String emp_f_name, 
      String emp_l_name,float emp_salary, int dept_id) {

      System.out.println("Creating new employee...");

      try {
         Connection conn =
            DriverManager.getConnection("jdbc:default:connection:");

         String sql =
            "INSERT INTO emp " +
            "(emp_id,emp_f_name,emp_l_name,emp_salary,dept_id) " +
            "VALUES(?,?,?,?,?)";
         PreparedStatement pstmt = conn.prepareStatement(sql);
         pstmt.setInt(1,emp_id);
         pstmt.setString(2,emp_f_name);
         pstmt.setString(3,emp_l_name);
         pstmt.setFloat(4,emp_salary);
         pstmt.setInt(5,dept_id);
         pstmt.executeUpdate();
         pstmt.close();
         }
      catch(SQLException e) {
         System.err.println("ERROR! Adding Employee: " 
           + e.getMessage());
         }
   }
}

There is nothing out of the ordinary here. Well, almost nothing. 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 and provides the fastest access to the database.

#2. Loading Classes

Our Java class is to become a full-fledged schema object, so it needs to be moved into the database. Oracle provides a command-line utility called loadjava to accomplish this task. The loadjava utility essentially provides an interface for SQL CREATE JAVA statements, which also may be used to migrate Java-related files to the database.

Because we've yet to compile EmpManager.java, we'll ask loadjava to do this as part of the loading process. This is achieved by specifying the -resolve attribute on the utility.

$ loadjava -u scott/tiger -v -resolve EmpManager.java

In addition to the -resolve attribute, the -v instructs the utility to include verbose feedback, and the -u specifies the database user and password. Because we asked loadjava to compile the source file, both the source and class files become members of the SCOTT schema.

We can verify the status of the compilation and load with a simple query against USER_OBJECTS. If done correctly, the status is 'VALID'.

SELECT object_name, object_type, status
FROM user_objects WHERE object_type LIKE 'JAVA%';

object_name object_type status

EmpManager JAVA CLASS  VALID
EmpManager JAVA SOURCE VALID

Conversely, if compilation fails, errors can be examined through the USER_ERRORS view.

If you choose to compile with an IDE, simply load the resulting class file. Then, the source can reside in version control on the file system. The loadjava utility accepts files with the extensions .sqlj (a sqlj source file), .properties, .ser, .jar, and .zip. In the case of .jar and .zip files, Oracle automatically extracts and stores each member as individual schema objects.

Before we move on, there's one more critical component to the load process that merits discussion: the Oracle JVM resolver. Typically, a JVM uses a classpath to locate Java classes that your program depends upon to run correctly. When storing Java in the database, a resolver accomplishes this.

You can simply think of a resolver as Oracle's version of classpath. Oracle stores core Java classes in the PUBLIC schema. PUBLIC, as well as your own schema, is automatically included in a default resolver. However, if you need to reference a class from another schema, you must provide your own 'resolver spec'. This is done by adding the -resolver attribute. As an example, loadjava -u scott/tiger@test -resolve -resolver "((* SCOTT) (* PUBLIC) (* ADMIN))" specifies that the SCOTT schema, as well as PUBLIC and ADMIN, should be searched when resolving class dependencies.





Page 1 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel