Oracle object oriented relational features Oracle Tips by Burleson Consulting First published in 2001

 

Overview of Oracle object-oriented features

There has been a huge debate about the benefits of adding objects into mainstream database management. For years, a small but highly vocal group of object-oriented evangelists preached the object revolution with an almost religious fervor. However, the object-oriented databases languished in the backwaters of specialized databases until the major relational database vendors made a commitment to incorporate objects into their relational engines. Now that object orientation is becoming more widely used, Oracle developers are struggling to understand how the new object extensions are going to change their lives.

Oracle's commitment to objects was not just acknowledgment of a fad; the benefits of being able to support objects are very real, and the exciting new extensions of Oracle 8 are going to create the new foundation for database systems of the next century.

When glancing at the Oracle 8 documentation, these extensions seem mundane: User-defined data types, pointers to rows, and the ability to couple data with behavior using methods. However, these additions are anything but mundane. They are going to change the way databases are designed, implemented, and used in ways that Oracle developers have not been able to imagine.

Oracle database designers will no longer need to model their applications at their most atomic levels. The pointer constructs of Oracle 8 allow for the creation of aggregate objects, and it will no longer be necessary to create Oracle "views" to see composite objects. One of the object-oriented challenges for the relational database relates directly to the characteristics of the relational model. The object gurus argued that it does not make sense to dismantle your car when you arrive at home each night, only to reassemble your car every time you want to drive it. Finally, relational designers will be able to model the real world at all levels of aggregation and not just at the third normal form level.

This ability to prebuild real-world objects will allow the Oracle designer to model the world as it exists, without having to re-create objects from their pieces each time that they are needed. These real-world objects also have ramifications for Oracle's SQL. Designers won't have to join numerous tables to create an aggregate object; the object will have an independent existence, even if it is composed entirely of pieces from atomic tables. Figure A illustrates this construct.

Objects are made up of atomic relational entities.

This modeling ability also implies that a whole new paradigm of database access will happen. Rather than having to use SQL, designers can navigate Oracle 8 databases from row to row, chasing the pointer references without ever having to join tables. Navigational data access will allow Oracle designers to create faster links between tables, avoiding the costly SQL JOIN operations that plague some systems.

Finally, the ability to tightly couple data and behavior will change everything about Oracle. Instead of having all your process logic in external programs, the process code will move into the Oracle database, and the Oracle engine will manage both the data and the processes that operate on the data. "Methods" were first introduced into the object-oriented model to provide encapsulation and reusability. Encapsulation refers to the requirement that the data inside an object can be modified only by invoking one of its methods. By having these pretested and reliable methods associated with the object, an Oracle object "knows" how to behave, and the methods will always function in the same manner regardless of the target objects. Reusability is achieved by eliminating the "code hunt." Before methods, Oracle programmers would have to scan through Pro*C programs or stored procedures searching for the code they wanted. With methods, they only need to know the name of the class associated with the object to display a list of methods.

However, this reusability does not come without a price. The structure of the aggregate objects must be carefully defined, and the Oracle developer must give careful thought to the methods that are associated with objects at each level of aggregation.

Now that we have seen the compelling benefits of object/relational databases, let's take a closer look at these features. Oracle has implemented the object/relational model in stages, introducing objects in Oracle 8.0 and promising inheritance in Oracle 8.2. In a nutshell, the new features of Oracle 8 will allow for the creation of abstract objects and the pairing of methods or PL/SQL code with these objects.

User-defined data types

Oracle's support of user-defined data types (sometimes called abstract data types, or ADTs) has profound implications for database design and implementation. User-defined data types will allow the database designer to:

• Create aggregate data types.
  These are data types that contain other data types. For example a type called full_address could contain all of the subfields necessary for a complete mailing address.
• Create nested data structures.
  Designers can place data types within other data types to create data structures that can easily be reused within Oracle. For example, a designer could define a data type called customer that contains a data type called customer_demographics, which in turn contains a data type called job_history, and so on.

Pointers in Oracle

One of the new user-defined data types in the object/relational model is the pointer. Basically, a pointer is a unique reference to a row in a relational table, as shown in Figure B.

 

The ability to store row IDs inside a relational table extends the traditional relational model and enhances the capacity of an object/relational database to establish relationships between tables. Pointer data types allow you to:

• Reference sets of related rows in other tables.
  It is possible to violate first normal form and have a cell in a table that contains a pointer to repeating table values. For example, an employee table could contain a pointer called job_history_set, which in turn contains pointers to all the relevant rows in a job_history table. This would also allow for aggregate objects to be prebuilt so that all the specific rows in the aggregate table could be predefined.
   
• Include pointers to nondatabase objects in a flat file.
  For example, a table cell could contain a pointer to a flat file that contains an object such as a picture in GIF or JPEG format.
   
• Establish pointers to repeating groups.
  Database designers can violate first normal form and create a table column that has pointers to an array of row pointers. For example, you might create a column called order_history in a customer table. The column could contain a pointer to a reference table containing pointers to the specific rows that represent prior orders for that customer.
   
• Establish one-to-many and many-to-many data relationships without relational foreign keys.
  This capability alleviates the need for relational JOIN operations, since table columns can contain references to rows in other tables. By de-referencing these pointers, rows from other tables can be retrieved without ever using the expensive SQL JOIN operator.

Now that we have a high-level understanding of these Oracle 8 features, let's take a closer look at how they are implemented.

Basic Oracle user-defined data types

One of the shortcomings of the relational model is the requirement to reference all data at its most atomic level. For example, if we want to select all the address information for a customer, we are required to manipulate street_address, city_address, and zip_code as three separate statements. With abstract data typing, we can create a new data type called address and manipulate it as if it were an atomic data type. While this may seem a huge improvement, it is interesting to note that prerelational databases supported this construct, and the COBOL language has easy facilities for creating data "types" that were composed of subtypes, as shown

below.

customer name

customer-address

job details (1)

job details (2)

first name

last name

street ad.

city

state

zip

job dates

emp. Name

Title

cust-address

S

C

S

Z

Nesting of abstract data types

For example, in COBOL, we can define Customer-address as follows:

05 CUSTOMER-ADDRESS.

07 STREET-ADDRESS PIC X(80).

07 CITY-ADDRESS PIC X(80).

07 ZIP-CODE PIC X(5).

We can then move Customer-address as if it were an individual entity:

MOVE CUSTOMER-ADDRESS TO PRINT-REC.

MOVE SPACES TO CUSTOMER-ADDRESS.

By the same token, Oracle 8 allows the definition of a customer_address data type:

CREATE TYPE customer_address (

street_address char(20),

city_address char(20),

zip_code char(5));

We can then treat customer_address as a valid data type, using it to create tables and select data:

CREATE TABLE CUSTOMER (

customer_name cust_name,

full_address customer_address,

. . .

);

SELECT DISTINCT full_address FROM CUSTOMER;

or perhaps:

UPDATE CUSTOMER (full_address) VALUES ' ';

Note that we would also have to alter our SQL if we wanted to select a component of full_address:

SELECT full_address.zip_code

WHERE

full_address.zip_code LIKE '144%';
 

Nesting Oracle user-defined data types

Let's take this concept one step further and consider how abstract data types can be nested within other data types. A basic example would be to create a data type that encapsulates all the data in a table:

CREATE TYPE customer_stuff (

full_name customer_name,

home_address customer_address

business_address customer_address);

With the customer_stuff type defined, table definition becomes simple:

CREATE TABLE CUSTOMER (customer_data customer_stuff);

Using this kind of data type, we are essentially duplicating the object-oriented concept of encapsulation. That is, we are placing groups of related data types into a container that is completely self-contained and has the full authority of the innate relational data types such as int and char:

SELECT customer_stuff.customer_name.zip_code

FROM customer

WHERE customer_stuff.customer_name.zip_code like ‘144%';

 

Using Oracle pointer references

The ability to define data types that contain pointers to rows in other database tables will profoundly change the way databases are created and maintained. These extensions to the relational model will allow a cell in a table to reference a list of values or another entire table. Designers will then be able to define and implement "aggregate objects," which contain pointer references to the components, rather than having to define a relational view on the data. This capability will enable designers to more effectively model the real world, reduce overhead, and attach methods to aggregate objects.

Repeating groups and abstract data types

What happens when we nest data types that have repeating groups? In pre-relational databases that supported COBOL data definitions, it was easy to create a record that contained a finite repeating group. For example, in CA-IDMS, you could define a record definition containing three repeating groups of job history information:

03 EMPLOYEE.

05 EMPLOYEE-NAME PIC X(80).

. . .

05 JOB-HISTORY OCCURS 3 TIMES.

07 JOB-DATE PIC X(80).

07 JOB-EMPLOYER-NAME PIC X(80).

07 JOB-TITLE PIC X(80).

07 EMPLOYER-ADDRESS

09 STREET-ADDRESS PIC X(80).

09 CITY-ADDRESS PIC X(80).

09 ZIP-CODE PIC X(80);

So in COBOL, the JOB-HISTORY component—and any other component—can be referenced by a subscript:

MOVE JOB-HISTORY(2) TO OUT-REC.

MOVE 'DATABASE ADMINISTRATOR' TO JOB-TITLE(3).

Now, let's take a look at how repeating values appear in Oracle 8. Oracle PL/SQL uses the VARRAY construct to indicate repeating groups, so we can use the VARRAY mechanism to declare our job history item. Here we create job-history with three data types:

CREATE TYPE customer_address (

street_address char(20),

city_address char(20),

zip_code char(5));

CREATE TYPE job_details (

job_dates char(80),

job_employer_name char(80),

job_title char(80)

job_address customer_address);

CREATE TYPE job_history (

VARRAY(3) OF REF job_details);

Now that we have defined the data types, we can create the Oracle table using them:

CREATE TABLE CUSTOMER (

customer_name full_name,

cust_address customer_address,

prior_jobs job_history);

CREATE TABLE JOB_HISTORY (

job_stuff job_details);

Once we have created a repeating list within our Oracle definition, in addition to de-referencing the data type, we need to subscript the prior_jobs to tell Oracle which one we want:

SELECT customer.prior_jobs.job_title(3)

FROM CUSTOMER

WHERE

customer.customer_name.last_name LIKE 'JONES%';

Note that this data structure is a direct violation of first normal form! As you may recall from college days, one fundamental principle (or restriction, however you want to look at it) was that a relational table could not contain repeating values. This was primarily because SQL had no mechanism for allowing for repeating groups to be defined and subscripted.

Establishing Oracle data relationships with pointers

It is possible to allow for repeating values within a table cell, so why not a reference to an entirely new table? Imagine a database that allows nesting of tables within tables so that a single cell of one table could be a pointer to another whole table. While this concept may seem foreign on the surface, it is not too hard to understand if we consider that many real-world objects are made up of subparts.

A nineteenth century philosophy professor named Augustus De Morgan created an interesting poem to demonstrate this fundamental truth:

Great fleas have little fleas
upon their backs to bite 'em
And little fleas have lesser fleas,
and so ad-infinitum.

The great fleas themselves in turn
has greater fleas to go on,
while these again have greater still
and greater still,
and so on.

It has always been a shortcoming of the relational database that only atomic things could be directly represented and that relational views were required to assemble aggregate objects. At last, nested abstract data types allow Oracle users to represent real-world "things" without resorting to views.

Let's look at how this type of recursive data relationship might be represented within Oracle 8. The following example creates a TYPE definition for a list of orders. This list of pointers to orders might become a column within an Oracle table:

CREATE TYPE order_set

AS TABLE OF order;

CREATE TYPE customer_stuff (

customer_id integer,

customer_full_name full_name,

customer_full_address customer_address,

. . .

order_list order_set);

CREATE TABLE CUSTOMER OF customer_stuff;

Here we see the new style of table creation syntax. The following table declarations are identical, except that the CREATE TABLE OF syntax will establish Object IDs (OIDs) so that other tables may contain references to rows in the customer table.

Without OIDs:

CREATE TABLE CUSTOMER (cust_data customer_stuff);

With OIDs:

CREATE TABLE CUSTOMER OF customer_stuff;

In either case, we have nested the ORDER table within the CUSTOMER table! So where do we go from here? How do we populate this new structure? Let's take a look:

INSERT INTO CUSTOMER VALUES (

full_name ('ANDREW','S.','BURLESON'),

customer_address('123 1st St.','Minot, ND','74635');

Now, we could add three orders for this customer:

INSERT INTO ORDER VALUES order_id, customer_id, order_date (

9961

123,

SYSDATE);

INSERT INTO ORDER VALUES order_id, customer_id, order_date (

9962

123,

SYSDATE);

INSERT INTO ORDER VALUES Doorder_id, customer_id, order_date (

9963

123,

SYSDATE);

Now, here comes the cool part. We can now "pre-join" with the ORDER table to add the three orders for this customer:

UPDATE CUSTOMER

SET order_list (

SELECT REF(ORDER) /* Row_id */

FROM ORDER

WHERE

order_date = SYSDATE

AND

order.customer_ID = (123)

)

So what have we got here? It appears that the order_list entry in the CUSTOMER table will contain pointers to the three orders that have been placed by this customer.

As a result, we should be able to reference these pointers without having to perform a relational JOIN:

SELECT DEREF(order_list)

FROM CUSTOMER

WHERE

customer_id = 123; /* this will return 3 rows in the order table */

This query should return a pointer to the three rows in the ORDER table. It should then be a simple matter to de-reference these pointers to retrieve the contents of the ORDER table. It might look something like this:

SELECT DEREF(order_list)

FROM CUSTOMER

WHERE

customer_ID = 123;

What's important here are the ramifications of having an alternative mechanism for storing Oracle data relationships. Consider the possibilities. We would never need to embed the foreign key for the CUSTOMER table in the order record, since we could store the pointers in each customer row. Of course, we would never be able to perform a relational JOIN between the CUSTOMER and ORDER tables, but this would not really make any difference as long as we have maintained the ability to navigate between customers and orders.

There are one-way pointers from customers to orders, and we would not have a method to get from the ORDER table to the CUSTOMER table unless we embedded a pointer to the row that contains the customer for each order.

Implications of ADTs for Oracle database design

We can now see that the ability to support abstract data types provides us with a lot of powerful capabilities, including:

• Storing repeating groups within a cell of a table.
• Nesting tables within tables.
• Providing pointer-based navigation for relational Oracle.
• Representing aggregate objects.

Consider the ramifications of the third item. If we are allowed to bypass SQL, imagine the possibilities!

With Oracle 8, we can navigate a data model without having the overhead of joining tables. More importantly, we can represent complex objects. This means that we can precreate objects composed of subobjects without having to build them each time we want to see them. Since the aggregate objects have an independent existence, we can attach methods to these objects. In addition, we can save database overhead by precreating complex database objects, having them instantly available to the database. To illustrate this concept, let's create an order_form for a customer. When customers place an order, they telephone the order department and indicate the following:

• Their name, address, and payment method.
• Their choices of items and the quantities of each one.

In a traditional Oracle database, we would populate tables to represent this relationship. They would be the CUSTOMER, ORDER, ORDER_LINE, and ITEM tables. Creating the order_form would involve the following SQL:

SELECT

customer.first_name,

customer.last_name,

customer.street_address,

customer.city_address,

customer.zip_code,

customer.payment_method,

order.order_date,

item.item_description,

order_line.quantity_ordered,

item.item_price,

order_line.quantity_ordered * item.item_price

FROM

customer,

order,

order_line,

item

WHERE

order.customer_ID = customer.customer_ID

AND

order.order_ID = order_line.order_ID

AND

order_line.item_ID = item.item_ID

AND

customer.customer_ID = 123;

As you can clearly see, the re-creation of the data for an order form would involve a four-way table JOIN. Using the object/relational features of abstract data types, we can define a new object called order_form that contains all the pointers to the various components in the order form. Note that it is never necessary to rebuild an order_form object unless items are deleted or added to the order_form itself. For example, when the quantity_ordered column in the order_line table is changed, the order_form object will automatically pick up this new value when the order_line row is de-referenced.

Here's how it might work. We first need to define a pointer to return the row that corresponds to the customer who has placed the order. Let's assume that we have already created an ADT for the entire customer row and defined the CUSTOMER table as follows:

CREATE TABLE CUSTOMER (customer_data customer_adt);

We can now create a customer_ref type to hold the pointer to the customer:

CREATE TYPE customer_ref

AS TABLE OF customer_adt;

Since we will only be retrieving one order row, we can do the same thing for the ORDER table row.

CREATE TYPE order_ref

AS TABLE OF order_adt;

Therefore, the first component of the order_form object will be the reference to the customer and order rows:

CREATE TABLE order_form (

customer customer_ref,

order order_ref);

Now we have the customer and order data, so we need to establish the pointers to represent the many-to-many relationship between the ORDER and ITEM tables. Let's start by defining a repeating group of all of the order_lines for each order:

CREATE TYPE item_list

AS TABLE OF order_line;

Having defined the item_list, let's take a stab at defining an order_form:

CREATE TABLE order_form (

customer customer_ref,

order order_ref,

lines item_list);

We'll need to establish pointers to all the items referenced in the order_line table. But how can we do this? We do not know the item_ID numbers that participate in the order until we have retrieved the order_line rows. In this case, we need to establish owner pointers inside each order_line row so that we will be able to de-reference the ITEM table. Let's assume that the line_item table has been defined as shown below to include a reference pointer to the ITEM table:

/* example of an owner pointer */

CREATE TYPE item_ref

AS TABLE of item;9

CREATE TABLE line_item (

order_ID integer,

item_ID integer,

item_pointer item_ref,

quantity_ordered integer);

So, let's see how we could display all the data for the order_form object:

CREATE TABLE order_form (

customer customer_ref,

order order_ref,

lines item_list);

SELECT

DEREF(customer.customer_last_name),

DEREF . . .

DEREF(order.order_date),

DEREF(item_list.item_name), /* if item is a foreign key in line item */

DEREF(order.quantity_ordered)

DEREF(DEREF(order_line.item_pointer)) /* this returns item data */

Pointer twizzling in Oracle

There's another remarkable facet to storing a pointer to an array of pointers (**char in C++). Since we have a pseudo-table with all the pointers for rows in a table, we can change the sequence of these row references without ever accessing the rows themselves. In object-oriented parlance, this concept is known as "pointer twizzling."

For example, assume that we have defined our CUSTOMER table as in the listing above. Since the references to the orders are stored in a VARRAY, we could write a PL/SQL routine to change the sequence that the rows are accessed without ever touching the ORDER table.

Oracle aggregate objects and methods

It should now be apparent that the ability to use references can be a powerful tool for creating virtual objects. By virtual, we mean aggregate objects that do not contain any row data but consist entirely of pointers to rows within other tables

One of the huge benefits of object/relational architecture is the ability to move procedures out of application programs and into the database engine. In addition to providing a more secure repository for the code, this ability to tie data and behavior makes it easier to reuse routines. When combined with the ability to directly represent aggregate objects, we now have a framework for coupling all data processes directly with the object containing the data that will be manipulated.

Another nice feature of coupling data and behavior is the elimination of the code hunt. In order to take advantage of code reusability for database objects, the programmer must first know where to find the appropriate methods. By using a methods browser, the programmer can quickly scan the methods attached to each database object and find the proper method, thereby alleviating the need to rewrite the code.

Since the object/relational model represents all objects as tables, we will now be able to couple a stored procedure—a method—with its owner table. In this fashion, a table will know how to behave based on its methods.

Basic methods for all objects are created automatically in most object/relational databases, and these basic methods correspond to the INSERT, DELETE, and UPDATE SQL verbs. However, you can couple more complex methods to their target objects. For example, an order_form object might contain a method called check_payment_history, which performs detailed checks into the prior payment history for the customer who is placing the order.

Polymorphism in Oracle:  new potential

As you may recall from the basics of object orientation, polymorphism is a situation where the same method call will result in the invocation of a different process, depending upon the target object.

For instance, consider this simple example:

C = a + 1;

Mystring = 'hello' + 'there'

In this code snippet, the + operator performs radically different functions. In the first line, it adds two numbers; in the second line, it concatenates two strings.

As another example of polymorphism, a common method called spread_it_on might exist for hair_tonic objects as well as for floor_wax objects. Clearly, the process of spreading it on would be vastly different for each of these objects, and the object engine would call the appropriate method after determining the target object for the method:

• spread_it_on(floor_wax)—This call would invoke a method to apply the wax to the floor and use the buffing machine.
• spread_it_in(hair_tonic)—This call would invoke a method to direct the gentle application of the tonic upon the scalp.

Oracle does not yet support true polymorphism because identical member method names are not yet supported, but we may expect to see these features in a future release.

Conclusion

Oracle has done a tremendous job of slowly adding object support to their relational database product. Once the basic object features in Oracle 8 have become widely accepted by DBAs, we can expect future releases of Oracle to support more object-oriented features.