Mastering Oracle SQL, 2nd Edition

Chapter 12. Objects and Collections

Beginning with Version 8.0, Oracle has been adding object-oriented features to what had been a purely relational database server. Object types and collections were introduced in Oracle8, and both have been sufficiently refined in Oracle8i, Oracle9i Database, and Oracle Database 10g so that they may now be considered fully-functional.^[1] Oracle now considers its database engine to be object-relational, in that a database may mix relational constructs such as tables and constraints with object-oriented constructs such as object types, collections, and references.

Object Types

An object type is a user-defined data type that combines data and related methods to model complex entities. In this regard, they are similar to class definitions in an object-oriented language such as C++ or Java. However, unlike Java and C++, Oracle object types have a built-in persistence mechanism, since a table can be defined to store an object type in the database. Thus, Oracle object types can be directly manipulated via SQL.

The best way to define the syntax and features of an object type is with an example. The following DDL statement creates an object type used to model an equity security such as a common stock:

CREATE TYPE equity AS OBJECT (
  issuer_id NUMBER,
  ticker VARCHAR2(6),
  outstanding_shares NUMBER,
  last_close_price NUMBER(9,2),
MEMBER PROCEDURE 
  apply_split(split_ratio in VARCHAR2)
)
NOT FINAL;

The equity object type has four attributes and a single member procedure. ...