Query Oracle 26ai Object Tables and Object Columns

A SQL object type gives related values a reusable structure. An address type, for example, can group a street, city and state code. Once that type exists, an application can store address values and query their attributes with SQL. The important first question is where the object value is stored: inside a column of a relational table, or as the entire row of an object table.

These are two storage arrangements for objects, rather than two kinds of object table. A column object is an object value in a column. A row object is an object instance stored as a row in an object table, created with CREATE TABLE ... OF .... Calling the first arrangement a "column object table" obscures the distinction. An ordinary table can contain scalar columns and several object-valued columns without becoming an object table.

Both arrangements support familiar relational operations: select expressions, filter rows, join tables and order results. What changes is the path to an attribute and whether the query asks for scalar values, a complete object, or an object reference. This lesson uses the same address type in both arrangements so that these choices are visible.

The examples are an instructional script for an Oracle 26ai practice schema. They were reviewed against Oracle documentation; the displayed expectations are illustrations, not results from an Oracle session. Use unused names in a disposable schema. The lesson4_ prefix separates these examples from earlier lessons. Creating the types and tables requires the appropriate privileges and storage quota. Run each setup block once, in the order shown.

First define the shape of an address. The type declares attributes but creates neither a customer row nor an address row. Its default attribute-value constructor accepts values in the declared attribute order. Here that order is street, city, state code.

CREATE TYPE lesson4_address_t AS OBJECT (
  street      VARCHAR2(100 CHAR),
  city        VARCHAR2(60 CHAR),
  state_code  VARCHAR2(2 CHAR)
);
/

CREATE TABLE lesson4_customers (
  customer_id    NUMBER PRIMARY KEY,
  customer_name  VARCHAR2(80 CHAR),
  address_obj    lesson4_address_t
);

INSERT INTO lesson4_customers VALUES (
  101, 'Avery Chen',
  lesson4_address_t('10 Oak Street', 'Sacramento', 'CA')
);
INSERT INTO lesson4_customers VALUES (
  102, 'Morgan Diaz',
  lesson4_address_t('20 Pine Avenue', 'Portland', 'OR')
);
COMMIT;

Each customer row has a numeric identifier, a character name and one address object. The address is part of the customer row. A future order table could also declare billing and shipping columns of lesson4_address_t. Reusing the type would give those values a common structure; it would not make them references to one shared address instance.

The slash on a line by itself submits the type definition in script-oriented tools such as SQL*Plus. It is a client command, not part of SQL sent through a database driver. When using another client, follow that client's rules for submitting a complete CREATE TYPE statement. The SQL statements that follow end with semicolons in this script.

Project and filter individual attributes

Use dot notation^[1] to navigate from the table alias to the object column and then to an attribute. In the following query, c identifies the customer table, address_obj identifies the object column, and city identifies the value to return.

SELECT c.customer_id,
       c.customer_name,
       c.address_obj.street AS street,
       c.address_obj.city AS city
FROM lesson4_customers c
WHERE c.address_obj.state_code = 'CA'
ORDER BY c.customer_id;

With the supplied inserts, the illustrative result contains customer 101, Avery Chen, 10 Oak Street, Sacramento. Customer 102 does not satisfy the state predicate. The four select expressions produce four result columns; the query does not return an address object as a single value.

The alias matters: Oracle requires a table alias for dot-notational references to object attributes or subprograms. Write c.address_obj.city, rather than the unqualified address_obj.city. Substituting the table name for c does not satisfy this rule. Consistent aliases also make joins easier to read and reduce name-resolution surprises when schemas change.

Return the complete object column

If the consumer needs an address value rather than separate attributes, select the object column itself:

SELECT c.customer_id,
       c.address_obj AS address_value
FROM lesson4_customers c
ORDER BY c.customer_id;

This result has two columns: the customer identifier and a value of type lesson4_address_t. A client might display the second column as constructor-like text, expandable attributes or a mapped object. That presentation does not change the SQL datatype. For a report, scalar projections are often easier to format; for an application that understands the type, returning the complete value can be appropriate.

SELECT * from this relational table returns its three declared columns, including the object-valued column. It does not automatically flatten every address attribute into independent result columns. Choose an explicit select list when the result contract matters, especially when a later table change could add a column.

An object table provides row-object identity through an object identifier, or OID. By default, Oracle generates this identifier. An object table can alternatively use a primary-key-based OID when explicitly created with OBJECT IDENTIFIER IS PRIMARY KEY. Merely declaring a primary key does not select that alternative. The address table above uses the default strategy.

A primary key enforces uniqueness and non-null key values for table data. An OID supports object identity. They are different concepts even when a table is configured to derive its OIDs from its primary key. An embedded column object, such as a customer's address, has no independent OID. Its owning relational row can still have an ordinary primary key.

Return a reference with REF

SELECT REF(a) AS address_reference
FROM lesson4_addresses_obj a
WHERE a.city = 'Sacramento';

REF(a) returns a typed reference to a row object. It does not return the address's street and city, and it does not mean a physical memory address. A client may display a reference in an unfamiliar encoded form. Applications should treat it as a reference value, rather than parse its display as a business identifier.

Store a scoped reference and retrieve its value

The following optional continuation stores a reference in a relational table. Run it after creating and populating the address object table. The existing sample has exactly one Sacramento row; a different dataset could make the insert select several rows and violate the request primary key.

CREATE TABLE lesson4_delivery_requests (
  request_id   NUMBER PRIMARY KEY,
  address_ref  REF lesson4_address_t
    SCOPE IS lesson4_addresses_obj
);

INSERT INTO lesson4_delivery_requests (request_id, address_ref)
SELECT 501, REF(a)
FROM lesson4_addresses_obj a
WHERE a.city = 'Sacramento';
COMMIT;

SELECT d.request_id,
       DEREF(d.address_ref) AS delivery_address
FROM lesson4_delivery_requests d
WHERE d.request_id = 501;

DEREF retrieves the object value designated by a reference. This query therefore returns the request identifier and the referenced address value. Changes to the referenced row can be observed when it is read again; the stored reference is different from a separately constructed copy of the address.

SCOPE restricts the target object table, but does not by itself guarantee that a target row still exists. Deleting a referenced address can leave a dangling reference. Check for that condition with IS DANGLING. If target-row existence must be enforced, investigate the supported referential constraint rather than treating a scope declaration as a foreign key.

SELECT d.request_id
FROM lesson4_delivery_requests d
WHERE d.address_ref IS DANGLING;

The illustrative result is empty immediately after the preceding insert, while its target row exists. A null reference is also distinct from a dangling reference. This lesson does not delete sample rows to manufacture a dangling value; the predicate shows how an application can detect the condition.

[1] Dot notation: A dot separates an object's attribute path. In c.address_obj.city, c is the table alias, address_obj is the object column and city is its attribute.