Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

Documentation Accessibility

Related Documents

What's New in Object-Relational Features?

Oracle Database 10g Release 1 (10.1) New Features in Object-Relational Features

1 Introduction to Oracle Objects

About Oracle Objects

Advantages of Objects

Key Features of the Object-Relational Model

Core Database Key Features

Type Inheritance

Row Objects and Column Objects

Language Binding Features

2 Basic Components of Oracle Objects

SQL Object Types and References

Null Objects and Attributes

Character Length Semantics

Constraints for 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

Methods for Comparing Objects

Guidelines for Comparison Methods

Comparison Methods in Type Hierarchies

Constructor Methods

External Implemented Methods

Inheritance in SQL Object Types

Types and Subtypes

FINAL and NOT FINAL Types and Methods

Creating Subtypes With Overriding Methods

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 Substitutability in a New Table

Constraining Substitutability

Modifying Substitutability

Restrictions on Modifying Substitutability

Assignments Across Types

Objects and REFs to Objects

Collection Assignments

Comparisons of Objects, REF Variables, and Collections

Comparing Object Instances

Comparing REF Variables

Functions and Operators Useful with Objects

3 Support for Collection Datatypes

Creating Collection Datatypes

Creating an Instance of a VARRAY or Nested Table

Constructor Methods for Collections

Specifying a Tablespace When Storing a Nested Table

Increasing 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 Collection Types

Assignment and Comparison of Multilevel Collections

Constructors for Multilevel Collections

Operations on Collection Datatypes

Querying Collections

Nesting Results of Collection Queries

Unnesting Results of Collection Queries

Unnesting Queries Containing Table Expression Subqueries

Unnesting Queries with Multilevel Collections

Performing DML Operations on Collections

Performing DML on Multilevel Collections

Comparisons of Collections

Equal and Not Equal Comparisons

Subset of Multiset Comparison

Member of a Nested Table Comparison

Empty Comparison

Multisets Operations

MULTISET EXCEPT

MULTISET INTERSECTION

POWERMULTISET_BY_CARDINALITY

4 Object Support in Oracle Programming Environments

Oracle Call Interface (OCI)

Associative Access in OCI Programs

Navigational Access in OCI Programs

Building an OCI Program That Manipulates Objects

Defining User-Defined Constructors in 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)

Representing Objects in Visual Basic (OraObject)

Representing REFs in Visual Basic (OraRef)

Representing VARRAYs and Nested 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 Strategy

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 Server

Creating SQLJ Object Types

Additional Notes About Mapping

Evolving SQLJ Types

Querying SQLJ Objects

Inserting Java Objects

Updating SQLJ Objects

Defining User-Defined Constructors in Java

5 Applying an Object Model to Relational Data

Why Use Object Views

Defining 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 Object Views

Specifying Object Identifiers for Object Views

Creating References to View Objects

Modelling Inverse Relationships with Object Views

Updating Object Views

Updating Nested Table Columns in Views

Using INSTEAD OF Triggers to Control Mutating and Validation

Applying the Object Model to Remote Tables

Defining Complex Relationships in Object Views

Tables and Types to Demonstrate Circular View References

Creating Object Views with Circular References

Object View Hierarchies

Creating an Object View Hierarchy

The Horizontal Model

The Vertical Model

Querying a View in a Hierarchy

Privileges for Operations on View Hierarchies

6 Managing Oracle Objects

Privileges on Object Types and Their Methods

System Privileges for Object Types

Schema Object Privileges

Using Types in New Types or Tables

Example: Privileges on Object Types

Privileges on Type Access and Object Access

Dependencies and Incomplete Types

Completing Incomplete Types

Manually Recompiling a Type

Type Dependencies of Substitutable Tables and Columns

The FORCE Option

Synonyms for Object 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 Local Schema Objects

Performance Tuning

Tools Providing Support for Objects

Utilities Providing Support for Objects

7 Advanced Topics for Oracle Objects

Storage of Objects

Leaf-Level Attributes

How Row Objects Are Split Across Columns

Hidden Columns for Tables with Column Objects

Hidden Columns for Substitutable Columns and Tables

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

Changes Involved When a Type Is Altered

Altering a Type by Adding a Nested Table Attribute

Validating a Type That Has Been Altered

If a Type Change Validation Fails

ALTER TYPE Statement for Type Evolution

ALTER TABLE Statement for Type Evolution

The Attribute-Value Constructor

Constructors and Type Evolution

Advantages of User-Defined 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

Storing Objects as Columns or Rows

Column Object Storage

Row Object Storage in Object Tables

Storage Considerations for Object Identifiers (OIDs)

Primary-Key Based OIDs

Performance of Object Comparisons

Design Considerations for REFs

Storage Size of REFs

Integrity Constraints for REF Columns

Performance and Storage Considerations for Scoped REFs

Indexing Scoped REFs

Speeding up Object Access Using the WITH ROWID Option

Design 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 Tables

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

Using SELF IN OUT NOCOPY with Member Procedures

Function-Based Indexes on the Return Values of Type Methods

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 Evolution

Pushing a Type Change Out to Clients

Changing Default Constructors

Altering the FINAL Property of a Type

Parallel Queries with Oracle Objects

Design Consideration Tips and Techniques

Deciding Whether to Evolve a Type or Create 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 of SQLJ Object Types

Uses of Custom Object Types

Differences Between SQLJ and Custom Object Types Through JDBC

Miscellaneous Tips

Column Substitutability and the Number of Attributes in a Hierarchy

Circular Dependencies Among Types

A Sample Application Using Object-Relational 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

Inserting Values Under the Relational Model

Querying Data Under the Relational Model

Updating Data Under the Relational Model

Deleting Data Under the Relational Model

Implementing the Schema on the Object-Relational Model

Method Definitions

The getPONo Method

The sumLineItems Method

The compareCustOrders Method

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 Values

Evolving Object Types

Adding an Attribute to the Customer Type

Working with Multilevel Collections

Inserting into Nested Tables

Inserting 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