Using Oracle Objects in 26ai: Benefits and Trade-offs

Oracle AI Database 26ai lets you define SQL object types that combine named attributes with optional methods. These types can provide reusable database interfaces for structured values such as addresses, employees, and engineering components. Their advantage is a particular way of organizing data and behavior, rather than a requirement to abandon relational tables or SQL.

The useful question is whether a typed object interface makes your application easier to build, query, and maintain. Ordinary tables can represent customers, calculate order totals, and model parent-child relationships. Stored functions, procedures, packages, and views can centralize operations over those tables. Oracle objects offer an additional design choice: applications can use a shared type and call operations associated with its instances.

This lesson develops that comparison with a salary method, type inheritance, and two collection examples. It also corrects several common misconceptions: object attributes are not automatically private, a reference is not merely a renamed foreign key, and VARRAY values can be queried with SQL. These are established Oracle capabilities documented for 26ai, not features invented in this release.

Reuse a type and its operations

A SQL object type is a schema-level contract. Its specification names the attributes and declares any methods; a type body implements the declared behavior. Applications with the appropriate privileges can use the same database definition. A consistent contract can reduce repeated data definitions and calculation code across several consumers.

The following illustrative script defines an employee with an annual salary method. It assumes twelve identical monthly payments and ignores bonuses, deductions, currencies, and changes during the year. That assumption belongs to the method's business meaning, not to Oracle's object model.

Run these examples in order in a disposable training schema with CREATE TYPE, CREATE TABLE, and sufficient tablespace quota. They have been reviewed against Oracle documentation but have not been executed on an Oracle instance here. DDL commits transactions. The slash terminates definitions in SQL*Plus or a compatible script mode; do not send it blindly as part of every driver-issued statement.

CREATE TYPE lesson2_employee_t AS OBJECT (
  emp_id         NUMBER,
  emp_name       VARCHAR2(100),
  monthly_salary NUMBER,
  MEMBER FUNCTION annual_salary RETURN NUMBER
);
/

CREATE TYPE BODY lesson2_employee_t AS
  MEMBER FUNCTION annual_salary RETURN NUMBER IS
  BEGIN
    RETURN SELF.monthly_salary * 12;
  END annual_salary;
END;
/

CREATE TABLE lesson2_employees OF lesson2_employee_t (
  emp_id PRIMARY KEY,
  monthly_salary NOT NULL,
  CONSTRAINT lesson2_salary_ck CHECK (monthly_salary >= 0)
);

INSERT INTO lesson2_employees
VALUES (lesson2_employee_t(101, 'Asha Patel', 5000));

SELECT e.emp_id, e.emp_name, e.annual_salary() AS annual_salary
FROM lesson2_employees e
WHERE e.emp_id = 101;

The expected annual amount is 60000 for this sample. It is an expected value inferred from the script, not a captured execution result. The table's primary key enforces employee-key uniqueness. Its salary constraints reject null and negative stored salary values. They are separate from the method that multiplies a salary by twelve.

You could perform the same arithmetic in an ordinary relational query. For example, after creating a relational employee table, this expression would calculate the same result:

-- Assumes an existing relational table with these columns.
SELECT emp_id, emp_name, monthly_salary * 12 AS annual_salary
FROM employees_rel;

The method's value is that it gives the operation a reusable name and a place in a shared type interface. A package function or view might provide an equally suitable contract for a relational design. Compare the interfaces your consumers actually need, including bulk queries and reporting, rather than treating one calculation as proof that objects are necessary.

What benefits should you expect?

Oracle's Advantages of Objects describes reuse, operations associated with data, and representations of part-whole structures. These benefits can be valuable when several applications share a stable structured value and a well-defined set of operations. A type can make that value explicit instead of repeatedly assembling unrelated scalar parameters.

Selecting an object-valued column can return its complete object value. SQL can also project individual scalar attributes. Returning one composite value may fit a client interface better, but it does not prove that every query is faster or that every referenced object is eagerly fetched. The amount of data transferred, driver mapping, query predicates, and execution plan still matter.

Consider a service that accepts an address with the same attributes in several workflows. A shared address type can make those inputs consistent. A reporting system that filters millions of addresses by postal code may prefer straightforward scalar projections. Both consumers can be legitimate; the best representation is the one supported by measured access patterns and manageable interfaces.

Reusable definitions also create dependencies. Changing a type can affect its tables, other types, views, and program units. Plan schema evolution, client compatibility, and deployment together. Reuse helps when teams manage a common contract; it can become expensive when unrelated consumers require incompatible changes to that contract.

Encapsulation and integrity are separate decisions

Four terms help organize the comparison: encapsulation^[1], composition^[2], aggregation^[3], and inheritance^[4]. They describe design ideas, not four operations that relational databases are incapable of supporting. The next sections explain what SQL object types contribute and what still requires explicit design.

Encapsulation groups attributes with relevant operations. The employee type makes annual_salary part of the employee interface. However, Oracle SQL object attributes are accessible through supported SQL and PL/SQL expressions. Declaring a member method does not create a Java-style private-field boundary or compel every writer to call that method.

For example, a caller with suitable privileges can update stored salary data through DML. If a method checks a business rule, another write path might bypass that check. Use appropriate table constraints, grants, and controlled APIs to enforce the rules that must hold for every writer. Do not advertise a validation method as a substitute for database integrity controls.

The sample method also illustrates the difference between table constraints and transient values. A constructor can create an employee value outside the constrained table. If its salary is null, the multiplication produces null. The table constraints apply when values are stored there; they do not automatically validate every object variable in every PL/SQL call.

Composition and aggregation

Composition describes a whole with parts owned as part of its meaning or lifecycle. An order's historical delivery address can be modeled as a value stored with that order. A nested address object can express that grouping clearly. Relational columns can express the same address, and a child table can represent more elaborate owned parts.

Aggregation describes an association in which a part can have an independent identity or be shared. A vehicle may refer to an independently managed supplier; many vehicles can use the same supplier. A foreign key or an object reference may suit that association. Decide ownership and sharing before choosing either mechanism.

Neither a nested object attribute nor a collection declaration automatically supplies every lifecycle rule. You still need to decide whether changing a shared record affects existing transactions, whether deletion is permitted, and whether historical values must remain unchanged. These decisions often matter more than the number of tables in a diagram.

Type inheritance and specialization

Oracle supports SQL object type hierarchies. A supertype declared NOT FINAL permits subtypes, and a subtype declared UNDER inherits its supertype's attributes and methods. A subtype can add attributes and, where permitted, override inherited methods. Inheritance concerns the type contract, not copying one row's column values into another row.

CREATE TYPE lesson2_asset_t AS OBJECT (
  asset_id   NUMBER,
  asset_name VARCHAR2(100)
) NOT FINAL;
/

CREATE TYPE lesson2_vehicle_t UNDER lesson2_asset_t (
  registration_no VARCHAR2(30)
);
/

SELECT lesson2_vehicle_t(201, 'Training van', 'TRAIN-201')
       AS vehicle_value
FROM dual;

This attribute-only example needs no method body. The vehicle constructor receives the inherited attributes followed by the added attribute. It constructs a value; it does not persist a vehicle row. A storage design must separately decide which tables or columns hold those values.

Type substitutability can allow values of subtypes in locations declared for a supertype, subject to the relevant Oracle rules and storage declarations. That flexibility requires clients to understand which concrete types they may receive. A relational design can instead represent specialization with related tables and explicit keys. Choose based on the operations, constraints, and consumer contracts you need.

References compared with foreign keys

A foreign key links relational key values and enforces the declared relationship. An Oracle REF is a typed reference to a referenceable row object. Row objects have OIDs, which are system-generated by default; primary-key-based OIDs are an explicit alternative. Creating a primary-key constraint alone does not select that alternative.

A column object, such as an address embedded in a customer row, does not have an independent row-object OID. It cannot simply become the target of a row-object reference because it has an object type. Understand storage identity before deciding that a reference should replace a foreign key.

Reference scope and referential integrity are also different concerns. A scoped reference limits the target table, while integrity rules must address target existence. References can become dangling when their targets are unavailable. Review the required constraints and deletion behavior instead of assuming that every REF protects the relationship automatically.

VARRAYs and nested tables: correct the comparison

Oracle supports schema-level VARRAY and nested table types for collection-valued data. Both can contain scalar values or objects, and both can be queried through SQL collection expressions. The important differences involve size contracts, ordering, storage, and update patterns.

SQL collection choices in Oracle 26ai

Property	VARRAY	Nested table
Size	Declared maximum; current count may be smaller.	No maximum count declared in the type; practical resource limits still apply.
Ordering	Preserves element order.	Stored element order is not preserved.
SQL queries	Can be unnested with TABLE().	Can be unnested with TABLE().
Storage	Collection stored as a value; inline or LOB storage depends on its size and configuration.	Elements reside in a separate associated storage table.
Typical access	Useful for bounded collections often handled as a whole.	Useful when element-level querying and updates are important.

A VARRAY with a limit of ten does not contain ten values immediately after definition. It can hold fewer values, including an initialized empty collection. A stored nested table's lack of a declared maximum does not mean memory and storage are unlimited. Treat size expectations as part of the application contract.

PL/SQL can access collection elements by procedural indexes under the collection's rules. Those indexes are different from B-tree indexes used to accelerate database storage access. A stored nested table should not use its old insertion positions as durable business sequence numbers. Store an explicit sequence attribute if the sequence must survive storage and retrieval.

Query both collection kinds with SQL

The next script stores two scalar collection columns in one relational table. It names the nested table's separate storage table. The VARRAY has a maximum of ten phone values; the sample populates only two.

CREATE TYPE lesson2_phone_va AS VARRAY(10) OF VARCHAR2(30);
/
CREATE TYPE lesson2_phone_nt AS TABLE OF VARCHAR2(30);
/

CREATE TABLE lesson2_contacts (
  contact_id       NUMBER PRIMARY KEY,
  preferred_phones lesson2_phone_va,
  other_phones     lesson2_phone_nt
)
NESTED TABLE other_phones STORE AS lesson2_other_phones_store;

INSERT INTO lesson2_contacts
VALUES (
  1,
  lesson2_phone_va('555-0101', '555-0102'),
  lesson2_phone_nt('555-0201', '555-0202')
);

SELECT c.contact_id, p.COLUMN_VALUE AS phone
FROM lesson2_contacts c, TABLE(c.preferred_phones) p
WHERE c.contact_id = 1
ORDER BY p.COLUMN_VALUE;

SELECT c.contact_id, p.COLUMN_VALUE AS phone
FROM lesson2_contacts c, TABLE(c.other_phones) p
WHERE c.contact_id = 1
ORDER BY p.COLUMN_VALUE;

For scalar collection elements, COLUMN_VALUE names the exposed scalar value. Each query expands one collection into rows. The first demonstrates SQL access to the VARRAY; the second uses the same pattern for the nested table. SQL access is not restricted to PL/SQL simply because the collection is a VARRAY.

The queries are intentionally separate. Unnesting both two-element collections in one ordinary join could produce four combinations for the contact. That is not a positional pairing of phone numbers. Choose a query that expresses the intended relationship and check its cardinality before adding aggregations or displaying parent-level totals.

ORDER BY p.COLUMN_VALUE sorts the result by the displayed phone value. It does not request the original VARRAY positions. Distinguish collection order from result-set ordering. Also distinguish an initialized empty constructor such as lesson2_phone_nt() from a null collection, and decide which state your application should store.

Choose the design from the workload

Objects can improve an interface when structured values and their operations are reused consistently. Ordinary relational designs remain strong choices for broad reporting, independently updated child rows, and tools that expect scalar columns. A type-oriented design should make a concrete operation or contract easier to understand, not merely resemble an application's class diagram.

Use a small prototype to evaluate the choice. Include typical reads, bulk updates, joins, and external consumers. Measure transferred data and query plans. Review constraint enforcement and schema changes as carefully as the happy-path method call. A successful introductory example proves a syntax pattern, not a production performance advantage.

• Identify whether each value is owned, shared, or historical.
• Decide which operations belong in a type interface or a package.
• Specify keys, constraints, reference integrity, and write permissions.
• Choose collection size and sequence semantics deliberately.
• Verify driver support, reporting access, and type-evolution impact.

The next lesson develops the terminology for Oracle objects. Bring forward the distinctions between a type and a value, a row object and a column object, and a collection's declaration and its stored elements. They provide the vocabulary for explaining why a particular design fits a requirement.

[1]Encapsulation: Grouping data with associated operations. Oracle SQL object attributes remain accessible; methods alone do not enforce private access or validate every write.

[2]Composition: A whole with parts owned as part of its meaning or lifecycle. Nested values and relational schemas can both represent it, with explicit integrity and lifecycle rules.

[3]Aggregation: An association with parts that can have independent identity or be shared. Keys or object references can represent the relationship; a collection alone does not establish ownership.

[4]Inheritance: A subtype derives attributes and methods from a supertype and can add or override supported features. Oracle permits subtypes under a NOT FINAL supertype.