5 Developing Java Stored Procedures

Oracle Java virtual machine (JVM) has all the features you need to build a new generation of enterprise-wide applications at a low cost. The most important feature is the support for stored procedures. Using stored procedures, you can implement business logic at the server level, thereby improving application performance, scalability, and security.

This chapter contains the following sections:

Stored Procedures and Run-Time Contexts

Stored procedures are Java methods published to SQL and stored in the database for general use. To publish Java methods, you write call specifications, which map Java method names, parameter types, and return types to their SQL counterparts.

Unlike a wrapper, which adds another layer of execution, a call specification publishes the existence of a Java method. As a result, when you call the method through its call specification, the run-time system dispatches the call with minimal overhead.

When called by client applications, a stored procedure can accept arguments, reference Java classes, and return Java result values. Figure 5-1 shows a stored procedure being called by various applications.

Figure 5-1 Calling a Stored Procedure

Calling a stored procedure
Description of "Figure 5-1 Calling a Stored Procedure"

Except for graphical user interface (GUI) methods, Oracle JVM can run any Java method as a stored procedure. The run-time contexts are:

Functions and Procedures

Functions and procedures are named blocks that encapsulate a sequence of statements. They are building blocks that you can use to construct modular, maintainable applications.

Generally, you use a procedure to perform an action and a function to compute a value. Therefore, you use procedure call specifications for void Java methods and function call specifications for value-returning methods.

Only top-level and package-level PL/SQL functions and procedures can be used as call specifications. When you define them using the SQL CREATE FUNCTION, CREATE PROCEDURE, or CREATE PACKAGE statement, they are stored in the database, where they are available for general use.

Java methods published as functions and procedures must be invoked explicitly. They can accept arguments and are callable from:

  • SQL data manipulation language (DML) statements

  • SQL CALL statements

  • PL/SQL blocks, subprograms, and packages

Database Triggers

A database trigger is a stored procedure that is associated with a specific table or view. Oracle Database calls the trigger automatically whenever a given DML operation modifies the table or view.

A trigger has the following parts:

  • A triggering event, which is generally a DML operation

  • An optional trigger constraint

  • A trigger action

When the event occurs, the trigger is called. A CALL statement in the trigger calls a Java method through the call specification of the method, to perform the action.

Database triggers are used to enforce complex business rules, derive column values automatically, prevent invalid transactions, log events transparently, audit transactions, and gather statistics.

Object-Relational Methods

A SQL object type is a user-defined composite data type that encapsulates a set of variables, called attributes, with a set of operations, called methods, which can be written in Java. The data structure formed by the set of attributes is public. However, as a good programming practice, you must ensure that your application does not manipulate these attributes directly and uses the set of methods provided.

You can create an abstract template for some real-world object as a SQL object type. The template specifies only those attributes and methods that the object will need in the application environment. At run time, when you fill the data structure with values, you create an instance of the object type. You can create as many instances as required.

Typically, an object type corresponds to some business entity, such as a purchase order. To accommodate a variable number of items, object types can use a VARRAY, a nested table, or both.

For example, the purchase order object type can contain a variable number of line items.

Advantages of Stored Procedures

Stored procedures offer several advantages. The following advantages are covered in this section:


Stored procedures are compiled once and stored in an executable form. As a result, procedure calls are quick and efficient. Executable code is automatically cached and shared among users. This lowers memory requirements and invocation overhead.

By grouping SQL statements, a stored procedure allows the statements to be processed with a single call. This minimizes the use of slow networks, reduces network traffic, and improves round-trip response time. Also, result-set processing eliminates network bottlenecks.

Additionally, stored procedures enable you to take advantage of the computing resources of the server. For example, you can move computation-bound procedures from client to server, where they will run faster. Likewise, stored functions called from SQL statements enhance performance by running application logic within the server.

Productivity and Ease of Use

By designing applications around a common set of stored procedures, you can avoid redundant coding and increase the productivity. Moreover, stored procedures let you extend the functionality of the database.

You can use the Java integrated development environment (IDE) of your choice to create stored procedures. Then, you can deploy these procedures on any tier of the network architecture. Moreover, they can be called by standard Java interfaces, such as Java Database Connectivity (JDBC), and by programmatic interfaces and development tools, such as SQLJ, Oracle Call Interface (OCI), Pro*C/C++, and JDeveloper.

This broad access to stored procedures lets you share business logic across applications. For example, a stored procedure that implements a business rule can be called from various client-side applications, all of which can share that business rule. In addition, you can leverage the Java facilities of the server while continuing to write applications for a preferred programmatic interface.


Stored procedures increase scalability by isolating application processing on the server. In addition, automatic dependency tracking for stored procedures helps in developing scalable applications.

The shared memory facilities of the shared server enable Oracle Database to support more than 10,000 concurrent users on a single node. For more scalability, you can use the Oracle Net Services Connection Manager to multiplex Oracle Net Services connections.


After a stored procedure is validated, you can use it with confidence in any number of applications. If its definition changes, then only the procedure is affected, not the applications that call it. This simplifies maintenance and enhancement. Also, maintaining a procedure on the server is easier than maintaining copies on different client computers.


Java in Oracle Database fully conforms to the Java Language Specification (JLS) and furnishes all the advantages of a general-purpose, object-oriented programming language. Also, as with PL/SQL, Java provides full access to Oracle data. As a result, any procedure that is written in PL/SQL can also be written in Java.

PL/SQL stored procedures complement Java stored procedures. Typically, SQL programmers who want procedural extensions favor PL/SQL, and Java programmers who want easy access to Oracle data favor Java.

Oracle Database allows a high degree of interoperability between Java and PL/SQL. Java applications can call PL/SQL stored procedures using an embedded JDBC driver. Conversely, PL/SQL applications can call Java stored procedures directly.


With Oracle Advanced Replication, you can replicate stored procedures from one Oracle Database instance to another. This enables you to use stored procedures to implement a central set of business rules. Once you write the procedures, you can replicate and distribute them to work groups and branch offices throughout the company. In this way, you can revise policies on a central server rather than on individual servers.


Security is a large arena that includes:

  • Network security for the connection

  • Access and execution control of operating system resources or of JVM and user-defined classes

  • Bytecode verification of JAR files imported from an external source.

In Oracle Database, all classes are loaded into a secure database and, therefore, are untrusted. A user requires the appropriate permissions to access classes and operating system resources. Likewise, all stored procedures are secured against other users. You can grant the EXECUTE database privilege to users who need to access the stored procedures.

You can restrict access to Oracle data by allowing users to manipulate the data only through stored procedures that run with their definer's privileges. For example, you can allow access to a procedure that updates a database table, but deny access to the table itself.

Java Stored Procedure Configuration

To configure the database to run Java stored procedures, you must decide on which of the following modes the database should run:

  • Dedicated server mode

    You must configure the database and clients in dedicated server mode using Oracle Net Services connections.

  • Shared server mode

    You must configure the server for shared server mode with the DISPATCHERS parameter.

Java, SQL, or PL/SQL clients, which run Java stored procedures on the server, connect to the database over an Oracle Net Services connection.

Java Stored Procedures Steps

You can run Java stored procedures in the same way as PL/SQL stored procedures. Normally, a call to a Java stored procedure is a result of database manipulation, because it is usually the result of a trigger or SQL DML call. To call a Java stored procedure, you must publish it through a call specification.

Before you can call Java stored procedures, you must load them into the Oracle Database instance and publish them to SQL. Loading and publishing are separate tasks. Many Java classes, which are referenced only by other Java classes, are never published.

To load Java stored procedures automatically, you can use the loadjava command-line utility. It loads Java source, class, and resource files into a system-generated database table, and then uses the SQL CREATE JAVA {SOURCE | CLASS | RESOURCE} statement to load the Java files into the Oracle Database instance. You can upload Java files from file systems, popular Java IDEs, intranets, or the Internet.

The following steps are involved in creating, loading, and calling Java stored procedures:


To load Java stored procedures manually, you can use the CREATE JAVA statements. For example, in SQL*Plus, you can use the CREATE JAVA CLASS statement to load Java class files from local BFILE and LOB columns into Oracle Database.

Step 1: Create or Reuse the Java Classes

Use a preferred Java IDE to create classes, or reuse existing classes that meet your requirements. Oracle Database supports many Java development tools and client-side programmatic interfaces. For example, the Oracle JVM accepts programs developed in popular Java IDEs, such as Oracle JDeveloper, Symantec Visual Cafe, and Borland JBuilder.

In the following example, you create the public class Oscar. It has a single method named quote(), which returns a quotation from Oscar Wilde.

public class Oscar
  // return a quotation from Oscar Wilde
  public static String quote()
    return "I can resist everything except temptation.";

Save the class as Oscar.java. Using a Java compiler, compile the .java file on your client system, as follows:

javac Oscar.java

The compiler outputs a Java binary file, in this case, Oscar.class.

Step 2: Load and Resolve the Java Classes

Using the loadjava utility, you can load Java source, class, and resource files into an Oracle Database instance, where they are stored as Java schema objects. You can run loadjava from the command line or from an application, and you can specify several options including a resolver.

In the following example, loadjava connects to the database using the default JDBC OCI driver. You must specify the user name and password. By default, the Oscar class is loaded into the schema of the user you log in as, in this case, scott.

$ loadjava -user scott/tiger Oscar.class

When you call the quote() method, the server uses a resolver to search for supporting classes, such as String. In this case, the default resolver is used. The default resolver first searches the current schema and then the SYS schema, where all the core Java class libraries reside. If necessary, you can specify different resolvers.

Step 3: Publish the Java Classes

For each Java method that can be called from SQL, you must write a call specification, which exposes the top-level entry point of the method to Oracle Database. Typically, only a few call specifications are needed. If preferred, you can generate these call specifications using Oracle JDeveloper.

In the following example, from SQL*Plus, you connect to the database and then define a top-level call specification for the quote() method:

SQL> connect scott/tiger

3 NAME 'Oscar.quote() return java.lang.String';

Step 4: Call the Stored Procedures

You can call Java stored procedures from SQL DML statements, PL/SQL blocks, and PL/SQL subprograms. Using the SQL CALL statement, you can also call the stored procedures from the top level, for example, from SQL*Plus. Stored procedures can also be called from database triggers.

In the following example, you declare a SQL*Plus host variable:


Then, you call the function oscar_quote(), as follows:

SQL> CALL oscar_quote() INTO :theQuote;

SQL> PRINT theQuote;

I can resist everything except temptation.

Step 5: Debug the Stored Procedures, if Necessary

Debugging the stored procedures is mandatory. Your Java stored procedures run remotely on a server that resides on a separate computer. However, the JDK debugger, jdb, cannot debug remote Java programs. As a result, you need to debug your stored procedures on your client computer before you load them on to the database.