Lesson 7

Oracle Database 23c introduces several SQL enhancements that facilitate querying related object tables using an object-relational approach. Notable features include:

• JSON Relational Duality: This feature allows data to be transparently accessed and updated as either JSON documents or relational tables, simplifying the integration between application data structures and database storage.
• Support for the ISO/IEC SQL Property Graph Queries (SQL/PGQ) Standard: Oracle Database now includes comprehensive support for property graph queries, enabling developers to build graph applications using SQL and existing development tools.
• Native Representation of Graphs in Oracle Database: The database now has native support for property graph data structures and graph queries, providing an intuitive way to analyze relationships and dependencies within data.

These enhancements make it easier to work with complex data relationships and integrate different data representations within Oracle Database 23c.

You now have an even more in-depth understanding of the object-relational approach within Oracle. Now that you have completed this module, you should be able to:

1. Describe the 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

Oracle 19c continues to provide support for the DEREF operator. This operator is used in conjunction with object-relational database features to dereference a reference (REF) type, enabling retrieval of the object it points to.
Key Points About DEREF in Oracle 19c:

1. Purpose: The DEREF operator retrieves the object instance that a REF type column or variable points to. This is useful when you are working with object-relational features where tables store references to rows in other tables containing object types.
2. Syntax:

         SELECT DEREF(ref_column)
         FROM table_name;
       

   • ref_column is the column of type REF.
   • The result is the object instance that the REF points to.
3. Use Case: The DEREF operator is typically used when you have object-relational designs in your schema, such as:
   • Object Types stored in tables.
   • A table containing REF columns pointing to rows in another table.
4. Object-Relational Example: Let's say you have an object type and two tables:

         CREATE TYPE employee_t AS OBJECT (
           emp_id NUMBER,
           emp_name VARCHAR2(100),
           department_id NUMBER
         );
   
         CREATE TABLE departments (
           dept_id NUMBER PRIMARY KEY,
           dept_name VARCHAR2(100),
           manager REF employee_t
         );
   
         CREATE TABLE employees OF employee_t;
       

   • The manager column in the departments table is a REF to rows in the employees table.
   To retrieve the details of the manager object, you can use:

         SELECT d.dept_name, DEREF(d.manager)
         FROM departments d;
       

5. Dereferencing in PL/SQL: If you're working in PL/SQL, you can use DEREF in a similar fashion:
   

         DECLARE
           emp_ref REF employee_t;
           emp employee_t;
         BEGIN
           SELECT manager INTO emp_ref FROM departments WHERE dept_id = 1;
           emp := DEREF(emp_ref);
           DBMS_OUTPUT.PUT_LINE(emp.emp_name);
         END;
       

6. Compatibility:
   • Oracle 19c maintains backward compatibility for object-relational features like DEREF, but such features are not widely used in modern database designs. The focus has shifted more toward relational models, JSON, and XML capabilities.
   • If you plan to use DEREF, ensure your schema design and application requirements justify its use.

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

1. Correlation argument: A correlation argument is a parameter, which is passed when a function is used, e.g. the VALUE finction requires the table alias to be passed as a parameter or correlation argument.
2. Correlation variable: A correlation variable is a parameter, which is passed when a function is used, e.g. the VALUE finction requires the table alias to be passed as a parameter or correlation variable.
3. Pre-delete trigger: A pre-delete trigger is a trigger which is fired before a record is deleted from a table.

As time goes by, you will need to be able to age related data from multiple tables in a consistent fashion. You will need to be able to remove data without worrying that there are dangling references that will invalidate referential integrity along the way. By using reference partitions, you are unifying the ways in which the data is divided across multiple tables, and that significantly eases the maintenance burden. Another new partition type available with Oracle 11g is the interval partition. In interval partitioning, you do not specify the specific range values for each partition; instead, you specify the duration of the interval. That is, instead of specifying that partition 1 ends on January 31 and partition 2 ends on February 29, you specify that each partition is one month long. When a new row is inserted, Oracle will determine which partition to place the row in based on the interval definition. If you have not created a partition for that month, the database will automatically create a new one. Use interval partitions with caution. You will need to perform the data value constraint checks yourself prior to inserting the row, if you do not, you may end up with unwanted partitions that were created simply because a data-entry person mistakenly entered "2098" for a year instead of "2008".
The interval partitioned version of the INVOICE_HEADERS table is shown in the following listing:

create table INVOICE_HEADERS
(
InvoiceNum number,
CustomerNum number,
Invoice_Date date
)
partition by range (Invoice_Date)
interval (numtoyminterval(1,'MONTH'))
(
partition p0701 values
less than (to_date('2007-02-01','yyyy-mm-dd'))
);

Note that if you rely on interval partitioning automatically creating your partitions for you, your application developers cannot rely on consistent partition names because Oracle will create a system-generated name for each partition it automatically creates.

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, you will learn about nested tables and varrays and techniques to query them.