As of V8.i, Oracle developers can now write stored procedures, functions, packages and triggers in Java instead of PL/SQL (Oracle's proprietary procedural language), which provides some appealing options:

• We don't have to learn a proprietary (and thus limited-use) language to write stored objects for Oracle databases.
• We can get performance improvements over PL/SQL that make stored objects much more usable than they've been in the past.
• We can write code that's at least somewhat migratable to other database platforms should we wish to do so.

I'll provide guidelines and strategies for the effective use of Java within Oracle databases, and a brief overview of how to write stored procedures, functions, packages and triggers for Oracle Databases (V8.i) for readers who aren't familiar with these new features of Oracle. A basic knowledge of Java and Oracle database concepts (including SQL and the definitions of stored procedure, function, trigger and package) is assumed.

Java Stored Procedure Overview
Review of Capabilities
Stored objects written in Java use JDBC to access Oracle databases the same way Java programs outside the database do. It's also common to embed SQLJ (a preprocessor that inserts generated JDBC code in your program) within Java stored procedures. The only coding difference with Java stored procedures is a change in how we initiate a database connection. There are also a couple of utilities that define Java programs to an Oracle database as well as changes to the CREATE PROCEDURE, FUNCTION, PACKAGE BODY and TRIGGER statements. If you're familiar with the capabilities of PL/SQL, stored objects in Java can do anything PL/SQL can do.

Oracle JVM Specifics
Because of Aurora's tight integration with the Oracle database kernel, it isn't pluggable. No upgrade to Java 1.2 was available at the time of this writing (though I'd expect one at some point).

Loadjava and dropjava Utilities
The two-step process to define Java stored objects is (1) load the Java class, and (2) expose its methods. Java classes are loaded into the database by either a CREATE JAVA statement or via the loadjava utility, which is typically executed from an operating system command prompt. In reality, the loadjava utility issues a CREATE JAVA statement behind the scene. I find the loadjava utility easier to use.

As an illustration, I've written a short class that'll determine a unique number for an identifier field of a table. Frequently, in Oracle-based applications, sequences that generate unique numbers are used to generate a unique number for use as a key field in a table. Unfortunately, the sequence that generates the number has no formal association with the field in the table using the number. This means programmers have to check for the possibility that the generated number isn't unique. My program centralizes this logic in one class so no one else has to code it (see Listing 1).

Java classes can be loaded as source, class or JAR files. Java source is compiled by the JVM in the Oracle database engine. The ability to load class and JAR files is nice because we can conceivably load purchased components (provided they aren't GUI components) into the database. An example of a loadjava statement follows:

loadjava -u derek/[email protected]:1521:ORA81a -thin -v -f -r -t
OracleProcs.java

The -v option produces detailed messages about the steps loadjava is going through to compile and load my Java class. The -f forces the loading of this Java class even though it's already present, which means I don't have to issue a dropjava command first. The -u option specifies the connection string in thin-driver format for the database in which this class is being loaded. The -r option designates that all external references are to be resolved at load-time instead of runtime. The -t option designates that the "thin" JDBC drivers are to be used for any database communication during the load process. The last argument specifies my Java source.

Similarly, I can remove my class with the dropjava utility. The command arguments are similar. The -v and -u options mean the same thing as with the loadjava utility. An example follows:

dropjava -u derek/[email protected]:1521:ORA81a -v -t OracleProcs

Once the classes have been loaded, we must expose individual methods with CREATE PROCEDURE, FUNCTION and PACKAGE BODY statements. It should be noted that the Java language libraries, JDBC libraries and ORB class libraries are already present in the database. No need to load them again.

CREATE PROCEDURE, FUNCTION, PACKAGE BODY and TRIGGER Statements
While the loadjava utility will associate Java classes with the database, none of the methods associated with those classes are callable until you register them. Methods of Java classes are registered (or "wrapped") by issuing CREATE PROCEDURE, FUNCTION or PACKAGE BODY statements. After registration, they can be called in the same manner as PL/SQL procedures, functions and packages.

An example of a CREATE FUNCTION statement that registers a Java method is presented below:

create or replace function getID(
TableName varchar2,
ColumnName varchar2,
SequenceName varchar2)
return number
as language java
name 'OracleProcs.getUniqueIdentifier(java.lang.String, java.lang.String, java.lang.String)
return double';
/

The "AS LANGUAGE JAVA" clause also works with CREATE PROCEDURE and CREATE PACKAGE BODY statements.

Note: You must fully qualify the argument passed if it isn't a native Java data type. As many of you know, strings aren't a native data type in the Java language. The definition of a string is obtained from the Java.lang import library. Hence, we must fully qualify the object type being passed.

Surprisingly, only a few alterations are needed to define a Java program as a stored object under V8.i. All Java stored objects use JDBC for database access.

Java Requirements
All Java methods executed from a database connection must be declared as static. This makes sense as these methods are essentially being invoked from outside the JVM, and hence must be runnable (as method main always is). Once invoked, methods can instantiate nonstatic objects and use them, but these object allocations disappear after the method completes.

If you wish to retain information within the Java class between method calls, you must store them in a static-defined variable.

Another major difference: within a Java stored object we initiate a JDBC connection differently. With stored procedures we'll typically use the connection created by the process that invoked the stored procedure as opposed to opening up a separate connection. An example of how to specify the default connection is:

Connection dbConnect = new OracleDriver().defaultConnection();

Issues to Be Considered When Introducing Java Stored Procedures
Consider the following issues before deploying Java stored procedures in your organization:

• Platform compatibility requirements for your applications
• Developer training
• System management and source control
• Software licensing
• Application performance

You need to identify the target Oracle database platform for your applications before migrating stored objects to Java. Java stored objects are new with V8.i; they're not supported in V8.0.5 and earlier. Applications that need to support earlier versions of Oracle software won't be able to migrate immediately.

As Java stored procedures use standard JDBC to issue SQL statements, Java is easier to migrate to other database platforms should that become necessary. I have a number of clients who would like to migrate an application from one database platform to another but can't because stored procedures are written in a proprietary, nonportable language. Stored procedures written in Java have a significant chance of being portable to a non-Oracle platform without a complete rewrite.

Developer training is usually a significant issue when adopting new technologies. For shops already developing in Java, introducing Java stored objects would be easy and inexpensive. No additional training would be necessary. For shops that don't use Java, training costs could be significant, but comparable to other languages. In addition, database administrators would also have to learn Java at a basic level in order to provide developer support.

Many Oracle environments use object ownership to distinguish between environments. It's common to define testing tables and indexes using one user ID and to create a development environment using another. For example, user DEV might own our development tables, indexes, and so on, while user TEST owns our testing environment in the same database. Oracle's JVM, like other JVMs, doesn't have a native ownership/object security model. Only one version of a class can be present in Oracle's JVM, so I can't have a development and testing version of the same class in a database. These environments must now be separated into different databases. Robust source control procedures are needed with Java stored procedures to avoid conflicts.

There's a software licensing issue with using Java stored procedures. At the time of this writing, Oracle's JVM was licensed separately and has its own cost component. From a strictly technical point of view, writing stored procedures in Java instead of PL/SQL has many advantages. However, the cost of Oracle's JVM may not be worth the benefits for some applications.

Performance Issues
Java stored procedures are much faster than PL/SQL. My tests indicated that Java stored procedures that do ordinary SQL statements, such as selects, updates, inserts and deletes, can be improved 20-40% if written in Java instead of PL/SQL. Additionally, procedures written in Java that don't issue SQL statements execute nine or 10 times faster than PL/SQL.

Java outperformed PL/SQL by 20-40% for SQL operations by allowing more flexible array processing and write batching. To get a simple SQL operation test, I wrote a Java and PL/SQL procedure to select and loop through all object names in the DBA_OBJECTS system view. For those that are interested, DBA_OBJECTS identified all objects existing in an Oracle database. At the time of my test there were 11,668 objects in my database.

Out of curiosity, I wrote the method to take the array size as an argument. Oracle allows array processing on select statements; array processing allows Oracle to retrieve rows in batches (e.g., 100 at a time) for efficiency. PL/SQL doesn't support array processing. Oracle's JDBC drivers set the array size to 10 by default. The Java source for this method can be found in Listing 2. The PL/SQL source for this method can be found in Listing 3. My results are in Listing 4.

The results show that by default (array size of 10) Java was about 18% faster than PL/SQL. However, if you employ array processing (which is easy to do with Java), you can get significant performance improvements for read operations.

As an aside, there are diminishing returns to increasing the array size - performance improves more if the array size is increased from 1 to 10 than from 100 to 200. To set the array size, use the setDefaultRowPrefetch method of the OracleConnection class. An example of how to do this is contained in Listing 1.

Strategies for Effectively Using Java Stored Procedures
Once you've decided to write stored procedures in Java instead of Oracle's native PL/SQL, you need to decide which Java classes should be deployed as stored procedures and which should be deployed normally (as part of an application, applet, CORBA service, etc.). The issues to think about when deciding whether to use Java stored procedures are:

• JVM currency
• Application design
• Performance

Oracle's JVM, Aurora, is currently in V1.1.6 and not pluggable. Java deployed as a stored procedure won't have access to features in later releases of the JVM (e.g., V1.2.x). While I'd expect Oracle to keep Aurora relatively current, it'll never be at the same level as the latest and greatest Java release.

From an application-design point of view, the fact that all methods called from a database connection need to be declared "static" tends to limit the role of Aurora to that of a "function loader." A class in this context is just an arbitrary collection of methods. While you can instantiate and use classes within a method call, any memory you allocate won't be available for future method calls. If you wish to retain information for future method calls, you must store this information in a statically defined and allocated variable. With this restriction it's hard to keep a purely object-oriented design for this section of the application.

Usage Guidelines
In my experience, Java code deployed as a normal application outside Aurora performs four to eight times faster than Java deployed as a stored procedure.

Because of the various performance issues involved, I tend to deploy Java code outside the database as part of an application, applet, servlet, CORBA service, and so on. However, I do use Java stored procedures to implement the following items:

• Database triggers
• Custom SQL column functions

Database triggers execute code when INSERT, UPDATE or DELETE statements are issued. They're defined on a per-table basis. Triggers are used to enforce business rules that the database can't enforce via referential integrity constraints. For instance, I use triggers to execute the unique identifier generator that was reviewed in the first section of the article. An example of such a trigger definition follows:

create or replace trigger BEER_TR
before insert on beer
for each row
when (new.beer_id is null)
begin
:new.beer_id :=
getID('beer',
'beer_id',
'beer_seq');
end;
/

Another place that Java stored procedures can be used effectively is in custom-column functions. Most developers are familiar with COUNT, SUM, AVERAGE and other native column functions that most databases provide. Using Oracle, it's possible to write custom column functions. I've used it in the past to format numbers (such as 999) into a currency format (such as $999.00).

Note: As Oracle Corporation is constantly tweaking its products, my usage guidelines for Java stored procedures may change for future versions of Oracle.

• Source Code