Lesson 9

This module discussed the object-relational approach using Oracle. This module discussed object details that helped you build the program logic using Oracle PL/SQL. Now that you have completed this module, you should be able to:

1. Understand the reasons to use Oracle objects versus relational tables
2. Understand the different terms for Oracle objects
3. Describe the SQL techniques that allow you to query object tables
4. Determine when to use collections and variable size arrays
5. Determine when to use nested tables in querying
6. Write SQL to query object tables

In Oracle 23ai, the concept of object-relational databases (ORD) is still supported, but its strategic role has evolved significantly over the years. While Oracle continues to offer object-relational features for backward compatibility and specific use cases, its mainstream development focus has shifted toward modern data models like JSON, spatial, graph, and AI/ML integrations.
Oracle introduced object-relational features in the late 1990s (Oracle 8/8i) to combine the benefits of:

• Relational model (tables, rows, columns)
• With object-oriented principles (user-defined types, inheritance, methods)

These features include:

• Object types (ADT)
• Object tables
• REFs (pointers to objects)
• Nested tables and VARRAYs
• Inheritance and method overloading

🔄 Oracle 23ai: Current Support for Object-Relational Features
Oracle 23ai still supports object-relational constructs, including:

Feature	Status in Oracle 23ai
User-defined object types	✅ Supported
Nested tables / VARRAYs	✅ Supported
Object tables	✅ Supported
REFs	✅ Supported
Table inheritance	✅ Supported

However, their usage is niche, most modern Oracle development now uses:

• Relational tables + JSON columns
• PL/SQL packages for procedural logic
• JSON-based APIs and Oracle REST Data Services (ORDS)

📌 Key Differences in Focus (Object-Relational vs. Modern Oracle 23ai)

Feature Set	Object-Relational Model	Oracle 23ai Focus
Data types	Object types, REFs, VARRAYs	JSON, XML, Graph, Spatial
Query interface	SQL + Object extensions	SQL + JSON path expressions (e.g., JSON_TABLE)
Integration	PL/SQL	REST APIs, GraphQL, AI vector search
Application models	OOP-style modeling	Document-store and event-driven modeling

You might still use object-relational constructs if:

• You are modernizing a legacy Oracle app built on object types.
• You need tight PL/SQL integration with complex types.
• You’re modeling real-world entities with nested hierarchies (e.g., CAD systems, bills of material).

But for new development, Oracle recommends:

• JSON-relational duality views
• Native JSON data types
• Graph and spatial models
• AI Vector data types

🔚 Summary
Oracle 23ai maintains full support for object-relational database features for backward compatibility and specialized use cases. However, the primary development paradigm has shifted toward more flexible, cloud-optimized, and AI-integrated models like JSON, REST, and vector search. Developers are encouraged to use JSON Duality Views and Document Store APIs for new applications rather than object-relational constructs.

Here’s a side-by-side usage example comparing Oracle’s traditional `OBJECT TYPE` approach with the modern `JSON_TABLE` approach in Oracle 23ai.
🎯 Use Case:
Store and query customer data with nested address details.
🧱 1. OBJECT TYPE Approach (Object-Relational Model)

-- Define object types
CREATE TYPE address_typ AS OBJECT (
  street   VARCHAR2(50),
  city     VARCHAR2(30),
  zip      VARCHAR2(10)
);

CREATE TYPE customer_typ AS OBJECT (
  id       NUMBER,
  name     VARCHAR2(100),
  address  address_typ
);

-- Create object table
CREATE TABLE customer_obj_tab OF customer_typ;

-- Insert sample data
INSERT INTO customer_obj_tab VALUES (
  customer_typ(101, 'Alice Smith', address_typ('123 Main St', 'Orlando', '32801'))
);

-- Query object attributes
SELECT c.id,
       c.name,
       c.address.city AS city
FROM customer_obj_tab c
WHERE c.address.zip = '32801';

📦 2. JSON_TABLE Approach (Modern JSON Model in Oracle 23ai)

-- Create table with JSON column
CREATE TABLE customer_json_tab (
  id      NUMBER,
  payload JSON
);

-- Insert JSON data
INSERT INTO customer_json_tab VALUES (
  101,
  JSON_OBJECT(
    'name' VALUE 'Alice Smith',
    'address' VALUE JSON_OBJECT(
      'street' VALUE '123 Main St',
      'city' VALUE 'Orlando',
      'zip' VALUE '32801'
    )
  )
);

-- Query nested JSON using JSON_TABLE
SELECT jt.name,
       jt.city
FROM customer_json_tab c,
     JSON_TABLE(c.payload, '$'
       COLUMNS (
         name  VARCHAR2(100) PATH '$.name',
         city  VARCHAR2(30)  PATH '$.address.city',
         zip   VARCHAR2(10)  PATH '$.address.zip'
       )
     ) jt
WHERE jt.zip = '32801';

🧮 Comparison Table

Feature	OBJECT TYPE	JSON_TABLE / JSON Model
Data Format	Strongly-typed relational objects	JSON documents (schema-flexible)
Definition Required	Yes – with CREATE TYPE statements	No – inline JSON, schema optional
Query Interface	SQL dot notation on object attributes	SQL with JSON_TABLE() and path syntax
Flexibility	Rigid (must define structure up front)	High (schema-less or schema-on-read)
Best for	Legacy Oracle systems, strict modeling	Modern apps, REST APIs, AI integration

🔚 Summary

• Use OBJECT TYPE when your system depends on strong data typing, inheritance, and tight PL/SQL integration.
• Use JSON_TABLE for modern applications that benefit from schema flexibility, easier REST integration, and cloud-native JSON workflows in Oracle 23ai.

In this module, you were introduced to the following glossary terms:

1. 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.
2. Application Programming Interface: An API is designed to allow quick and easy integration of different programming languages like C, Java, and so on, with the Oracle 8i database and its development environment.
3. 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 Oracle8i, it is possible with object types and collections such as varrays and nested table.
4. Dot notation: To reference individual fields within the record, use the dot notation. A dot (.) serves as the selector for the individual field name.
5. 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.
6. Enumeration: An enumeration is a data type which can be given a finite (usually small) set of named values. Therefore, enumerations are useful for representing real world values that naturally have a finite set of values, each of which has a meaningful name.
7. Homogenous: Homogenous means elements of the same data type.
8. 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.
9. Object-relational: The object-relational model allows users to define object types, specifying both the structure of the data and the methods of operating on the data, and to use these datatypes within the relational model.
10. Persistent object: An object that is stored or saved. Within Oracle, an object is stored within the database.
11. REPLACE: CREATE OR REPLACE is the syntax for creating procedures, functions, or object types. It means that if the object type / procedure already exists within the database, the DDL statement should replace the existing object type / procedure.
12. Sparse: Sparse means lesser number of elements or records.
13. Transient object: A transient object is an instance of an object type. It may have an object identifier, and it has a lifetime which is determined by the application when the instance is created. The application can also delete a transient object at any time. Transient objects are often created by the application to store temporary values for computation. Transient objects cannot be converted to persistent objects. Their role is fixed at the time they are instantiated.

Click the Quiz link below to take a multiple-choice quiz about the material we have covered in this module.
Querying Object Tables - Quiz
In the next module, we will go into more detail on querying related object tables.