As an application goes through its life cycle, the question often arises whether to change an existing object type or to create a specialized subtype to meet new requirements. The answer depends on the nature of the new requirements and their context in the overall application semantics. Here are two examples:

CREATE TYPE address AS OBJECT (
  Street  VARCHAR2(80),
  State   VARCHAR2(20),
  ZIP     VARCHAR2(10));
/

ANYDATA is an Oracle-supplied type that can hold instances of any Oracle data type, whether built-in or user-defined. ANYDATA is a self-describing type and supports a reflection-like API that you can use to determine the shape of an instance.

While both inheritance, through the substitutability feature, and ANYDATA provide the polymorphic ability to store any of a set of possible instances in a placeholder, the two models give the capability two very different forms.

In the inheritance model, the polymorphic set of possible instances must form part of a single type hierarchy. A variable can potentially hold instances only of its defined type or of its subtypes. You can access attributes of the supertype and call methods defined in the supertype (and potentially overridden by the subtype). You can also test the specific type of an instance using the IS OF and the TREAT operators.

ANYDATA variables, however, can store heterogeneous instances. You cannot access attributes or call methods of the actual instance stored in an ANYDATA variable (unless you extract out the instance). You use the ANYDATA methods to discover and extract the type of the instance. ANYDATA is a very useful mechanism for parameter passing when the function/procedure does not care about the specific type of the parameter(s).

Inheritance provides better modeling, strong typing, specialization, and so on. Use ANYDATA when you simply want to be able to hold one of any number of possible instances that do not necessarily have anything in common.

Applying an Object Model to Relational Data describes how to build up a view hierarchy from a set of object views each of which contains objects of a single type. Such a view hierarchy enables queries on a view within the hierarchy to see a polymorphic set of objects contained by the queried view or its subviews.

As an alternative way to support such polymorphic queries, you can define an object view based on a query that returns a polymorphic set of objects. This approach is especially useful when you want to define a view over a set of tables or views that already exists.

For example, an object view of Person_t can be defined over a query that returns Person_t instances, including Employee_t instances. The following statement creates a view based on queries that select persons from a persons table and employees from an employees table.

CREATE VIEW Persons_view OF Person_t AS
SELECT Person_t(...) FROM persons
UNION ALL
SELECT TREAT(Employee_t(...) AS Person_t) FROM employees;

An INSTEAD OF trigger defined for this view can use the VALUE function to access the current object and to take appropriate action based on the object's most specific type.

Polymorphic views and object view hierarchies have these important differences:

• Addressability: In a view hierarchy, each subview can be referenced independently in queries and DML statements. Thus, every set of objects of a particular type has a logical name. However, a polymorphic view is a single view, so you must use predicates to obtain the set of objects of a particular type.

• Evolution: If a new subtype is added, a subview can be added to a view hierarchy without changing existing view definitions. With a polymorphic view, the single view definition must be modified by adding another UNION branch.

• DML Statements: In a view hierarchy, each subview can be either inherently updatable or can have its own INSTEAD OF trigger. With a polymorphic view, only one INSTEAD OF trigger can be defined for a given operation on the view.

This section discusses the SQLJ object type.

Topics:

• The Intended Use of SQLJ Object Types

• Actions Performed When Creating a SQLJ Object Type

• Uses of SQLJ Object Types

• Uses of Custom Object Types

• Differences Between SQLJ and Custom Object Types Through JDBC

You should know these miscellaneous tips for designing with Oracle objects.