Understanding Objects and the Oracle9i Database - dummies

Understanding Objects and the Oracle9i Database

By Carol McCullough-Dieter, Ned Dana

Oracle9i technically can be called an object-relational database — that’s something that’s new to Oracle. This term means that Oracle9i contains all the features of a relational database combined with some of the features of an object-oriented database. The program is a hybrid, which means that you have some choices about how to set up your database design. This article covers the basic definitions of Oracle9i’s world of objects.

Defining an object

An object can be anything, sort of. In terms of Oracle9i, an object is a framework that defines

  • How data is stored
  • Where data is stored
  • What kind of data is stored
  • How to put data together into logical whole parts

That last item is the part that’s really interesting and unique to objects. In fact, if you prefer, Oracle9i allows you to define the first three items in the traditional relational database style and then gives you a way to map these relational tables into objects.

Objects — a life of their own

Think of an object as a holistic set of concepts. Your object contains data about a real-world thing, such as a car part. In addition to the data, your object contains information about what you can do with the data. It’s like having the car-parts data and the assembly instructions for the car.

An object can contain other objects. For example, you might define one object called PERSONAL_ADDRESS that contains up to three lines of a person’s address. Another object, called PERSONAL_INFO, contains a person’s name, Social Security number, and the PERSONAL_ADDRESS object for that person.

The scoop on types

Types are Oracle9i’s way of defining the format of an object column, an object table, a nested table, or an array. Types are like wrappers around an object, the data, the methods, and even relational tables or views. The basic syntax for defining a type is similar to the syntax for defining a table. Table 1 shows a list of the types of types that you can define with Oracle9i.

Table 1: Object-Relational Types




An object component that contains definitions of the methods (or functions) that can be performed on an object.


A collection of data, tables, and methods defined and manipulated as a unit.


A table mapped to an object.


A variable-length array, similar to a nested table but referenced and accessed as a set rather than as individual rows of data. This is also called VARRAY in some parts of Oracle’s documentation.


A table within a table or within some object. Oracle9i stores this type as though it were a relational table, but it can be used only in the context of the object.

Methods to their madness

Methods are self-contained bits of programming code that travel with an object, delivering parts or modifying data according to the method code. Methods are the heart of object-oriented technology.

Suppose that you are working with an object-relational database schema that contains information about making a model airplane. A model airplane has several subassemblies, such as the engine and the instrument panel, which are made up of individual parts. A change in any individual part may affect the assembly of the entire plane. If the fuel gauge in the cockpit is changed to a different diameter, for example, the hole drilled in the instrument panel must also be changed.

You can use object types to define the individual parts, the subassemblies, and the entire airplane. Object methods define how these object types interact. One program uses the object types and methods to handle changes in the data. Another program can use the objects and their associated methods to extract a complete instruction booklet for the plane.

Connecting relational tables with objects

Oracle9i is a hybrid of both relational and object-oriented databases. To allow you to combine the two to get the best of both worlds, Oracle9i provides two bridges between relational tables and objects:

  • Object view. An object view maps relational tables to an object. Like relational views, the object view does not have data of its own; it is merely a way of looking at the underlying tables. The object view allows you to use existing relational tables in an object-oriented way.
  • Object table. An object table is a table made up of rows that are themselves objects, or a table that has one or more columns with objects as their datatype. An object table is a way to collect groups of like objects and manipulate them with more traditional relational-table techniques. Object tables can have primary keys and indexes.

If you use object views, the underlying data resides in relational tables that can be updated with the usual SQL commands.

When you use object tables, the underlying data resides in objects. You must maintain data through those objects, usually by using a programming language such as Java or PL/SQL.

SQL has been extended to handle more object queries, inserts, updates, and deletions. Expect even more convenient extensions for objects in the future.