Query Nested Oracle 26ai Objects with SQL and Dot Notation

Define the embedded and outer object types

Use a practice schema where the following names are unused. You need privileges to create SQL types, tables and views, with appropriate quota for the tables. Lesson-specific names keep these objects separate from earlier examples. Create the address type before the person type because the latter depends on it.

CREATE TYPE lesson8_address_t AS OBJECT (
  street  VARCHAR2(50 CHAR),
  city    VARCHAR2(30 CHAR),
  zip     VARCHAR2(10 CHAR)
);
/
CREATE TYPE lesson8_person_t AS OBJECT (
  person_id  NUMBER,
  name       VARCHAR2(50 CHAR),
  addr       lesson8_address_t
);
/
CREATE TABLE lesson8_persons OF lesson8_person_t (
  person_id PRIMARY KEY
);

INSERT INTO lesson8_persons VALUES (
  lesson8_person_t(1, 'Alice Smith',
    lesson8_address_t('123 Main St', 'Boston', '02101'))
);
INSERT INTO lesson8_persons VALUES (
  lesson8_person_t(2, 'Bob Lee',
    lesson8_address_t('25 Oak St', 'Seattle', '98101'))
);
INSERT INTO lesson8_persons VALUES (
  lesson8_person_t(3, 'Carla Diaz', NULL)
);
INSERT INTO lesson8_persons VALUES (
  lesson8_person_t(4, 'Dev Patel',
    lesson8_address_t(NULL, NULL, NULL))
);
COMMIT;

The slash submits each type definition in SQL*Plus-style script execution. It is a client command, not part of the SQL statement sent through a driver. The constructors supply attribute values in declaration order. Creating a type alone does not insert people or create persistent rows; the table and INSERT statements perform those separate operations.

The zip attribute is character data so a leading zero survives. It is not a number to be added or multiplied. CHAR length qualifiers make the intended character limits explicit, but do not validate postal-code formats. Similarly, the primary key enforces person_id uniqueness in this table; it does not make every other attribute mandatory.

Select and filter through the address path

SELECT p.person_id, p.name,
       p.addr.city AS city, p.addr.zip AS postal_code
FROM lesson8_persons p
WHERE p.person_id = 1;

The expected row contains identifier 1, Alice Smith, Boston and postal code 02101. The output aliases city and postal_code name the result columns. They do not rename attributes in lesson8_address_t. An application can request a simple tabular result even though its source is an object table.

SELECT p.person_id, p.name, p.addr.zip AS postal_code
FROM lesson8_persons p
WHERE p.addr.city = 'Boston'
ORDER BY p.person_id;

Distinguish embedded values from other object features

Here addr contains an address value within a person. It is neither a REF to a separately stored address row nor a collection of addresses. A nested-table collection needs collection-query syntax such as TABLE, which was introduced in Lesson 6. Do not add TABLE merely because an object contains another object.

A relational table can instead contain an object-valued column. The same attribute-navigation rule applies, but the path includes that column. The following optional example creates one such table and copies the first person's value into it:

CREATE TABLE lesson8_contacts (
  contact_id  NUMBER PRIMARY KEY,
  person      lesson8_person_t
);

INSERT INTO lesson8_contacts
SELECT p.person_id, VALUE(p)
FROM lesson8_persons p
WHERE p.person_id = 1;
COMMIT;

SELECT c.person.name AS person_name,
       c.person.addr.city AS city
FROM lesson8_contacts c
WHERE c.contact_id = 1;

The path c.person.addr.city includes the object-valued column person, then its addr attribute, then city. VALUE(p) retrieves the complete row object for the copy. The copied value is not a live reference that automatically tracks later updates to lesson8_persons. Decide whether a real application requires a snapshot or a separately maintained relationship.

Handle a missing address separately from a missing city

SELECT p.person_id, p.name
FROM lesson8_persons p
WHERE p.addr IS NULL
ORDER BY p.person_id;

SELECT p.person_id, p.name
FROM lesson8_persons p
WHERE p.addr IS NOT NULL
  AND p.addr.city IS NULL
ORDER BY p.person_id;

The first query illustrates Carla, whose address object is null. The second illustrates Dev, whose address object exists but whose city is null. The constructor lesson8_address_t(NULL, NULL, NULL) creates an object value with null attributes; it is different from assigning NULL to addr itself.

A comparison to a city literal does not classify all missing addresses as another city. SQL comparisons involving null yield unknown rather than true, and WHERE retains true conditions. Write an explicit IS NULL test when missing information is the reporting criterion. Test both address states because selecting only city can make them look alike to the client.

Group related scalar values into a report

SELECT p.addr.city AS city, COUNT(*) AS person_count
FROM lesson8_persons p
GROUP BY p.addr.city
ORDER BY city NULLS LAST;

Create a category-count view with reproducible data

The legacy category example demonstrates the same grouped-result idea with ordinary relational data. The following compact practice setup creates 31 bookshelf rows in six categories. The boundaries of the CASE expression reproduce the displayed counts; each row represents one book entry.

CREATE TABLE lesson8_bookshelf (
  book_id       NUMBER PRIMARY KEY,
  categoryname  VARCHAR2(20)
);

INSERT INTO lesson8_bookshelf (book_id, categoryname)
SELECT LEVEL,
       CASE
         WHEN LEVEL <= 6  THEN 'ADULTFIC'
         WHEN LEVEL <= 16 THEN 'ADULTNF'
         WHEN LEVEL <= 22 THEN 'ADULTREF'
         WHEN LEVEL <= 27 THEN 'CHILDRENFIC'
         WHEN LEVEL <= 28 THEN 'CHILDRENNF'
         ELSE 'CHILDRENPIC'
       END
FROM dual
CONNECT BY LEVEL <= 31;
COMMIT;

CREATE VIEW category_count AS
SELECT b.categoryname, COUNT(*) AS counter
FROM lesson8_bookshelf b
GROUP BY b.categoryname;

A conventional view stores a query definition. It does not create a separate subtotal table containing these six results. Querying it evaluates its definition under Oracle's query and read-consistency rules. A materialized view is a different database feature with its own storage and refresh requirements.

DESC category_count

Name                 Null?    Type
-------------------- -------- ------------
CATEGORYNAME                  VARCHAR2(20)
COUNTER                       NUMBER

SELECT categoryname, counter
FROM category_count
ORDER BY categoryname;

CATEGORYNAME         COUNTER
-------------------- -------
ADULTFIC                   6
ADULTNF                   10
ADULTREF                   6
CHILDRENFIC                5
CHILDRENNF                 1
CHILDRENPIC                3

The numbers total 31, matching the inserted rows. They are counts of book entries, not monthly sums or proof of distinct titles. If an application stores several copies of one title, the row count follows that representation. Name the measure so users understand what is being counted.

Name expressions and expose stable view columns

CREATE VIEW lesson8_category_totals (categoryname, counter) AS
SELECT b.categoryname, COUNT(*)
FROM lesson8_bookshelf b
GROUP BY b.categoryname;

SELECT cc.categoryname, cc.counter
FROM category_count cc
WHERE cc.counter >= 6
ORDER BY cc.counter DESC, cc.categoryname;

Use quoted identifiers deliberately

CREATE VIEW lesson8_quoted_counts AS
SELECT b.categoryname AS "CategoryName",
       COUNT(*) AS "Counter"
FROM lesson8_bookshelf b
GROUP BY b.categoryname;

SELECT q."CategoryName", q."Counter"
FROM lesson8_quoted_counts q
ORDER BY q."CategoryName";

Build the query around the declared structure

Receive one nested attribute with SELECT INTO

SET SERVEROUTPUT ON
DECLARE
  l_city VARCHAR2(30 CHAR);
BEGIN
  SELECT p.addr.city INTO l_city
  FROM lesson8_persons p
  WHERE p.person_id = 1;

  DBMS_OUTPUT.PUT_LINE('City: ' || l_city);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No matching person.');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('The lookup returned multiple people.');
END;
/

DECLARE
  l_flag BOOLEAN;
BEGIN
  SELECT TRUE INTO l_flag FROM dual;
  IF l_flag THEN
    DBMS_OUTPUT.PUT_LINE('SQL Boolean retrieved.');
  END IF;
END;
/

Check the result before extending the query

Work through the practice data in stages. First project the four person identifiers and names without a predicate. Then add the address city to the projection and inspect which values are missing. Apply the Boston filter only after you understand the unfiltered result. This separates an incorrect attribute path from a valid query whose predicate excludes the rows you expected.

For the grouped view, verify the six category counts and their total before adding an outer filter. The threshold query deliberately returns only three categories, so its total cannot be compared directly with all 31 bookshelf rows. If a later report joins this view to another table, check whether the join repeats category rows before summing counter. A repeated subtotal is a query-shape problem, not evidence that the view changed its underlying count.

Also distinguish statement success from application correctness. A query can parse, execute and return rows while selecting the wrong city or using an unintended grouping key. Conversely, a correct filter may legitimately return no rows. Use the known practice cases to check populated addresses, missing addresses, null city values, one-person lookups and absent-person lookups. These checks make the expected behavior explicit without relying on a screenshot's formatting or on row order that the SQL never requested.

The next page concludes the module. Before continuing, trace p.addr.city from the alias to the embedded scalar, distinguish grouped counts from stored totals, and explain why view-column names and SELECT INTO cardinality affect the result your application receives.

Oracle 26ai references: object attributes and references, CREATE VIEW, identifier rules, SQL BOOLEAN, and SELECT INTO.