12.1 Object Types

An object
type is
a user-defined data type that combines data and related methods to
model complex entities. In this regard, they are similar to class
definitions in an object-oriented language such as C++ or Java.
However, unlike Java and C++, Oracle object types have a built-in
persistence mechanism, since a table can be defined to store an
object type in the database. Thus, Oracle object types can be
directly manipulated via SQL.

The best way to define the syntax and features of an object type is
with an example. The following DDL statement creates an object type
used to model an equity security such as a common stock:

The equity object type has four attributes and a
single member procedure. The NOT FINAL declaration at the end of the
type definition allows for subtypes to be defined in the future (more
on this later). The body of the apply_split
procedure is defined within a
CREATE TYPE BODY statement. The following
example illustrates how the apply_split member
procedure might be defined:

In this example, the
SELF keyword is used to identify the
current instance of the equity object type. Although it is not
required, we recommend using SELF in your code so that it is clear
that you are referencing or modifying the current
instance's data. We will explore how to call member
functions and procedures a bit later in the chapter.

Instances of type equity are created using the
default constructor, which has the same name as the object type and
expects one parameter per attribute of the object type. The following
PL/SQL block demonstrates how an instance of the equity object type
can be created using the default constructor:

Object type constructors may also be called from within DML
statements. The next example queries the issuer
table to find the issuer with the name 'ACME
Wholesalers', and then uses the retrieved
issuer_id field to construct an instance of the
equity type:

12.1.1 Subtyp
es

Oracle9i introduced several notable features to
the object-oriented toolset, including inheritance (release 1) and
user-defined constructors (release 2). The following type definition
demonstrates both of these features:

Because preferred stock is a special type of equity, you can create a
subtype that includes all of the attributes and methods of the
equity type along with additional attributes to
hold dividend information. In this case, we have added two attributes
to hold information about the last dividend payment, along with a
user-defined constructor for the preferred_stock
type, which is defined in the type body:

For the constructor, we chose to pass in an instance of
equity rather than pass in the four
equity attributes individually. The next example
shows how to create an instance of preferred_stock
by creating an instance of equity and passing it
into the user-defined constructor:

The equities inline view constructs an instance of
equity and an instance of
preferred_stock, and the containing query returns
those objects that are of type equity; as you can
see, both instances, one of equity and one of
preferred_stock, are returned. However, an
instance of equity is not an instance of
preferred_stock, as demonstrated in the next
example:

In this case, the equity instance is
not returned, since it is not of type
preferred_stock. This distinction will be
important when we look at collections later in the chapter.

12.1.2 Object Attributes

So far, we have created several object types and generated several
non-persistent (not stored in a database) instances of those object
types. If you want to store instances of your object types in a
database, you have two choices: create a column in a table to store
instances of your object type, or create a special type of table,
called an object table, to store instances of
your object type. This section will explore how to generate
object-based columns, while
the
following section will explore object tables.

The following table definition shows how an equity
object type may be used as an attribute of the
fund_holding table:

While the fund_id and
num_shares columns are defined using standard
built-in data types, the security column is
defined as type equity. When adding records to the
table, you must utilize the object type constructor, as illustrated
by the following INSERT statement:

To see the attributes of the equity object, you
must provide an alias for the table and reference the alias, the name
of the column containing the object type, and the object
type's attribute. The next query retrieves the
fund_id, which is a column in the
fund_holding table, and the
ticker, which is an attribute of the
equity object within the
fund_holding table:

The ability to substitute
a
subtype for its parent type is turned on by default. If you do not
want this behavior, you can turn it off when creating or altering
your object table. For the fund_holding table, you
would issue the following statement:

Even though you can substitute an instance of a subtype for an
instance of a parent type, you are limited to using the attributes
and methods defined for the parent type. In the most recent example,
the SELECT statement "sees" a table
of equity objects. That does not change just
because one of those objects happens to be a specific subtype of
equity.

12.1.3 Object Tables

In addition
to creating object type columns, you can
also build an object table specifically for holding instances of your
object type. You create these tables by referencing the object type
in the
CREATE TABLE statement using the
OF keyword:

CREATE TABLE equities OF equity;

You can populate the equities table using the
constructor for the equity object type, or you may
populate it from existing instances of the equity
object type. For example, the next statement populates the
equities table using the
security column of the
fund_holding table, which is defined as an
equity type:

If you want to retrieve the data in the equities
table as an instance of an equity object rather
than as a set of attributes, you can use the
VALUE function to return an object.
The following query retrieves the object having a ticker equal to
'ACMW' from the equities table:

Since you can treat an object table as either a relational table or
as a set of objects, object tables represent the best of both worlds.

You must use a table alias with the VALUE
function. You cannot pass a table name to VALUE.

Now that you have an object stored in the database, we can explore
how to call the apply_split member
procedure defined earlier. Before you call the procedure, you need to
find the target object in the table and then tell the object to run
its apply_split procedure. The following PL/SQL
block expands on the previous example, which finds the object in the
equities table with a ticker of
'ACMW', by finding an equity
object, invoking its apply_split method, and
saving it back to the table again:

It is important to realize that the apply_split
procedure is not operating directly on the data in the
equities table; rather, it is operating on a copy
of the object held in memory. After the
apply_split procedure has executed against the
copy, the UPDATE statement overwrites the object in the
equities table with the object referenced by the
local variable eq, thus saving the modified
version of the object.

Specifying FOR UPDATE at the end of the SELECT statement signifies
your intent to modify the data being selected at some point in the
future, which causes Oracle to place a lock on the data on your
behalf. If you do not specify FOR UPDATE in this case, it is possible
for another user to modify or delete the data between when you first
select the data and when you issue the UPDATE statement.

12.1.4 Object Parameters

Regardless of whether you
decide to store object types
persistently in the database, you can use them as vehicles for
passing data within or between applications. Object types may be used
as input parameters and return types for PL/SQL stored procedures and
functions. Additionally,
SELECT statements can instantiate and
return object types even if none of the tables in the FROM clause
contain object types. Therefore, object types may be used to graft an
object-oriented veneer on top of a purely relational database design.

To illustrate how this might work, let's build an
API for our example database that both accepts and returns object
types to find and build customer orders. First,
we'll identify the necessary object types:

From the API user's standpoint, objects are being
stored and retrieved from the database, even though the database
behind the API is purely relational. If you are squeamish about using
object types in your database schema, this approach can be an
attractive alternative to asking
your Java coders to directly manipulate
relational data.