Skip Headers

Oracle9i Java Stored Procedures Developer's Guide
Release 2 (9.2)

Part Number A96659-01
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page


The Oracle JVM has all the features you need to build a new generation of enterprise-wide applications at a low cost. Chief among those features are stored procedures, which open the Oracle RDBMS to all Java programmers. With stored procedures, you can implement business logic at the server level, thereby improving application performance, scalability, and security.

This chapter contains the following information:

Java and the RDBMS: A Robust Combination

The Oracle RDBMS provides Java applications with a dynamic data-processing engine that supports complex queries and different views of the same data. All client requests are assembled as data queries for immediate processing, and query results are generated on the fly.

Several features make Java ideal for server programming. Java lets you assemble applications using off-the-shelf software components (JavaBeans). Its type safety and automatic memory management allow for tight integration with the RDBMS. In addition, Java supports the transparent distribution of application components across a network.

Thus, Java and the RDBMS support the rapid assembly of component-based, network-centric applications that can evolve gracefully as business needs change. In addition, you can move applications and data stores off the desktop and onto intelligent networks and network-centric servers. More important, you can access those applications and data stores from any client device.

Figure 1-1 shows a traditional two-tier, client/server configuration in which clients call Java stored procedures the same way they call PL/SQL stored procedures. (PL/SQL is an advanced 4GL tightly integrated with Oracle.) The figure also shows how the Oracle Net Services Connection Manager can funnel many network connections into a single database connection. This enables the RDBMS to support a large number of concurrent users.

Figure 1-1 Two-Tier Client/Server Configuration

Text description of two_tier.gif follows
Text description of the illustration two_tier.gif

Stored Procedures and Run-Time Contexts

Stored procedures are Java methods published to SQL and stored in an Oracle database for general use. To publish Java methods, you write call specifications (call specs for short), 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 spec simply publishes the existence of a Java method. So, when you call the method (through its call spec), 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 1-2 shows a stored procedure being called by various applications.

Figure 1-2 Calling a Stored Procedure

Text description of call_sto.gif follows
Text description of the illustration call_sto.gif

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

The next three sections describe these contexts.

Functions and Procedures

Functions and procedures are named blocks that encapsulate a sequence of statements. They are like 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. So, for void Java methods, you use procedure call specs, and for value-returning methods, you use function call specs.

Only top-level and package (not local) PL/SQL functions and procedures can be used as call specs. 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:

Database Triggers

A database trigger is a stored procedure associated with a specific table or view. Oracle invokes (fires) the trigger automatically whenever a given DML operation modifies the table or view.

A trigger has three parts: a triggering event (DML operation), an optional trigger constraint, and a trigger action. When the event occurs, the trigger fires and a CALL statement calls a Java method (through its call spec) to perform the action.

Database triggers, which you define using the SQL CREATE TRIGGER statement, let you customize the RDBMS. For example, they can restrict DML operations to regular business hours. Typically, triggers are used to enforce complex business rules, derive column values automatically, prevent invalid transactions, log events transparently, audit transactions, or gather statistics.

Object-Relational Methods

A SQL object type is a user-defined composite datatype that encapsulates a set of variables (attributes) with a set of operations (methods), which can be written in Java. The data structure formed by the set of attributes is public (visible to client programs). However, well-behaved programs do not manipulate it directly. Instead, they use the set of methods provided.

When you define an object type using the SQL CREATE ... OBJECT statement, you create an abstract template for some real-world object. The template specifies only those attributes and behaviors 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 (objects) as necessary.

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 variable-length arrays (varrays) and nested tables. For example, this feature enables a purchase order object type to contain a variable number of line items.

Advantages of Stored Procedures

Stored procedures offer several advantages including better performance, higher productivity, ease of use, and increased scalability.


Stored procedures are compiled once and stored in executable form, so 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 them to be executed with a single call. This minimizes the use of slow networks, reduces network traffic, and improves round-trip response time. OLTP applications, in particular, benefit because 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 execute faster. Likewise, stored functions called from SQL statements enhance performance by executing 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 your productivity. Moreover, stored procedures let you extend the functionality of the RDBMS. For example, stored functions called from SQL statements enhance the power of SQL.

You can use the Java integrated development environment (IDE) of your choice to create stored procedures. Then, you can deploy them on any tier of the network architecture. Moreover, they can be called by standard Java interfaces, such as JDBC, and by programmatic interfaces and development tools such as SQLJ, the 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 server's Java facilities while continuing to write applications for your favorite programmatic interface.


Stored procedures increase scalability by isolating application processing on the server. In addition, automatic dependency tracking for stored procedures aids the development of scalable applications.

The shared memory facilities of the Shared Server enable Oracle 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.


Once it is validated, you can use a stored procedure with confidence in any number of applications. If its definition changes, 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 machines.


Within the RDBMS, Java conforms fully to the Java Language Specification 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, so any procedure written in PL/SQL can 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.

The RDBMS 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 (copy) stored procedures from one Oracle database to another. That feature makes them ideal for implementing a central set of business rules. Once you write them, you can replicate and distribute the stored procedures 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, and bytecode verification of imported JAR files from an external source.

Oracle uses Java 2 security to protect its Java virtual machine. All classes are loaded into a secure database, so they are untrusted. To access classes and operating system resources, a user needs the proper permissions. Likewise, all stored procedures are secured against other users (to whom you can grant the database privilege EXECUTE).

You can restrict access to Oracle data by allowing users to manipulate the data only through stored procedures that execute 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.

For a full discussion of Oracle JVM security, see the Oracle9i Java Developer's Guide.

The Oracle JVM and Its Components

The Oracle Java virtual machine (JVM) is a complete, Java 2-compliant Java execution environment. It runs in the same process space and address space as the RDBMS kernel, sharing its memory heaps and directly accessing its relational data. This design optimizes memory use and increases throughput.

The Oracle JVM provides a run-time environment for Java objects. It fully supports Java data structures, method dispatch, exception handling, and language-level threads. It also supports all the core Java class libraries including java.lang,,, java.math, and java.util. Figure 1-3 shows its main components.

Figure 1-3 Main Components of the Oracle JVM

Text description of jvm_comp.gif follows
Text description of the illustration jvm_comp.gif

The Oracle JVM embeds the standard Java namespace in RDBMS schemas. This feature lets Java programs access Java objects stored in Oracle databases and application servers across the enterprise.

In addition, the JVM is tightly integrated with the scalable, shared memory architecture of the RDBMS. Java programs use call, session, and object lifetimes efficiently without your intervention. So, you can scale Oracle JVM and middle-tier Java business objects, even when they have session-long state.

The Oracle JVM versus Client JVMs

This section discusses some important differences between the Oracle JVM and typical client JVMs.

Method main()

Client-based Java applications declare a single, top-level method (main()) that defines the profile of an application. As with applets, server-based applications have no such "inner loop". Instead, they are driven by logically independent clients.

Each client begins a session, calls its server-side logic modules through top-level entry points, and eventually ends the session. The server environment hides the managing of sessions, networks, and other shared resources from hosted Java programs.


A server cannot provide GUIs, but it can supply the logic that drives them. For example, the Oracle JVM does not supply the basic GUI components found in the JDK's Abstract Windowing Toolkit (AWT). However, all AWT Java classes are available within the server environment. So, your programs can use AWT functionality, as long as they do not attempt to materialize a GUI on the server.


The Oracle JVM is oriented to Java application deployment, not development. You can write and unit-test applications in your favorite IDE, then deploy them for execution within the RDBMS.

Java's binary compatibility enables you to work in any IDE, then upload Java class files to the server. You need not move your Java source files to the database. Instead, you can use powerful client-side IDEs to maintain Java applications that are deployed on the server.


Multithreading support is often cited as one of the key scalability features of the Java language. Certainly, the Java language and class libraries make it simpler to write multithreaded applications in Java than many other languages, but it is still a daunting task in any language to write reliable, scalable multithreaded code.

As a database server, Oracle9i efficiently schedules work for thousands of users. The Oracle JVM uses the facilities of the RDBMS server to concurrently schedule Java execution for thousands of users. Although Oracle9i supports Java language level threads required by the Java Language Specification (JLS) and Java Compatibility Kit (JCK), using threads within the scope of the database will not increase your scalability. Using the embedded scalability of the database eliminates the need for writing multithreaded Java servers. You should use the database's facilities for scheduling users by writing single-threaded Java applications. The database will take care of the scheduling between each application; thus, you achieve scalability without having to manage threads. You can still write multithreaded Java applications, but multiple Java threads will not increase your server's performance.

One difficulty multithreading imposes on Java is the interaction of threads and automated storage management, or garbage collection. The garbage collector executing in a generic JVM has no knowledge of which Java language threads are executing or how the underlying operating system schedules them.

Main Components of the Oracle JVM

This section briefly describes the main components of the Oracle JVM and some of the facilities they provide.

Library Manager

To store Java classes in an Oracle database, you use the command-line utility loadjava, which employs SQL CREATE JAVA statements to do its work. When invoked by the CREATE JAVA {SOURCE | CLASS | RESOURCE} statement, the library manager loads Java source, class, or resource files into the database. You never access these Java schema objects directly; only the Oracle JVM uses them.

Garbage Collection of Memory

Garbage collection is a major feature of Java's automated storage management, eliminating the need for Java developers to allocate and free memory explicitly. Consequently, this eliminates a large source of memory leaks that commonly plague C and C++ programs. There is a price for such a benefit: garbage collection contributes to the overhead of program execution speed and footprint. Although many papers have been written qualifying and quantifying the trade-off, the overall cost is reasonable, considering the alternatives.

Garbage collection imposes a challenge to the JVM developer seeking to supply a highly scalable and fast Java platform. The Oracle9i JVM meets these challenges in the following ways:


The Oracle JVM includes a standard Java 2 (also known as JDK 1.2) Java compiler. When invoked by the CREATE JAVA SOURCE statement, it translates Java source files into architecture-neutral, one-byte instructions known as bytecodes. Each bytecode consists of an opcode followed by its operands. The resulting Java class files, which conform fully to the Java standard, are submitted to the interpreter at run time.


To execute Java programs, the Oracle JVM includes a standard Java 2 bytecode interpreter. The interpreter and associated Java run-time system execute standard Java class files. The run-time system supports native methods and call-in/call-out from the host environment.


Although your own code is interpreted, the Oracle JVM uses natively compiled versions of the core Java class libraries, SQLJ translator, and JDBC drivers. For more information, see "Native Compiler (Accelerator)".

Class Loader

In response to requests from the run-time system, the Java class loader locates, loads, and initializes Java classes stored in the database. The class loader reads the class, then generates the data structures needed to execute it. Immutable data and metadata are loaded into initialize-once shared memory. As a result, less memory is required for each session. The class loader attempts to resolve external references when necessary. Also, it invokes the Java compiler automatically when Java class files must be recompiled (and the source files are available).


Java class files are fully portable and conform to a well-defined format. The verifier prevents the inadvertent use of "spoofed" Java class files, which might alter program flow or violate access restrictions. Oracle security and Java security work with the verifier to protect your applications and data.

Server-Side JDBC Internal Driver

JDBC is a standard set of Java classes providing vendor-independent access to relational data. Specified by Sun Microsystems and modeled after ODBC (Open Database Connectivity) and the X/Open SQL CLI (Call Level Interface), the JDBC classes supply standard features such as simultaneous connections to several databases, transaction management, simple queries, calls to stored procedures, and streaming access to LONG column data.

Using low-level entry points, a specially tuned JDBC driver runs directly inside the RDBMS, thereby providing the fastest access to Oracle data from Java stored procedures. The server-side internal JDBC driver complies fully with the Sun Microsystems JDBC specification. Tightly integrated with the RDBMS, it supports Oracle-specific datatypes, globalization character sets, and stored procedures. Additionally, the client-side and server-side JDBC APIs are the same, which makes it easy to partition applications.

Server-Side SQLJ Translator

SQLJ enables you to embed SQL statements in Java programs. It is more concise than JDBC and more amenable to static analysis and type checking. The SQLJ preprocessor, itself a Java program, takes as input a Java source file in which SQLJ clauses are embedded. Then, it translates the SQLJ clauses into Java class definitions that implement the specified SQL statements. The Java type system ensures that objects of those classes are called with the correct arguments.

A highly optimized SQLJ translator runs directly inside the RDBMS, where it provides run-time access to Oracle data using the server-side internal JDBC driver. SQLJ forms can include queries, DML, DDL, transaction control statements, and calls to stored procedures. The client-side and server-side SQLJ APIs are identical, which makes it easy to partition applications.

Native Compiler (Accelerator)

Java executes platform-independent bytecodes on top of a JVM, which in turn interacts with the specific hardware platform. Any time you add levels within software, your performance is degraded. Because Java requires going through an intermediary to interpret platform-independent bytecodes, a degree of inefficiency exists for Java applications that does not exists within a platform-dependent language, such as C. To address this issue, several JVM suppliers create native compilers. Native compilers translate Java bytecodes into platform-dependent native code, which eliminates the interpreter step and improves performance.

The following describes two methods for native compilation:

Compiler Description

Just-In-Time (JIT) Compilation

JIT compilers quickly compile Java bytecodes to native (platform-specific) machine code during runtime. This does not produce an executable to be executed on the platform; instead, it provides platform-dependent code from Java bytecodes that is executed directly after it is translated. This should be used for Java code that is run frequently, which will be executed at speeds closer to languages such as C.

Ahead-of-Time Compilation

Compilation translates Java bytecodes to platform-independent C code before runtime. Then a standard C compiler compiles the C code into an executable for the target platform. This approach is more suitable for Java applications that are modified infrequently. This approach takes advantage of the mature and efficient platform-specific compilation technology found in modern C compilers.

Oracle9i uses Ahead-of-Time compilation to deliver its core Java class libraries: JDBC code in natively compiled form. It is applicable across all the platforms Oracle supports, whereas a JIT approach requires low-level, processor-dependent code to be written and maintained for each platform. You can use this native compilation technology with your own Java code.

As Figure 1-4 shows, natively compiled code executes up to ten times faster than interpreted code. So, the more native code your program uses, the faster it executes.

Figure 1-4 Interpreter versus Accelerator

Text description of interpre.gif follows
Text description of the illustration interpre.gif

You can natively compile your own code using the ncomp tool. See Oracle9i Java Developer's Guide for more information.

Java Stored Procedure Configuration

To configure the database to run Java stored procedures, you must decide whether you want the database to run in dedicated server mode or shared server mode.

Java, SQL, or PL/SQL clients, which execute Java stored procedures on the server, connect to the database over a Oracle Net Services connection. For a full description of how to configure this connection, see the Oracle9i Net Services Administrator's Guide.

Developing Stored Procedures: An Overview

You execute Java stored procedures similarly to PL/SQL. Normally, calling a Java stored procedure is a by-product of database manipulation, because it is usually the result of a trigger or SQL DML call. To invoke a Java stored procedure, you must publish it through a call specification.

This section demonstrates how to develop a simple Java stored procedure. For more examples of a Java stored procedures application, see Chapter 5.

Step 1: Create or Reuse the Java Classes

Use your favorite Java IDE to create classes, or simply reuse existing classes that meet your needs. Oracle's Java facilities support many Java development tools and client-side programmatic interfaces. For example, the Oracle JVM accepts programs developed in popular Java IDEs such as Oracle's JDeveloper, Symantec's Visual Café, and Borland's 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.";

In the following example, using Sun Microsystems's JDK Java compiler, you compile class Oscar on your client workstation:


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

Step 2: Load and Resolve the Java Classes

Using the utility loadjava, you can upload Java source, class, and resource files into an Oracle database, 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 username and password. By default, class Oscar is loaded into the logon schema (in this case, scott).

> loadjava -user scott/tiger Oscar.class

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

For more information, see Chapter 2.

Step 3: Publish the Java Classes

For each Java method callable from SQL, you must write a call spec, which exposes the method's top-level entry point to Oracle. Typically, only a few call specs are needed, but if you like, Oracle's JDeveloper can generate them for you.

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

SQL> connect scott/tiger

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

For more information, see Chapter 3.

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 them from the top level (from SQL*Plus, for example) and 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.

For more information, see Chapter 4.

Step 5: If Necessary, Debug the Stored Procedures

Your Java stored procedures execute remotely on a server, which typically resides on a separate machine. However, the JDK debugger (jdb) cannot debug remote Java programs.

Oracle9i furnishes a debugging capability that is useful for developers who use the JDK's jdb debugger. Two interfaces are supported.

Oracle's JDeveloper provides a user-friendly integration with these debugging features. See the JDeveloper documentation for more information on how to debug your Java application through JDeveloper. Other independent IDE vendors will be able to integrate their own debuggers with Oracle9i.

Another Example

The following example shows how to create, resolve, load, and publish a simple Java stored procedure that echoes "Hello world".

  1. Write the Java class.

    Define a class, Hello, with one method,, that returns the string "Hello world".

    public class Hello
       public static String world ()
          return "Hello world";
  2. Compile the class on your client system. Using the Sun Microsystems JDK, for example, invoke the Java compiler, javac, as follows:

    Normally, it is a good idea to specify your CLASSPATH on the javac command line, especially when writing shell scripts or make files. The Java compiler produces a Java binary file--in this case, Hello.class.

    Keep in mind where this Java code will execute. If you execute Hello.class on your client system, it searches the CLASSPATH for all supporting core classes it must execute. This search should result in locating the dependent class in one of the following:

    • as an individual file in a directory, where the directory is specified in the CLASSPATH
    • within a .jar or .zip file, where the directory is specified in the CLASSPATH
  3. Decide on the resolver for your class.

    In this case, you load Hello.class in the server, where it is stored in the database as a Java schema object. When you execute the world() method of the Hello.class on the server, it finds the necessary supporting classes, such as String, using a resolver--in this case, the default resolver. The default resolver looks for classes in the current schema first and then in PUBLIC. All core class libraries, including the java.lang package, are found in PUBLIC. You may need to specify different resolvers, and you can force resolution to occur when you use loadjava, to determine if there are any problems earlier, rather than at runtime. Refer to the Oracle9i Java Developer's Guide for more details on resolvers and loadjava.

  4. Load the class on the Oracle9i server using loadjava. You must specify the username and password.
    loadjava -user scott/tiger Hello.class
  5. Publish the stored procedure through a call specification.

    To invoke a Java static method with a SQL CALL, you must publish it with a call specification. A call specification defines for SQL which arguments the method takes and the SQL types it returns.

    In SQL*Plus, connect to the database and define a top-level call specification for

    SQL> connect scott/tiger
    SQL> create or replace function HELLOWORLD return VARCHAR2 as
      2  language java name ' () return java.lang.String';
      3  /
    Function created.
  6. Invoke the stored procedure.
    SQL> variable myString varchar2[20];
    SQL> call HELLOWORLD() into :myString;
    Call completed.
    SQL> print myString;
    Hello world

    The call HELLOWORLD() into :myString statement performs a top-level call in Oracle9i. The Oracle-specific select HELLOWORLD from DUAL also works. Note that 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 invoked with triggers or SQL and PL/SQL calls. Furthermore, JDeveloper can automate the task of writing call specifications.

Go to previous page Go to next page
Copyright © 2000, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index