Contents

Title a nd Copyright Information

Send Us Your Comment s

Preface

Audience

Organization

R elated Documentation

Conventions

Documentation Accessibility

What's New in Object-Relati onal Features?

Oracle 10g Release 1 (10.1) New in Object-Relational F eatures

Oracle9i Release 2 (9.2) New in Object-Relational Features

Oracle9i Release 1 (9.0.1) New in Object-Relational Features

1 Introduction to Oracle Objects< /h3>

About Oracle Objects

Advantages of Objects

Key Features of the Object-Relational Model

Core Database Key Features

Object Types

Objects

Object Methods

T ype Inheritance

Type Evolution

Object Table s

Row Objects and Column Objects

Object Vie ws

References

Collections

Language Binding Features

2 Basic Components of Oracle Objects

SQL Object Types and References

Null Objects and Attribut es

Character Length Semantics

Constraints f or Object Tables

Indexes for Object Tables

Triggers for Object Tables

Rules for REF Columns and Attributes

Name Resolution

When Table Aliases Are Required

Restriction on Using User-Defined Types with a Remote Database

Object Methods

Member Methods

< dd>Methods for Comparing Objects

Map Methods

Order Methods

Guidelines for Comparison Methods

Comparison Methods in Type Hierarchies

Static Methods

Constructor Methods

External Implemented Methods

Inheritance in SQL Object Types

Types and Subtypes

FINAL and NOT FINA L Types and Methods

Creating Subtypes

NOT INSTANTIABLE Types and Methods

Inheriting, Overloading, and Overriding Methods

Overloading Methods

Redefining Methods

Restrictions on Overriding Methods

Dynamic Method Dispatch

Substituting Types in a Type Hierarchy

Column and Row Substitutability

Using OBJECT_VALUE and OBJECT_ID with Substitutable Rows

Subtypes Having Supertype Attributes

REF Columns and Attributes

Collection Elements

Creating Subtypes After Creating Substitutable Columns

Dropping Subtypes After Creating Substitutable Columns

Turning Off S ubstitutability in a New Table

Constraining Substitutability

Modifying Substitutability

Restrictions on Modifying Substitutabili ty

Assignments Across Types

Obj ects and REFs to Objects

Collection Assignments

Comparisons of Objects, REF Variables, and Collections

Compa ring Object Instances

Comparing REF Variables

Functions and Operators Useful with Objects

CAST

CURSOR

DEREF

IS OF type

REF

SY S_TYPEID

TABLE()

TREAT

VALUE

3 Support for Collection Datatypes

Creating Collection Datatypes

Creating an Instance of a VARRAY or Nested Table

Constructor Methods for Collections

Varrays

Nested Tables

Specifying a Tablespace When S toring a Nested Table

Varray Storage

Incr easing the Size and Precision of VARRAYs and Nested Tables

Increasing VARRAY Limit Size

Creating a Varray Containing LOB References

Multilevel Collection Types

Nested Table Storage Tables for Multilevel Co llection Types

Assignment and Comparison of Multilevel Collections

Constructors for Multilevel Collections

O perations on Collection Datatypes

Querying Collections

Nesting Results of Collection Queries

Unnesting Resul ts of Collection Queries

Unnesting Queries Containing Table Expression Subqueries

Unnesting Queries with Multilevel Collections

Performing DML Operations on Collections

Performing DML on Multilev el Collections

Comparisons of Collections

Equal and Not Equal Comparisons

In Comparisons

Subset of Multiset Comparison

Member of a Nested Table Compa rison

Empty Comparison

Set Comparison

Multisets Operations

CAR DINALITY

COLLECT

MULTISET EXCEPT

MULTISET INTERSECTION

MULTISET UNION

POWERMULTISET

POWERMULTISET_BY_CARDINALITY

< dd>SET

4 Object Support in Oracle Programming Environments

SQL

PL/SQL

Oracl e Call Interface (OCI)

Associative Access in OCI Programs

Navigational Access in OCI Programs

Object Cache

Building an OCI Program That Manipulates Objects

Def ining User-Defined Constructors in C

Pro*C/C++

Associative Access in Pro*C/C++

Navigational Access in Pro*C/ C++

Converting Between Oracle Types and C Types

Oracle Type Translator (OTT)

Oracle C++ Call Interface (OCCI)

OCCI Associative Relational and Object Interfaces

The OCCI Navigational Interface

Oracle Objects For OLE (OO4O)< /dd>

Representing Objects in Visual Basic (OraObject)

Representing REFs in Visual Basic (OraRef)

Representing VARRAYs and Neste d Tables in Visual Basic (OraCollection)

Java: JDBC, Oracle SQLJ, JPublisher, and SQLJ Object Types

JDBC Access to Oracle Object Data

SQLJ Access to Oracle Object Data

Choosing a Data Mapping Str ategy

Using JPublisher to Create Java Classes for JDBC and SQLJ Programs

What JPublisher Produces for a User-Defined Object Type

Java Object Storage

Representing SQLJ Types to the Serve r

Creating SQLJ Object Types

Additional N otes About Mapping

Evolving SQLJ Types

Co nstraints

Querying SQLJ Objects

Inserting Java Objects

Updating SQLJ Objects

Defining User-Defined Constructors in Java

XML

5 Applying an Object Model to Relationa l Data

Why Use Object Views

D efining Object Views

Using Object Views in Applications

Nesting Objects in Object Views

Identifying Null Objects in Object Views

Using Nested Tables and Varrays in Object Views

Single-Level Collections in Object Views

Multilevel Collections in Ob ject Views

Specifying Object Identifiers for Object Views

Creating References to View Objects

Modelling Inverse Relati onships with Object Views

Updating Object Views

Updating Nested Table Columns in Views

Using INSTEAD OF Triggers to Co ntrol Mutating and Validation

Applying the Object Model to Remote Tables< /dd>

Defining Complex Relationships in Object Views

Tables and Types to Demonstrate Circular View References

Creating Object Vi ews with Circular References

Object View Hierarchies

< a href="adobjvew.htm#sthref543">Creating an Object View Hierarchy

The Flat Mo del

The Horizontal Model

The Vertical Mod el

Querying a View in a Hierarchy

Privileges for Operations on View Hierarchies

6 Managing Oracle Objects

Pri vileges on Object Types and Their Methods

System Privileges for Object Types< /a>

Schema Object Privileges

Using Types in N ew Types or Tables

Example: Privileges on Object Types

Privileges on Type Access and Object Access

Dependencies and In complete Types

Completing Incomplete Types

Manually Recompiling a Type

Type Dependencies of Substitutable Tables and C olumns

The FORCE Option

Synonyms for User-Defined Types

Creating a Type Synonym

Using a Type Synonym

Describing Schema Objects That Use Synonyms

Dependents of Type Synonyms

Restriction on Replacing a Type Synonym

Dropping Type Synonyms

Renaming Type Synonyms

Public Type Synonyms and Loca l Schema Objects

Performance Tuning

Tools Providing Support for Objects

Utilities Providing Support for Obje cts

7 Advance d Topics for Oracle Objects

Storage of Objects

Leaf-Level Attributes

How Row Objects Are Split Across Colu mns

Hidden Columns for Tables with Column Objects

Hidden Columns for Substitutable Columns and Tables

REFs

Internal Layout of Nested Tables

Internal Layout of VARRAYs

Creating Indexes on Typeids or Attributes

Indexing a Type Discriminant Column

Indexing Subtype Attributes of a Substitutable Column

Type Evolution

Changes Involved When a Type Is Altered

Steps to Change a Type

Validating a Type

If a Type Change Validation Fai ls

ALTER TYPE Statement for Type Evolution

ALTER TABLE Statement for Type Evolution

The Attribute-Value Constructor

Constructors and Type Evolution

Advantages of User-De fined Constructors

Defining and Implementing User-Defined Constructors

Overloading and Hiding Constructors

Calling User-Defined Constructors

Constructors for SQLJ Object Types

Transient and Generic Types

User-Defined Aggregate Functions

Partitioning Tables That Contain Oracle Objects

How Locators Improve the Performance of Nested Tables

8 Design Considerations for Oracle Objects

General Storage Considerations for Objects

Storin g Objects as Columns or Rows

Column Object Storage

Row Object Storage in Object Tables

Storage Consideration s for Object Identifiers (OIDs)

Primary-Key Based OIDs

Performance of Object Comparisons

Design Co nsiderations for REFs

Storage Size of REFs

Integrity Constraints for REF Columns

Performance and Storage Consideration s for Scoped REFs

Indexing Scoped REFs

Speeding up Object Access Using the WITH ROWID Option

Desig n Considerations for Collections

Viewing Object Data in Relational Form with Unnesting Queries

Using Procedures and Functions in Unnesting Queries

Storage Considerations for Varrays

Propagating VARRAY Size Change

Performance of Varrays Versus Nested Ta bles

Design Considerations for Nested Tables

Nested Table Storage

Nested Table Indexes

Nested Table Locators

Optimizing Set Membership Queries

Design Considerations for Multilevel Collections

Design Considerations for Methods

Choosing a Language for Method Functions

Static Methods

Using SELF IN OU T NOCOPY with Member Procedures

Function-Based Indexes on the Return Values of Type Met hods

Writing Reusable Code Using Invoker Rights

Replicating Object Tables and Columns

Replicating Columns of Object, Collection, or REF Type

Replicating Object Tables

Constraints on Objects

Considerations Related to Type Evolu tion

Pushing a Type Change Out to Clients

Changing Default Constructors

Altering the FINAL Property of a Type

Parallel Queries with Oracle Objects

De sign Consideration Tips and Techniques

Deciding Whether to Evolve a Type or C reate a Subtype Instead

How ANYDATA Differs from User-Defined Types

Polymorphic Views: An Alternative to an Object View Hierarchy

The SQLJ Object Type

The Intended Use of SQLJ Object Types

Actions Performed When Creating a SQLJ Object Type

Uses o f SQLJ Object Types

Uses of Custom Object Types

Differences Between SQLJ and Custom Object Types Through JDBC

Miscella neous Tips

Column Substitutability and the Number of Attributes in a Hierarch y

Circular Dependencies Among Types

9 A Sample Application Using Object-Relationa l Features

Introduction to the Sample Application

Implementing the Schema on the Relational Model

Entities and Relationships

Creating Tables Under the Relational Model

Customer_reltab

PurchaseOrder_reltab

LineItems_reltab

Stock_reltab

Inserting Values Under the Relational Model

Querying Data Und er the Relational Model

Updating Data Under the Relational Model

Deleting Data Under the Relational Model

Implementing the Schema on the Object-Relational Model

Defining Types

Method Definitions

The getPONo Method

The sumLineItems Method

The compareCustOrders Method< /a>

Creating Object Tables

The Object Table Customer_objtab

Object Datatypes as a Template for Object Tables

Object Identifiers and References

Object Tables with Embedded Objects

The Object Table Stock_objtab

The Object Table PurchaseOrder_objtab

Inserting Val ues

Querying

Deleting

Evolving User-Defined Types

Adding an Attribute to the Customer Type

Working with Multilevel Collections

Inserting into Nested Tables

Inserti ng a New Purchase Order with Line Items

Querying Multilevel Nested Tables

Type Inheritance and Substitutable Columns

Creating a Subtype

Inserting Subtypes

Querying Substitutable Columns

Index