Make the Java-Oracle9i connection

Put Oracle9i's object-oriented features to work

One of Java's most appealing features is platform neutrality: Java gives you the ability to port applications to numerous operating systems without tie-ins to a particular application server, IDE, or even database vendor. This flexibility is the cornerstone of Java's appeal and presents a strong foundation for software development. At the very least, such platform independence is an admirable goal to work towards. But under certain circumstances, commitments are necessary.

A team of Java developers might be forced to rationalize their commitment to a particular database vendor for numerous reasons, but invariably, one of the strongest motivators is performance. Leveraging a database's inherent single processes for executing stored procedures is a well-known technique to improve an application's scalability and speed. As an aside, many critics of the .Net Pet Shop application complained that the better performance achieved by that Pet Shop version was largely due to its reliance on stored procedures (our purpose isn't to argue for or against .Net or even comment on the merits of its architecture; our goal is to draw attention to the benefits of stored procedures).

In addition, a very common design pattern in Enterprise JavaBeans (EJB) development is the Fast Lane Reader, or Data Access Object (DAO) pattern. This pattern is meant to expedite read-only requests by avoiding the entity bean layer altogether and going directly to the database. In a way, the techniques described in this article extend that approach.

In this article, we examine the relationship between Java and Oracle9i. More precisely, we demonstrate some basic techniques for utilizing Oracle9i's new object-oriented features in stored procedure development. We show how the stored procedure development process can be easily and seamlessly accomplished, taking into account the complexities of modern object-oriented software development with Java.

Note: You can downloaded this article's source code from Resources.

Oracle types and inheritance

Both Oracle and Java support single inheritance, and the basics are similar for both. Oracle type inheritance, as in a Java class hierarchy, allows for ever-increasing specificity in subtypes. By expanding a type hierarchy, new columns can be added to subtypes while inheriting the basic derived features (both attributes and behavior) from the parent.

However, the syntax of how this is accomplished differs slightly between Java and Oracle's PL (Procedural Language)/SQL. In Java, object inheritance involves the keyword extends, whereas Oracle's PL/SQL uses the keyword under. PL/SQL creates object types with the create type phrase. To derive from this type, the keyword NOT FINAL must be used.

The table below compares Oracle PL/SQL and Java syntax.

Oracle PL/SQL syntax and Java syntax compared

FeatureOracle PL/SQL syntaxJava syntax
Type specification or definitionCreate type <type name> (e.g., create type Student)<access specifier> class <name> public class Student
Implementation inheritanceunderextends
Abstract typesNot instantiableAbstract
SubtypingNot final; Oracle types are not extendable by defaultNot applicable; Java objects can be extended by default
Method overriding/polymorphismOverridingSubtypes override supertype methods by default; to explicitly call the supertype method, use the keyword super
Standard outputDBMS_OUTPUT.PUT_LINE()System.out.println()

Traditionally, the migration from a class hierarchy in Java to a relational model required an O/R (object relational) mapping layer. Many products, such as Thought Inc.'s CocoBase Enterprise O/R, were designed to avoid manually coding this O/R layer. In addition, container-managed persistence (CMP) strives to accomplish this mapping, but, by specifying the relations in XML, does so with considerable overhead. However, when trying to employ straight Java Database Connectivity (JDBC) and SQL via the Fast Lane Reader pattern to achieve optimal performance, these approaches are not an option.

Fortunately, Oracle9i allows a developer to map existing class hierarchies or create new ones without modifying relational data and/or create a mapping translation layer from the object to relational domains. This ability to map Java objects directly to Oracle objects, foregoing any translation layer (often referred to as impedance mismatch), is an extremely useful feature. Without the need to create and manage a translation layer, development time can be greatly reduced, and reliance on third-party middleware products can be mitigated. With Oracle9i and JDBC, developers can now place mapping information in the object itself.

Oracle object types are abstractions upon Oracle relational types. Underneath, the Oracle database might still deal with the data as a group of relational entities (tables, columns, rows, etc.), but developers can think of the object types in aggregate as real-world entities. This allows Java-Oracle PL/SQL developers to cast aside the detailed inner workings of the relational structures and safely move about in the realm of objects. The utility of this object perspective cannot be overstated from a developer's vantage point. And just like any other database element, object types are similarly managed alongside other database schema elements. Therefore, database administrators do not need to alter their approach to accommodate Oracle object types.

Let's look at our example. A few basic types are derived from the Vehicle superclass. The abstract Java Vehicle class has a direct descendant, PassengerVehicle. PassengerVehicle inherits all of the key attributes from Vehicle, such as vehicle ID, make, model, etc.

We have a basic Vehicle type and can derive PassengerVehicle, CommercialVehicle, and EnduranceVehicle types from it. Each subtype becomes more specialized than its parent, and if the need arises, you can later add more subtypes to the hierarchy, such as EconomyVehicle or FuelHybrid, to further qualify the PassengerVehicle type.

The Java base class Vehicle shown below contains our hierarchy's essential attributes. Vehicle contains a vehicleID (e.g., a vehicle identification number) used as a primary key and some descriptive information such as the make and model. The fuelTank and odometer attributes refer to types defined elsewhere in our domain. The ORACLE_ARRAY_NAME attribute identifies the Oracle type in which the Java class is mapped (VEHICLE_TBL_T in Vehicle's case):

public abstract class Vehicle
    extends AbstractSQLData
    public static final String ORACLE_ARRAY_NAME = "VEHICLE_TBL_T";
    protected int vehicleId;
    protected String make;
    protected String model;
    protected int range;
    protected FuelTank fuelTank;
    protected Odometer odometer;
    protected Odometer tripOdometer;

The PassengerVehicle is a subtype for the Vehicle class. It shares all of the base Vehicle class's attributes, via inheritance, differing only in the Oracle type it maps to the Oracle type, PASSENGER_VEHICLE_OBJ_T. The setSQLTypeName() method is inherited from the base class AbstractSQLData from which all Vehicle types derive. AbstractSQLData is merely a wrapper for the java.sql.SQLData class. SQLData plays a central role in custom mappings between Oracle and Java:

public class PassengerVehicle
    extends Vehicle
    public static final String ORACLE_OBJECT_NAME = 
    public PassengerVehicle()

The class hierarchy is further extended with the introduction of two more classes, OffRoadVehicle and CommercialVehicle. This hierarchy is interesting because the Java classes map to Oracle types that rely on a single database table named VEHICLE. Thus, the Oracle types mentioned above are abstractions on a single relational entity. Whether your design involves a one-to-one mapping of Oracle types to tables or a more complex representation, the ability to do either greatly improves the flexibility of migration between application layers.

This code lists the VEHICLE table:

create table VEHICLE
  VEHICLE_ID              NUMBER(10) not null,
  MAKE                    VARCHAR2(80) not null,
  MODEL                   VARCHAR2(80) not null,
  ODOMETER_ID             NUMBER(10) not null,
  TRIP_ODOMETER_ID        NUMBER(10) not null,
  FUEL_TANK_ID            NUMBER(10) not null,
  MPG                     NUMBER(2) not null

Another nice feature is the object view. As an extension to traditional views in relational databases, an object view is a virtual object table. Just as a view is a virtual relational table that simply references a group of actual tables, an object view is also a reference to a group of relational entities but constructed as an object type. Object views are useful in situations where a developer must create a set of object-oriented services upon an existing relational store without the need or desire to modify the actual relational data. In this article's example, we do not use object views since there is no preexisting relational model to build upon. Rather we start from scratch with pure object types. But in practice, dealing with object views is similar to dealing with the actual object type. Figure 1 shows the Vehicle example's object model.

Figure 1. Object model for Vehicle example. Click on thumbnail to view full-size image.

Dynamic dispatch (polymorphism)

Commonly referred to as polymorphism, dynamic dispatch behavior in Oracle allows stored procedures or functions to override supertype methods and directly invoke the subtype methods. Subtypes can override nonfinal methods derived from supertypes. Oracle9i also supports substitutability—another consequence of an object-oriented language—that allows the substitution of a subtype where the supertype is required. The combination of dynamic dispatch and substitutability enables procedures to invoke inherited class methods on any object instance regardless of its location in a class hierarchy.

Polymorphism in a stored procedure is demonstrated below. The offroad_vehicle_obj_t overrides the set_range() method's inherited behavior. The offroad_vehicle_obj_t version of set_range() takes into account the existence of an auxiliary fuel tank, whereas the supertype vehicle_obj_t uses the default fuel tank capacity to calculate the range:

member procedure set_range(mpg in number) is
     range := mpg * fuel_tank.capacity;
create or replace type body offroad_vehicle_obj_t is
  overriding member procedure set_range(mpg in number) is
     range := mpg * (fuel_tank.capacity + auxillary_fuel_tank.capacity);

Type evolution

Another useful feature is type evolution. Through type evolution, a developer can modify the type's definition while simultaneously cascading the changes to all of the type's dependents. You can use type evolution to add, modify, or drop methods and attributes on an object type as long as the change is not to a final or noninstantiable type. However, if need be, a noninstantiable or final type can be evolved to an instantiable type. When evolving a type, the type's dependencies are checked and validated by recompilation. If the dependency can be recompiled, then the change commits; otherwise, the change rolls back, and the type is not evolved.

The syntax for type evolution is the ALTER TYPE phrase, but type evolution does not require the ALTER TYPE statement. A developer can drop all of the objects, make the necessary type change (the evolution), and then manually recreate the dependent types one by one. However, type evolution gives you the ability to quickly cascade simple changes throughout a type hierarchy without elaborate groups of drop and create statements. We do not demonstrate type evolution in this article, but the functionality is useful on mature projects where object models change frequently.

Custom constructors

Every user-defined Oracle type has a system default constructor. In Java terms, this is often referred to as the default constructor or no-arg constructor. Oracle provides the same initialization feature with its object types, except the default constructor requires the inclusion of all type members as parameters—the exact opposite from Java and other object-oriented languages.

The key is that the attribute order must match the order in which they are defined. Noninstantiable types (equivalent to an abstract class) do not have defined constructors. Functions can also be declared as noninstantiable so long as they are in a noninstantiable type. Ironically, a no-arg constructor in an Oracle type would involve a custom user-defined constructor. Here's an example of a user-defined constructor in PL/SQL and how you invoke it:

--the definition
constructor function passenger_vehicle_obj_t(vehicle_id in number, make in 
varchar2, model in varchar2, mpg in number, odometer in odometer_obj_t, 
trip_odometer in odometer_obj_t, fuel_tank in fuel_tank_obj_t) return self 
as result
--the invocation
vehicle_obj := passenger_vehicle_obj_t(vehicle_id, make, model, mpg, 
odometer(1), trip_odometer(1), fuel_tank(1));

Mapping Java objects to Oracle types

Oracle's object-relational facilities are tightly integrated with JDBC and SQLJ. This tight integration allows seamless integration between Java code and PL/SQL types in methods. Objects, through a form of serialization, persist their state to the database. Objects can be mapped to C++, Java, or XML. In this article, we only explore techniques for mapping objects to Java via JDBC.


SQLJ is a popular alternative to JDBC. SQLJ is an Oracle technology that simplifies mapping from Java to SQL by alleviating the developer from the task of object serialization. SQLJ allows strong typing through the use of oracle.sql.CustomData and weak typing through oracle.sql.STRUCT. SQLJ uses the oracle.sql package. The remainder of this article focuses on the JDBC approach to O/R mapping because many good resources about SQLJ already exist.


The JDBC API provides a programming bridge between Java programs and the Oracle database. JDBC allows access to database entities through dynamic SQL. Through customizable mappings, JDBC also provides Java with a way to access Oracle object types as if they are Java classes. When JDBC calls encounter an Oracle object, the database driver looks up the Oracle-to-Java mapping and instantiates the Java object based upon the Oracle object's state. This process also works in the Java-to-Oracle direction, where object parameters in a stored procedure convert to their Oracle corollaries.

This process of mapping Java objects to Oracle objects back to Java objects is a variation on serialization. In the JDBC scenario, the state is marshaled between the Oracle and the Java environments (e.g., JVM).

The Vehicle base class's readSQL() method is responsible for transforming Oracle data into Java attribute values (i.e., when receiving an Oracle stored procedure's return value). The writeSQL() method translates Java attribute data into Oracle data (i.e., when a Java program supplies a stored procedure's parameters). These methods are not called directly by the developer but are instead internally invoked by the Connection object:

protected void readSQL(SQLInput sqlInput)
        throws SQLException
        vehicleId = sqlInput.readInt();
        make = sqlInput.readString();
        model = sqlInput.readString();
        range = sqlInput.readInt();
        fuelTank = (FuelTank)sqlInput.readObject();
        odometer = (Odometer)sqlInput.readObject();
        tripOdometer = (Odometer)sqlInput.readObject();
    public void writeSQL(SQLOutput sqlOutput)
        throws SQLException

The run() method below demonstrates the invocation of a stored procedure from our sample Java program. The example highlights connection type map usage, the passing of procedure parameters, array descriptor usage, and finally the CallableStatement class's various methods:

    public void run(String[] makes)
        throws SQLException
        Connection connection = null;
        CallableStatement callableStatement = null;
            connection = 
(HOST=" + host + ")(PORT=" + port + ")))(CONNECT_DATA=(SERVICE_NAME=" + database + ")))", username, password);
            Map typeMap = connection.getTypeMap();
            typeMap.put(Odometer.ORACLE_OBJECT_NAME, Odometer.class);
            typeMap.put(FuelTank.ORACLE_OBJECT_NAME, FuelTank.class);
            typeMap.put(Vehicle.ORACLE_ARRAY_NAME, Vehicle[].class);
            callableStatement = connection.prepareCall("{? = call 
            ArrayDescriptor arrayDescriptor = 
ArrayDescriptor.createDescriptor("MAKE_TBL_T", connection);
            ARRAY array = new ARRAY(arrayDescriptor, connection, makes);
            callableStatement.setObject(2, array);
            callableStatement.registerOutParameter(1, OracleTypes.ARRAY, 
            Object[] objects = 
            for (int i = 0; i < objects.length; i++)
                if (callableStatement != null)
            catch(SQLException e)
                System.out.println("Exception encountered closing callable 
statement: " + e.getMessage());
                if (connection != null)
            catch(SQLException e)
                System.out.println("Exception encountered closing 
connection: " + e.getMessage());


SQLData provides a mechanism to specify explicit mappings between Oracle object types and Java classes. The java.sql package contains the SQLData class.

The explicit mapping is entered in a type map, which must be provided to the Connection object. The programmer simply supplies the Oracle type name, the Java class name, and the field mappings. This is automatically done in SQLJ and O/R mapping tools. But with JDBC, we must specify the mapping instructions directly.

Cast and MultiSet functions

Pseudo functions were first introduced in Oracle8i and enable relational tables to be treated as if they were a collection of user-defined types. In our example, we use the two pseudo functions, Cast and MultiSet, to transform between user-defined Oracle objects and relational data.

MultiSet converts a table into a collection, while Cast converts among collections of different types. Together they form a nice one-two punch.

The following code demonstrates how Vehicle_manager uses Cast and MultiSet together. MultiSet and Cast are extensively used in the example to convert a series of SQL queries into the vehicle_info_tbl_t type, which is selected into a ref cursor:

create or replace package body vehicle_manager is
  type ref_cur_t is ref cursor;
  function search(make_tbl in make_tbl_t) return vehicle_tbl_t is
    vehicle_info_cur ref_cur_t;
    vehicle_info_tbl vehicle_info_tbl_t;
    vehicles vehicle_tbl_t := vehicle_tbl_t();
    open vehicle_info_cur for select cast ( multiset (
      select v.vehicle_id
           , v.make
           , v.model
           , v.mpg
           , cast ( multiset (
               select ft.fuel_tank_id, ft.capacity from fuel_tank ft where 
v.fuel_tank_id = ft.fuel_tank_id
             ) as fuel_tank_tbl_t )
           , cast ( multiset (
               select ft.fuel_tank_id, ft.capacity from fuel_tank ft where 
v.auxillary_fuel_tank_id = ft.fuel_tank_id
             ) as fuel_tank_tbl_t )
           , cast ( multiset (
               select o.odometer_id, o.mileage from odometer o where 
v.odometer_id = o.odometer_id
             ) as odometer_tbl_t )
           , cast ( multiset (
               select o.odometer_id, o.mileage from odometer o where 
v.trip_odometer_id = o.odometer_id
             ) as odometer_tbl_t )
           , cast ( multiset (
               select o.odometer_id, o.mileage from odometer o where 
v.maintenance_odometer_id = o.odometer_id
             ) as odometer_tbl_t )
        from vehicle v where make in (select * from table ( make_tbl ) )
    ) as vehicle_info_tbl_t ) from dual;
    if vehicle_info_cur%isopen then
      fetch vehicle_info_cur into vehicle_info_tbl;
      if vehicle_info_tbl.count > 0 then
        for i in vehicle_info_tbl.first .. vehicle_info_tbl.last
          vehicles(i) := vehicle_info_tbl(i).get_vehicle();
        end loop;
      end if;
    end if;
    return vehicles;
end vehicle_manager;

Run the sample and its output

To run this article's example, first download the code from Resources. Then run the script JavaWorldExample.sql in SQLPlus, or within any active Oracle environment, to create the tables and types, and populate sample data into the tables. Make sure you have the appropriate create permissions within your schema to create entities such as objects and tables. You will need to modify the attributes host, database, username, and password on to reflect your development environment's specifics. Next, compile all of the .java files (javac *.java) with a J2SE (Java 2 Platform, Standard Edition) 1.3 compiler or greater. Finally, you should be able to run the JavaWorldExample by typing java JavaWorldExample into the command-line prompt. Figure 2 is a screen shot of the desired output.

Figure 2. JavaWorldExample screen shot

Java and Oracle in unison

Oracle's object features are an extremely useful facility for Java developers in overcoming the dreaded impedance mismatch between Java applications and relational databases. This article explained and demonstrated features such as type inheritance, polymorphism, and type maps. Java programmers using Oracle9i can apply this article's concepts without remodeling their domains since Oracle object types can be neatly built upon existing relational models. We showed how Java developers can take advantage of Oracle's object features by using stored procedures and JDBC. Although this may reduce your application's portability, the performance pay-off often justifies the cost.

Terry Camerlengo is a software developer in Columbus, Ohio with more than six years of experience designing and implementing enterprise software solutions. He has numerous certifications and is coauthor of The Sun Certified Java Developer Exam with J2SE 1.4 (APress, 2002; ISBN: 1590590309) Carl Johnson has fourteen years of experience designing and implementing both commercial and enterprise software solutions in C, C++, and Java.

Learn more about this topic