Object Tables   «Prev  Next»

Lesson 2 Using Oracle objects
Objective Advantages of Oracle Objects over Relational Tables

Advantages of Oracle Objects over Relational Tables

Oracle Objects are an extension of Oracle's relational database model, integrating object-oriented features into the traditional SQL model. This Object-Relational model provides several advantages over purely relational tables:
  1. Complex Data Types: Oracle Objects allow you to create user-defined data types, also known as Abstract Data Types (ADTs). ADTs can encapsulate both data structure and methods that operate on that data into a single unit. This enables complex real-world entities to be modeled more naturally and efficiently.
  2. Data Encapsulation: Oracle Objects support encapsulation, one of the main principles of object-oriented programming. Encapsulation helps in bundling the data and the methods that use the data into a single unit, improving data security and integrity.
  3. Type Inheritance: Oracle Objects support type inheritance. This means a subtype can inherit characteristics from a supertype, allowing for better code reusability and a hierarchical data model. In a purely relational model, this kind of hierarchy is difficult to achieve.
  4. Object Identity: Oracle Objects are identified by system-generated unique object identifiers (OIDs), which provide a way to uniquely identify each object in the database. This is distinct from relational tables where a row doesn't inherently have a unique identity unless explicitly defined by a primary key.
  5. Object Views: Object views allow existing relational data to be accessed as objects. This helps transition a relational model to an object-relational model without changing the existing application code.
  6. Nested Tables and VARRAYs: Oracle Objects also support collections such as nested tables and VARRAYs (variable-size arrays). These are used to represent multi-valued attributes and can be nested within rows, enabling complex data structures and relationships.
  7. Methods: Oracle Objects allow methods to be defined within ADTs. These are procedures or functions that are used to access or manipulate the data attributes of the object, enabling encapsulation of business rules and logic within the database.

Oracle Objects bring the benefits of an object-oriented programming paradigm to the robust, efficient framework of a relational database system. They provide enhanced modeling capabilities, reusability, and encapsulation, which can lead to more efficient development and better data integrity. However, the best use of Oracle Objects depends on the specific needs and complexity of your application, and they are not a replacement for relational tables but rather an extension of them.

Why should we use Oracle objects?

A relational database management system (RDBMS) provides structures for storing and retrieving data. However, the application developer must craft the operations needed for each application. This means that you must recode the operations often, even though they may be very similar to operations that have already been coded for applications within the same enterprise. A number of significant limitations exist with the relational approach.
These include limitations in representing encapsulation[1], composition[2], aggregation[3], and inheritance[4].
Let us describe the impact of these limitations.
Select the link below if you would like to review Oracle objects.

Object-oriented Design

The main difference between object-oriented design and relational design is that object-oriented design allows you to not only define what data you can store in your database, you also define a set of actions (methods) associated with the data.
You can transfer a relational database table design into an object-oriented design by making the set of columns in a table into an object type and defining an object table. There is a different name for a foreign key within object-oriented design. It is called a reference, or REF. This is similar to a foreign key column because it points to a row in another table.
It is different because it uses an internal format called an object-id (OID) to store the pointer rather than a readable value like a
  1. NUMBER or
  2. VARCHAR2.
There are two object-oriented objects that are useful to store small sets of repeating data within a single column in a table. These are called arrays and nested tables. The two objects are very similar, but there are some subtle differences. The table below outlines the similarities and differences between these two objects.

Array Nested table
Has a predefined number of rows Has an unlimited number of rows
Rows can be retrieved using their position (row 1, 2, and so on) Rows cannot be retrieved using their position. However, you can add an index onto a nested table.
Cannot be queried using SQL, you must use PL/SQL Can be queried using SQL via special extensions for "flattening" a nested table.

Here is an example of an array of ten phone numbers:

Here is an example of creating a nested table of phone numbers:

You can use an object type as the datatype for a nested table or an array.

Drawbacks to using relational tables

Using relational tables has the following drawbacks:
  1. Encapsulation: Relational tables are excellent for modeling a structure of relationships. However, they fail to represent real-world objects effectively. For example, when you sell items from a pet store in the real world, you expect to be able to sum the line items to find the total cost to the customer and perhaps to retrieve information about the customer who placed the order, such as his or her buying history and payment patterns. Relational tables do not allow you to do this.
  2. Composition: Relational tables cannot capture composition. For example, within a relational table, the notion of an address as a structure made up of individual columns for number, street, city, state, and zip code cannot be represented.
  3. Aggregation: Complex part-whole relationships cannot be represented within relational tables.
  4. Inheritance: Relational tables do not support inheritance, in which each "child" attribute inherits the characteristics of its parent attribute.
The bottom line is, even though Oracle RDBMS allows you to store and retrieve data, you must code the operations needed for each application. This can be avoided by using Oracle objects. In the next lesson, you will learn about the different terms for Oracle objects.

[1]Encapsulation: Encapsulation is the concept of information hiding. Encapsulation asserts that you can only "get at" the contents of the object using predefined functions. This allows the programmer to retain control of the data and helps reduce the impact on changes to the schema.
[2]Composition: A composition embodies the concept of a column within a table is a combination of several composite columns. This concept cannot be handled within a relational database. In Oracle, it is possible with object types and collections such as varrays and nested table.
[3]Aggregation: Aggregation occurs where one object is composed, at least in part, of other objects; you may call it a part-of relationship. A tail is a part-of a dog or a piston is a part-of an engine. This concept is implemented within collections.
[4]Inheritance: Inheritance is a technique used within object-oriented development to create objects that contain generalized attributes and behavior for groups of related objects. The more general objects created using inheritance are referred to as super-types. The objects that "inherit" from the super-types (that is, they are more specific cases of the super-type) are called subtypes.

Oracle PL/SQL