Lesson 1

Professional DBAs that have worked with real-world business applications may think, how does the object-relational approach work with modern Oracle? Oracle8i was an object-relational database management system (ORDBMS), which means that users could define additional kinds of data, specifying both the structure of the data and the ways of operating on the data and use these types within the relational model. This approach added value to the data stored within a database.
User-defined data types make it easier for application developers to work with complex data such as images, audio, and video. Object types store structured business data in its natural form and allow applications to retrieve it that way. For this reason, they work efficiently with applications developed using oject-oriented programming techniques.
The object-relational model allows users to define new sets of data types and models drawn from the object programming languages. This means you can create persistent objects^[1] within the database and access them through an API (application programming interface) from C++, Java, and other programming languages. This object-relational approach^[2] specifies both the structure of the data and the methods of operating on the data. We will look at the details of defining methods later in the course. In the next lesson, we will begin by explaining the reasons to use Oracle objects.

• Module Objectives
  When you have completed this module, you will 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 Structured Query Language (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

When Oracle8i came onto the scene in 1999, it marked a significant shift by introducing an object-relational database management system (ORDBMS). This was a hybrid approach, marrying the structured, tabular world of relational databases with the flexibility of object-oriented programming. As you mentioned, it allowed users to:

Fast forward to today March 08, 2025, and Oracle’s ORDBMS features have matured significantly (we’re talking Oracle 23ai and beyond). The object-relational approach is still alive and kicking, though its adoption and practical utility depend heavily on the use case. Here’s how it fits into modern Oracle and what DBAs might think:

1. Nested Tables and Varrays
   • A "Purchase Order" table might have a nested table column "Line Items," where each row in the parent table holds a collection of item details (product ID, quantity, price).
   • DBAs love this for hierarchical data but curse it when indexing and querying get tricky—more on that later.
2. Object Types and Methods

   CREATE TYPE Address_T AS OBJECT (
     street VARCHAR2(100),
     city VARCHAR2(50),
     zip VARCHAR2(10),
     MEMBER FUNCTION get_full_address RETURN VARCHAR2
   );
   /
   CREATE TYPE BODY Address_T AS
     MEMBER FUNCTION get_full_address RETURN VARCHAR2 IS
     BEGIN
       RETURN street || ', ' || city || ' ' || zip;
     END;
   END;
   /
   CREATE TABLE customers (
     id NUMBER,
     name VARCHAR2(50),
     address Address_T
   );
   

   
   Here, the Address_T type bundles attributes and logic, and a table can store it directly. Querying SELECT c.address.get_full_address() FROM customers c; pulls the formatted address—clean and elegant.
3. Integration with Modern Apps

   As you noted, this approach shines with object-oriented programming. Modern Oracle supports JSON, XML, and spatial data natively, but object types still have a niche for structured business data. Developers using frameworks like Java’s Hibernate or Python’s SQLAlchemy can map these types to classes, reducing impedance mismatch between the database and application layers.

Professional DBAs who’ve wrestled with this in production environments have mixed feelings:

1. Pros:
   • Natural Data Modeling: For complex domains (e.g., healthcare records, financial instruments), object types and nested tables mirror real-world entities better than normalized tables.
   • Developer Productivity: App devs save time by working with data in its native structure—no need to manually reconstruct objects from JOINs.
   • Encapsulation: Methods in object types enforce business logic at the database level, which can improve consistency.
2. Cons:
   • Performance Overhead: Nested tables and object types can complicate indexing and querying. A deeply nested structure might require TABLE() casts or MULTISET operations, which aren’t as straightforward as a B-tree index on a flat table.
   • Maintenance Pain: Schema changes to object types ripple through dependent tables and code, making evolution harder than tweaking a simple relational design.
   • Adoption Lag: Many businesses stick to pure relational models because they’re more familiar, and tools like BI platforms often assume flat data.

Does It Still Add Value? Absolutely, but it’s situational. In modern Oracle, the object-relational features are most valuable when:

• You’re dealing with inherently hierarchical or complex data (e.g., geospatial apps, multimedia repositories).
• Your team embraces object-oriented design and can leverage PL/SQL or Java stored procedures to exploit methods.
• You’re not overly constrained by performance tuning or reporting needs that favor traditional relational approaches.

For example, a telecom company might use nested tables to store call detail records within customer profiles, while a retail app might skip them entirely for simpler key-value JSON storage (a feature Oracle’s added in recent years).
Closing Thoughts The object-relational approach in Oracle8i laid the groundwork for flexibility that’s still relevant in 2025. It’s not the default for every application. Pure relational and NoSQL-style features often win out, but for DBAs and developers tackling specific business problems, it’s a powerful tool in the kit.

Oracle's implementation of the EXCEPTION datatype has some limitations. An exception consists of an identifier (a name) with which you can associate a number and a message. You can raise the exception, and you can handle it. That is all there is to it.. Consider the way that Java approaches this same situation: all errors derive from a single Exception class. You can extend that class, adding other characteristics about an exception that you want to keep track of (error stack, context-sensitive data, etc.). An object instantiated from an Exception class is like any other kind of object in Java. You certainly can pass it as an argument to a method
PL/SQL does not let you do that with its native exceptions. This fact should not stop you from implementing your own exception object. You can do so with Oracle object types or with a relational table of error information. Regardless of implementation path, the key insight here is to distinguish between an error definition (error code is -1403, name is no data found, cause is "implicit cursor did not find at least one row") and a particular instance of that error (I tried to select a company for this name and did not find any rows.). There is, in other words, just one definition of the NO_DATA_FOUND exception, but there are many different instances or occurrences of that exception. Oracle does not distinguish between these two representations of an error, but we should and we need to.
Here is an example of a simple exception object hierarchy to demonstrate the point. First, the base object type for all exceptions:

/* File on web: exception.ot */
CREATE TYPE exception_t AS OBJECT (
name VARCHAR2(100),
code INTEGER,
description VARCHAR2(4000),
help_text VARCHAR2(4000),
recommendation VARCHAR2(4000),
error_stack CLOB,
call_stack CLOB,
created_on DATE,
created_by VARCHAR2(100)
)
NOT FINAL;
/

[1]Persistent object: An object that is stored or saved. Within Oracle, an object is stored within the database.

[2]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.