Advantages of Oracle Objects over Relational Tables

Oracle Objects extend the traditional Oracle relational database model by integrating object-oriented principles and features directly into the relational schema. This integration allows the database to represent real-world entities and complex data structures more naturally and intuitively.
Key Object-Oriented Features Provided by Oracle Objects:

1. Object Types:
   • Allow defining user-defined data types (UDTs) that encapsulate attributes and behaviors (methods).
   • Support inheritance, enabling subtype/supertype relationships.
2. Methods:
   • Can define methods within object types, encapsulating behavior alongside data.
3. Encapsulation:
   • Bundle attributes and operations together, improving data integrity and reusability.
4. Inheritance and Polymorphism:
   • Allow subtypes to inherit from base types, simplifying schema management.
   • Facilitate polymorphic behaviors through method overriding.
5. Object Tables and Collections:
   • Allow storage of objects directly in tables, or use object views.
   • Support collections like nested tables and varrays, enabling complex data structures.

Example of Creating Oracle Objects:

-- Define an object type
CREATE OR REPLACE TYPE employee_t AS OBJECT (
   emp_id NUMBER,
   emp_name VARCHAR2(50),
   emp_salary NUMBER,
   
   -- Method definition
   MEMBER FUNCTION annual_salary RETURN NUMBER
);
/

-- Implement the methods
CREATE OR REPLACE TYPE BODY employee_t AS
   MEMBER FUNCTION annual_salary RETURN NUMBER IS
   BEGIN
      RETURN emp_salary * 12;
   END;
END;
/

-- Create a table using the object type
CREATE TABLE employee_obj_tab OF employee_t;

1. Improved schema representation of complex real-world entities.
2. Easier management of hierarchical and inheritance-based relationships.
3. Enhanced code reuse and maintainability through encapsulation.
4. Improved performance and clarity when handling sophisticated data structures.

Practical Use-Cases:

1. Complex business logic encapsulation.
2. Geographic Information Systems (GIS) and spatial data modeling.
3. Multimedia and rich content management.
4. Advanced applications needing a clear representation of entities and their relationships.

Summary: Oracle Objects blend object-oriented principles with traditional relational database structures, providing powerful tools for modeling and managing complex data structures in a relational database environment like Oracle 19c.

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.

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 1) arrays and 2) 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:

CREATE TYPE PHONE_ARRAY AS 
VARRAY (10) OF VARCHAR2(15)

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

CREATE TYPE PHONE_NUMBER_LIST
AS TABLE OF VARCHAR2(15)

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

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.