An object type is a kind of data type.

You can use an object in the same ways that you use standard data types such as NUMBER or VARCHAR2. For example, you can specify an object type as the data type of a column in a relational table, and you can declare variables of an object type. The value is a variable or an instance of that type. An object instance is also called an object.

Figure 1-1 shows an object type, person_typ, and two instances of the object type.

Figure 1-1 Object Type and Object Instances

Description of "Figure 1-1 Object Type and Object Instances"

Object types serve as blueprints or templates that define both structure and behavior. Object types are database schema objects, subject to the same kinds of administrative control as other schema objects. Application code can retrieve and manipulate these objects. See Managing Oracle Objects.

You use the CREATE TYPE SQL statement to define object types.

Example 1-1 shows how to create an object type named person_typ. In the example, an object specification and object body are defined. For information on the CREATE TYPE SQL statement and on the CREATE TYPE BODY SQL statement, see Oracle Database PL/SQL Language Reference.

Example 1-1 Creating the person_typ Object Type

CREATE TYPE person_typ AS OBJECT (
  idno           NUMBER,
  first_name     VARCHAR2(20),
  last_name      VARCHAR2(25),
  email          VARCHAR2(25),
  phone          VARCHAR2(20),
  MAP MEMBER FUNCTION get_idno RETURN NUMBER, 
  MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ));
/

CREATE TYPE BODY person_typ AS
  MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
  BEGIN
    RETURN idno;
  END;
  MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) IS
  BEGIN
    -- use the PUT_LINE procedure of the DBMS_OUTPUT package to display details
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(idno) || ' ' || first_name || ' ' || last_name);
    DBMS_OUTPUT.PUT_LINE(email || ' '  || phone);
  END;
END;
/

Object types differ from the standard data types that are native to a relational database:

• Oracle Database does not supply predefined object types. You define the object types you want by combining built-in types with user-defined ones as shown in Example 1-1.

• Object types are composed of attributes and methods as illustrated in Figure 1-2.

  • Attributes hold the data about an object. Attributes have declared data types which can, in turn, be other object types.

  • Methods are procedures or functions that applications can use to perform operations on the attributes of the object type. Methods are optional. They define the behavior of objects of that type.

Figure 1-2 Object Attributes and Methods

Figure 1-2 shows the relationship of attributes and methods in the spec.

Description of "Figure 1-2 Object Attributes and Methods"

A variable of an object type is an instance of the type, or an object.

An object has the attributes and methods defined for its type. Because an object instance is a concrete thing, you can assign values to its attributes and call its methods.

Defining an object type does not allocate any storage. After they are defined, object types can be used in SQL statements in most of the same places you use types such as NUMBER or VARCHAR2. Storage is allocated once you create an instance of the object type.

Example 1-2 shows how to create object instances of the person_typ created in Example 1-1, and define a relational table to keep track of these instances as contacts.

-- requires existing person_typ fr. Ex 1-1
CREATE TABLE contacts (
  contact         person_typ,
  contact_date    DATE );

INSERT INTO contacts VALUES (
  person_typ (65, 'Verna', 'Mills', 'vmills@example.com', '1-650-555-0125'), 
  to_date('24 Jun 2003', 'dd Mon YYYY'));

Object methods are functions or procedures that you can declare in an object type definition to implement behavior that you want objects of that type to perform.

The general kinds of methods that can be declared in a type definition are:

• Member Methods

  Using member methods, you can provide access to the data of an object, and otherwise define operations that an application performs on the data. To perform an operation, the application calls the appropriate method on the appropriate object.

• Static Methods

  Static methods compare object instances and perform operations that do not use the data of any particular object, but, instead, are global to an object type.

• Constructor Methods

  A default constructor method is implicitly defined for every object type, unless it is overwritten with a user-defined constructor. A constructor method is called on a type to construct or create an object instance of the type.

Example 1-3 show the get_idno() method, created in Example 1-1, to display the Id number of persons in the contacts table:

-- requires Ex 1-1 and Ex 1-2 
SELECT c.contact.get_idno() FROM contacts c;

Objects can be stored in two types of tables:

• Object tables: store only objects

  In an object table, each row represents an object, which is referred to as a row object.

• Relational tables: store objects with other table data

  Objects that are stored as columns of a relational table, or are attributes of other objects, are called column objects. Example 1-2 shows the contacts table which stores an instance of the person_typ object.

Objects that have meaning outside of the relational database in which they are contained, or objects that are shared among more than one relational database object, should be made referenceable as row objects. That is, such objects should be stored as a row object in an object table instead of in a column of a relational table.

Object identifiers (OIDs) uniquely identify row objects in object tables.

You cannot directly access object identifiers, but you can make references (REFs) to the object identifiers and directly access the REFs, as discussed in "References to Row Objects".

There are two types of object identifiers.

• System-Generated Object Identifiers (default)

  Oracle automatically creates system-generated object identifiers for row objects in object tables unless you choose the primary-key based option.

• Primary-Key Based Object Identifiers

  You have the option to create primary-key based OIDs when you create the table using the CREATE TABLE statement.

A REF is a logical pointer or reference to a row object that you can construct from an object identifier (OID).

You can use the REF to obtain, examine, or update the object. You can change a REF so that it points to a different object of the same object type hierarchy or assign it a null value.

REFs are Oracle Database built-in data types. REFs and collections of REFs model associations among objects, particularly many-to-one relationships, thus reducing the need for foreign keys. REFs provide an easy mechanism for navigating between objects.

Example 1-6 illustrates a simple use of a REF.

CREATE TYPE emp_person_typ AS OBJECT (
  name     VARCHAR2(30),
  manager  REF emp_person_typ );
/
CREATE TABLE emp_person_obj_table OF emp_person_typ;

INSERT INTO emp_person_obj_table VALUES (
   emp_person_typ ('John Smith', NULL));
   
INSERT INTO emp_person_obj_table
  SELECT emp_person_typ ('Bob Jones', REF(e))
    FROM emp_person_obj_table e
    WHERE e.name = 'John Smith';

COLUMN name FORMAT A10
COLUMN manager FORMAT A50
select * from emp_person_obj_table e;

NAME       MANAGER
---------- --------------------------------------------------
John Smith
Bob Jones  0000220208424E801067C2EABBE040578CE70A0707424E8010
           67C1EABBE040578CE70A0707

Two REF variables can be compared if, and only if, the targets that they reference are both of the same declared type, or one is a subtype of the other.

REF variables can only be compared for equality.

For modeling multi-valued attributes and many-to-many relationships, Oracle Database supports these two collection data types:

• Varrays

• Nested Tables

You can use collection types anywhere other data types are used. You can have object attributes of a collection type in addition to columns of a collection type. For example, a purchase order object type might contain a nested table attribute that holds the collection of line items for the purchase order.

To define a collection type, use the CREATE TYPE . . . AS TABLE OF statement.

Example 1-12 shows CREATE TYPE statements that define a collection and an object type.

-- requires Ex. 1-1
CREATE TYPE people_typ AS TABLE OF person_typ;
/

CREATE TYPE dept_persons_typ AS OBJECT (
  dept_no    CHAR(5),
  dept_name  CHAR(20),
  dept_mgr   person_typ,
  dept_emps  people_typ);
/

An object view is a way to access relational data using object-relational features.

An object view lets you develop object-oriented applications without changing the underlying relational schema.

You can access objects that belong to an object view in the same way that you access row objects in an object table. Oracle Database also supports materialized view objects of user-defined types from data stored in relational schemas and tables.

Object views let you exploit the polymorphism that a type hierarchy makes possible. A polymorphic expression takes a value of the expression's declared type or any of that type's subtypes. If you construct a hierarchy of object views that mirrors some or all of the structure of a type hierarchy, you can query any view in the hierarchy to access data at just the level of specialization you are interested in. If you query an object view that has subviews, you can get back polymorphic data—rows for both the type of the view and for its subtypes.