Oracle 26ai Object-Relational Queries: Module Review

Review the eight lessons as one workflow

1. Lesson 1: Introduction to querying object tables established the object-relational vocabulary and distinguished type metadata from stored values. Start here if a table, object type and application class still seem interchangeable.
2. Lesson 2: Using Oracle objects compared reusable type interfaces with relational designs. Its salary method, composition discussion and inheritance example showed benefits together with their limits.
3. Lesson 3: Object-oriented terms followed an address and customer through attributes, methods, construction and storage. Its four modern diagrams distinguish a blueprint, a composed customer, method implementation and three places to hold values.
4. Lesson 4: Query object tables developed the different projections and relationships used in object queries. Revisit it when deciding whether you need attributes, a complete object or a reference to a row object.
5. Lesson 5: Collections and VARRAYs explained bounded collections, initialization, element access and collection operations. A declared maximum describes a size contract, not the number of values already present.
6. Lesson 6: Querying nested tables separated element types, collection types and parent storage. Its three diagrams and sales examples covered unnesting, parent selection, totals, empty collections and element updates.
7. Lesson 7: Practical SQL examples used animal rows to demonstrate projections, predicates, explicit ordering and PL/SQL processing. It distinguished scalar SELECT INTO exceptions from DML cursor status.
8. Lesson 8: Querying complex SQL objects followed nested address paths and exposed grouped results through named view columns. It also distinguished absent objects, null attributes and dangling references.

Separate the definition, instance and storage

Use methods and inheritance with explicit rules

A type specification declares its interface; a type body implements declared methods. A member method operates on an invoking instance through SELF. A static method belongs to the type without requiring that instance context. Constructors initialize values. The address get_city method in Lesson 3 demonstrated behavior attached to a type, while the annual_salary method in Lesson 2 expressed a particular calculation.

Methods do not make Oracle object attributes private or guarantee that every write uses the method. A salary calculation is also not a salary constraint. Separate reusable behavior from rules that must hold for every stored row, then enforce those rules through appropriate constraints, privileges and controlled APIs. A transient constructor value does not automatically inherit the constraints of every table using its type. See Oracle's method documentation.

Composition means containing another value, such as a customer containing an address. Type inheritance means a subtype derives attributes and methods from a supertype. Oracle permits subtypes UNDER a supertype declared NOT FINAL, subject to its type rules. This is SQL object type inheritance, not a general promise of table inheritance. A nested address attribute demonstrates composition, regardless of whether either type defines methods.

Choose the query's result shape first

A report asking for customer names and cities needs scalar projections. An application accepting a complete customer value may need an object projection. A reference-based relationship has a different result and lifetime from a copied value. Write down what one output row represents before adding collections or grouping; this prevents a valid SQL statement from answering the wrong question.

For an object table, VALUE(alias) retrieves a complete row-object value. Selecting alias.attribute retrieves individual components. In a relational table with an object column, the path includes that column. Object attribute paths use a table alias, so c.address.city identifies the declared address attribute of the row represented by c. The dots follow structure; they do not establish a new table join.

REF designates a referenceable row object, and DEREF obtains the referenced object value. Reference scope and target existence are distinct: a scoped REF can still be dangling. IS DANGLING tests that condition. An embedded address value is not a REF, so an address predicate is not a dangling-reference test. Oracle documents the rules under SQL object types and references.

A compact review script

Use a disposable training schema with unused names, CREATE TYPE and CREATE TABLE privileges, and sufficient tablespace quota. The following independent example uses lesson9 names rather than assuming that earlier practice tables exist. Run the definitions in order. The slash submits type definitions in SQL*Plus-style script mode; it is a client command, not part of every driver-issued SQL statement. DDL commits transactions.

CREATE TYPE lesson9_address_t AS OBJECT (
  city        VARCHAR2(40 CHAR),
  postal_code VARCHAR2(20 CHAR)
);
/
CREATE TYPE lesson9_phone_nt AS TABLE OF VARCHAR2(30 CHAR);
/
CREATE TYPE lesson9_customer_t AS OBJECT (
  customer_id NUMBER,
  name        VARCHAR2(100 CHAR),
  address     lesson9_address_t,
  phones      lesson9_phone_nt
);
/
CREATE TABLE lesson9_customers OF lesson9_customer_t (
  customer_id PRIMARY KEY
) NESTED TABLE phones STORE AS lesson9_phones_store;

INSERT INTO lesson9_customers VALUES (
  lesson9_customer_t(101, 'Avery Lee',
    lesson9_address_t('Boston', '02101'),
    lesson9_phone_nt('617-555-0100', '617-555-0101'))
);
INSERT INTO lesson9_customers VALUES (
  lesson9_customer_t(102, 'Morgan Chen',
    lesson9_address_t('Seattle', '98101'),
    lesson9_phone_nt())
);
COMMIT;

SELECT c.customer_id, c.name, c.address.city AS city
FROM lesson9_customers c
ORDER BY c.customer_id;

SELECT VALUE(c) AS customer_value
FROM lesson9_customers c
WHERE c.customer_id = 101;

SELECT c.customer_id, p.COLUMN_VALUE AS phone
FROM lesson9_customers c, TABLE(c.phones) p
ORDER BY c.customer_id, p.COLUMN_VALUE;

The first query illustrates two scalar rows, with Boston for Avery and Seattle for Morgan. The second returns one typed customer value, whose display depends on the client. The third returns Avery's two phone strings as separate rows. Morgan contributes no element row because the supplied collection is empty. These results follow the inserted values; display spacing and object rendering are client choices.

The type definitions alone created neither customer rows nor phone values. The parent table's nested-table storage clause supplies storage for persisted elements. COLUMN_VALUE is appropriate because the collection contains scalar strings. For a collection of sale-detail objects, Lesson 6 instead projected element attributes such as product_id and detail_amount. Choose the projection from the declared element type.

Compare collection semantics

Collection order and SQL result order remain separate requirements. Specify ORDER BY for a report, and store an explicit sequence attribute when a nested collection needs durable business ordering. A PL/SQL collection subscript is also different from a database index used for storage access. A sparse nested table has gaps in its existing indexes, not simply a small number of elements.

A null collection differs from an initialized empty constructor such as lesson9_phone_nt(). Test both states when preserving parent rows or applying collection conditions. Ordinary unnesting asks for element rows; a parent with no contributing elements disappears from that result. Lesson 6 demonstrated Oracle's collection outer-join pattern for retaining those parents. An element predicate added afterward can change that behavior. Consult collection querying and DML for the exact operation.

Choose EXISTS when the question is whether a parent has a matching element. Choose unnesting when each matching element should be an output row. Joining two independent collections can multiply combinations rather than pair positions. Likewise, summing a parent total after unnesting can repeat that total. These are cardinality decisions, so check populated, empty and repeated-element cases before trusting a report.

Keep SQL and PL/SQL contracts distinct

A SQL result set can contain zero, one or many rows. A scalar PL/SQL SELECT INTO expects exactly one row and assigns type-compatible variables. Zero rows raises NO_DATA_FOUND; multiple rows raises TOO_MANY_ROWS. A cursor FOR loop instead processes each returned row and executes its body zero times for an empty result. Choose the interface from the application requirement.

SQL%ROWCOUNT is useful immediately after DML to inspect affected rows. It does not prove a commit, count every client result set or replace SELECT INTO exception handling. Later SQL can replace the implicit cursor status. Lesson 7 captured the count immediately after its update and used ROLLBACK to restore the practice data. Oracle's SELECT INTO reference explains the single-row contract.

When returning grouped results, name the aggregate expression and specify the grouping key. Lesson 8's category_count view exposed counter as a usable view column. A conventional view stores its query definition, rather than maintaining a separate subtotal table. ORDER BY controls displayed order. A quoted mixed-case name is legal but requires matching references; an unquoted name follows Oracle's identifier rules.

Place object types alongside JSON facilities

SELECT jt.name, jt.city
FROM JSON_TABLE(
  '{"name":"Avery Lee","address":{"city":"Boston","postal_code":"02101"}}',
  '$' COLUMNS (
    name        VARCHAR2(100) PATH '$.name',
    city        VARCHAR2(40)  PATH '$.address.city',
    postal_code VARCHAR2(20)  PATH '$.address.postal_code'
  )
) jt
WHERE jt.postal_code = '02101';

Make the design decision reviewable

Glossary terms for object tables

Object-relational

Using typed objects and related facilities within a relational database and SQL.

Attribute and method

An attribute is a typed component; a method is an operation declared for the type.

Composition and aggregation

Composition describes owned parts; aggregation describes associations whose parts may have independent identity. Both require explicit lifecycle rules.

Encapsulation

Grouping values with operations. It does not make Oracle object attributes private.

Inheritance

A subtype derives supported attributes and methods from a supertype.

Dot notation

An expression follows named components, such as c.address.city, using the relevant alias and declared structure.

Persistent and transient values

A persistent value is stored in database tables; a transient value is held temporarily during execution. Explicit DML can store a constructed working value.

Homogeneous and sparse

Homogeneous elements share one declared datatype. A sparse collection has gaps in its existing index set.

API and enumeration

An API is a defined program interface. An enumeration represents a finite set of named values; it is a general concept, not a requirement for querying object tables.

CREATE OR REPLACE

DDL syntax supported for particular schema objects. Replacement is subject to privileges, dependencies and object-specific rules; it is not a harmless way to reset every populated type.

Querying Object Tables - Quiz