Lesson 1

Object references are useful for uniquely identifying and locating an object. Within Oracle, a reference to an object within an object table may be obtained by using the REF pointer. An object type may be declared as a reference (REF) to another object type.

• Module Objectives
  When you have completed this module, you will be able to:
  1. Describe the Structured Query Language (SQL) enhancements that allow you to query related object tables
  2. Detect dangling references
  3. Write a query using a DEREF data type to retrieve related data
  4. Determine when PL/SQL is appropriate or required for querying object tables
  5. Write a PL/SQL block to retrieve data from an object table

In Oracle Database 23ai, object references (`REF`) continue to function as they did in earlier versions, but their usage now integrates more seamlessly with modern features like JSON-relational duality, graph data models, and autonomous in-database operations.
🔹What Is a REF in Oracle?
A `REF` is a pointer to an object instance stored in an object table or as a column in another table. It provides a way to reference and navigate complex object-relational structures.
🔹 How Object References Are Used in Oracle 23ai

1. Referencing Objects in Object Tables

   You can still declare a column in a table as a REF to an object type. This is often used to represent relationships (e.g., parent-child, owner-asset).

   
   

   -- Define an object type
   CREATE TYPE Department_t AS OBJECT (
      dept_id   NUMBER,
      name      VARCHAR2(50)
   );
   
   -- Create an object table
   CREATE TABLE departments OF Department_t;
   
   -- Create another table with a REF to Department_t
   CREATE TABLE employees (
      emp_id      NUMBER,
      emp_name    VARCHAR2(50),
      dept_ref    REF Department_t
   );
   

2. Dereferencing with the DEREF() Function

   You can access the contents of a referenced object using DEREF():

   SELECT e.emp_name, DEREF(e.dept_ref).name AS department_name
   FROM employees e;
   

🔹 Enhancements in 23ai Context
While the `REF` and `DEREF` mechanisms are not themselves new in 23ai, their relevance persists in modern scenarios:
✅ Modern Use Cases Include:

• Modeling complex hierarchies (e.g., organization charts, bill of materials)
• Implementing navigable relationships in object-relational schemas
• Maintaining referential integrity between object types without foreign keys

In 23ai, you can represent object relationships as part of a JSON Duality View:

• While JSON doesn’t directly support REF, underlying object references can be exposed as part of a JSON-based API.
• You may use REF internally and map these relationships externally to JSON for use in applications.

🧠 Integration with AI Features
*Object references can be used in tandem with Oracle Machine Learning (OML) or Vector Search when managing entities with metadata or embeddings linked via `REF`s. 🔹 Summary

Feature	Description
REF	Pointer to an object row in an object table
DEREF	Retrieves object content via the reference
23ai Compatibility	Fully supported and integrable with AI, JSON, and relational features
Use Cases	Graph modeling, navigable object relationships, smart APIs

Here's a complete Oracle 23ai example showing how a `REF` column can link two objects—`Department_t` and `Employee_t`—and how you can query the relationship using a JSON duality view, which is a modern feature in Oracle 23ai.
✅ Step-by-Step Example

1. 🔹Define Object Types
   

   -- Department object type
   CREATE OR REPLACE TYPE Department_t AS OBJECT (
     dept_id   NUMBER,
     name      VARCHAR2(50)
   );
   
   -- Employee object type with a REF to Department
   CREATE OR REPLACE TYPE Employee_t AS OBJECT (
     emp_id     NUMBER,
     emp_name   VARCHAR2(50),
     dept_ref   REF Department_t
   );
   

2. 🔹 Create Object Tables
   

   -- Table to hold departments
   CREATE TABLE department_obj_table OF Department_t
     OBJECT IDENTIFIER IS PRIMARY KEY;
   
   -- Table to hold employees, referencing departments
   CREATE TABLE employee_obj_table OF Employee_t;
   

3. 🔹Insert Sample Data
   

   -- Insert a department
   INSERT INTO department_obj_table VALUES (Department_t(10, 'HR'));
   
   -- Insert an employee with a REF to HR department
   DECLARE
     d_ref REF Department_t;
   BEGIN
     SELECT REF(d) INTO d_ref FROM department_obj_table d WHERE d.dept_id = 10;
     INSERT INTO employee_obj_table VALUES (Employee_t(1001, 'Alice', d_ref));
   END;
   /
   

4. 🔹Create a JSON Duality View
   

   Here, we expose the employee object along with its department name by using DEREF().

   
   

   CREATE OR REPLACE JSON DUALITY VIEW emp_view
   AS JSON
     SELECT 
       e.emp_id        AS "empId",
       e.emp_name      AS "name",
       DEREF(e.dept_ref).dept_id AS "department.deptId",
       DEREF(e.dept_ref).name    AS "department.name"
     FROM employee_obj_table e;
   

5. 🔹 Query the JSON Duality View
   

   SELECT JSON_SERIALIZE(emp_view PRETTY)
   FROM emp_view;
   

   
       ✅ Result (Pretty JSON Output):

   {
     "empId" : 1001,
     "name" : "Alice",
     "department" : {
       "deptId" : 10,
       "name" : "HR"
     }
   }
   

Feature	Usage
REF	Used to store a reference to a department in the employee object
DEREF()	Used to extract department details in the JSON view
JSON Duality View	Combines relational data and object references into structured JSON for APIs or apps

Generalized invocation syntax is now supported. Therefore, a member method in a subtype can statically invoke (dispatch) a member method in any supertype in the supertype hierarchy of the current subtype, including the subtype's immediate supertype.
Generalized Invocation
Generalized invocation provides a mechanism to invoke a method of a supertype or a parent type, rather than the specific subtype member method. Example 2 below demonstrates this using the following syntax:

(SELF AS person_typ).show

The student_typ show method first calls the person_typ show method to do the common actions and then does its own specific action, which is to append '--Major:' to the value returned by the person_typ show method. This way, overriding subtype methods can call corresponding overriding parent type methods to do the common actions before doing their own specific actions. Methods are invoked just like normal member methods, except that the type name after AS should be the type name of the parent type of the type that the expression evaluates to. In Example 2, there is an implicit SELF argument just like the implicit self argument of a normal member method invocation. In this case, it invokes the person_typ show method rather than the specific student_typ show method.
Example 2: Using Generalized Invocation

DECLARE
myvar student_typ := student_typ(100, 'Sam', '6505556666', 100, 'Math');
name VARCHAR2(100);
BEGIN
name := (myvar AS person_typ).show; --Generalized invocation
END;
/			

In the next lesson, we will begin describing the SQL enhancements that allow you to query related object tables.