Contents

Title and Copyright Information

Send Us Your Comments

Preface

New Object-Relational Features

1 Introduction to Oracle Objects

About Oracle Objects and Object Types

Advantages of Objects

Key Features of the Object-Relational Model

2 Basic Components of Oracle Objects

Object-Relational Elements

Object Types

Type Inheritance

Objects

Methods

Object Tables

Row Objects and Column Objects

Object Views

REF Datatype

Scoped REFs

Dangling REFs

Dereferencing REFs

Obtaining REFs

Collections

Defining Object and Collection Types

Object Types and References

Null Objects and Attributes

Default Values for Objects and Collections

Constraints for Object Tables

Indexes for Object Tables and Nested 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

Member Methods

Methods for Comparing Objects

Map Methods

Order Methods

Guidelines

Comparison Methods in Type Hierarchies

Static Methods

Constructor Methods

Collections

Varrays

Nested Tables

Multi-Level Collection Types

Nested Table Storage Tables

Varray Storage

Assignment and Comparison of Multi-Level Collections

Creating a VARRAY or Nested Table

Constructors for Multi-Level Collections

Querying Collections

Nesting Results of Collection Queries

Unnesting Results of Collection Queries

Unnesting Queries Containing Table Expression Subqueries

Unnesting Queries with Multi-Level Collections

Performing DML Operations on Collections

Performing DML on Multi-Level Collections

Type Inheritance

Types and Subtypes

FINAL and NOT FINAL Types and Methods

Creating Subtypes

NOT INSTANTIABLE Types and Methods

Inheriting, Overloading, and Overriding Methods

Overloading Methods

Overriding Methods

Restrictions on Overriding Methods

Dynamic Method Dispatch

Substituting Types in a Type Hierarchy

Attribute Substitutability

Column and Row Substitutability

Subtypes Having Supertype Attributes

REF Columns and Attributes

Collection Elements

Turning Off Substitutability

Constraining Substitutability

Assignments Across Types

Objects and REFs to Objects

Collection Assignments

Comparisons: Objects, REF Variables, and Collections

Comparing Object Instances

Comparing REF Variables

Comparing Collections.

Functions and Predicates Useful with Objects

VALUE

REF

DEREF

TREAT

IS OF type

SYS_TYPEID

3 Object Support in Oracle Programmatic Environments

SQL

PL/SQL

Oracle Call Interface (OCI)

Associative Access in OCI Programs

Navigational Access in OCI Programs

Object Cache

Building an OCI Program that Manipulates Objects

Oracle C++ Call Interface (OCCI)

OCCI Associative Relational and Object Interfaces

The OCCI Navigational Interface

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

Java Object Storage

Representing SQLJ Types to the Server

Creating SQLJ Object Types

Sample SQLJ Object Type Mapping

More About Mapping

Evolving SQLJ Types

Constraints

Querying SQLJ Objects

Inserting Java Objects

Updating SQLJ Objects

4 Managing Oracle Objects

Privileges on Object Types and Their Methods

System Privileges

Schema Object Privileges

Using Types in New Types or Tables

Example

Privileges on Type Access and Object Access

Dependencies and Incomplete Types

Completing Incomplete Types

Type Dependencies of Substitutable Tables and Columns

The FORCE Option

Tools

JDeveloper

Business Components for Java (BC4J)

JPublisher

Utilities

Import/Export of Object Types

Types

Object View Hierarchies

SQL*Loader

5 Applying an Object Model to Relational Data

Why to 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

Multi-Level 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 Flat Model

The Horizontal Model

The Vertical Model

Querying a View in a Hierarchy

Privileges for Operations on View Hierarchies

6 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

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

Object Identifiers

Type Evolution

Changes Required by a Change to a Type

Steps to Change a Type

Validating a Type

If a Type Change Validation Fails

ALTER TYPE Options for Type Evolution

ALTER TABLE Option for Type Evolution

OCI Tips and Techniques for Objects

Initializing an OCI Program in Object Mode

Creating a New Object

Updating an Object

Deleting an Object

Controlling Object Cache Size

Retrieving Objects into the Client Cache (Pinning)

Specifying which Version of an Object to Retrieve

Specifying How Long to Keep the Object Pinned

Specifying Whether to Lock the Object on the Server

How to Choose the Locking Technique

Flushing an Object from the Object Cache

Pre-Fetching Related Objects (Complex Object Retrieval)

Demonstration of OCI and Oracle Objects

Using the OCI Object Cache with View Objects

Transient and Generic Types

User-Defined Aggregate Functions

Partitioning Tables that Contain Oracle Objects

Parallel Query with Object Views

How Locators Improve the Performance of Nested Tables

7 Frequently Asked Questions About Using Oracle Objects

General Questions about Oracle Objects

Are the object-relational features a separate option?

What are the design goals of Oracle9i Object-Relational & Extensibility technologies?

Object Types

What is structured data?

Where are the user-defined types, user-defined functions, and abstract data types?

What is an object type?

Why are object types useful?

How is object data stored and managed in Oracle9i?

Is inheritance supported in Oracle9i?

Object Methods

What language can I use to write my object methods?

How do I decide between using PL/SQL and Java for my object methods?

When should I use external procedures?

What are definer and invoker rights?

Object References

What is an object reference?

When should I use object references? How are they different from foreign keys?

Can I construct object references based on primary keys?

What is a scoped REF and when should I use it?

Can I manipulate objects using object references in PL/SQL and Java?

Collections

What kinds of collections are supported by Oracle9i?

Do Oracle Objects support collections within collections?

How do I decide between using varrays and nested tables for modeling collections?

What is a collection locator?

What is collection unnesting?

Object Views

What are the differences between object views and object tables?

Are object views updateable?

Object Cache

Why do we need the object cache?

Does the object cache support object locking?

Large Objects (LOBs)

How can I manage large objects using Oracle?

User-Defined Operators

What is a user-defined operator?

Why are user-defined operators useful?

8 Design Considerations for Oracle Objects

Representing Objects as Columns or Rows

Column Object Storage

Row Object Storage in Object Tables

Performance of Object Comparisons

Storage Considerations for Object Identifiers (OIDs)

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

Viewing Object Data in Relational Form with Unnesting Queries

Using Procedures and Functions in Unnesting Queries

Storage Considerations for Varrays

Performance of Varrays Versus Nested Tables

Nested Tables

Nested Table Storage

Nested Table in an Index-Organized Table (IOT)

Nested Table Indexes

Nested Table Locators

Optimizing Set Membership Queries

DML Operations on Nested Tables

Multi-Level Collections

Choosing a Language for Method Functions

Static Methods

Writing Reusable Code using Invoker Rights

Function-Based Indexes on the Return Values of Type Methods

Converting to the Current Object Format

Replicating Object Tables and Columns

Replicating Columns of Object, Collection, or REF Type

Replicating Object Tables

Constraints on Objects

Type Evolution

Pushing a Type Change Out to Clients

Changing Default Constructors

Altering the FINAL Property of a Type

Performance Tuning

Parallel Queries with Oracle Objects

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

What is the intended use of SQLJ Object Type?

What is involved in creating a SQLJ Object Type?

When would you use SQLJ Object Type?

When would you use Custom Object Type?

What are the differences between the SQLJ and Custom Object Types through JDBC?

Miscellaneous Tips

Column Substitutability and the Number of Attributes in a Hierarchy

Circular Dependencies Among Types

PL/SQL and TREAT and IS OF

9 A Sample Application Using Object-Relational Features

Introduction

A Purchase Order Example

Implementing the Application Under 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 Under The Relational Model

Updating Data Under The Relational Model

Deleting Data Under The Relational Model

Implementing the Application Under The Object-Relational Model

Defining Types

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

Querying

Average Discount across all Line Items of all Purchase Orders

Deleting

Manipulating Objects Through Java

Using oracle.sql.* Classes (Weak Typing)

Using Strong Typing (SQLData or CustomDatum)

Generating Wrapper Classes with JPublisher

How to Use the Wrapper Classes

Sample Program Using the SQLData Interface

Manipulating Objects with Oracle Objects for OLE

Selecting Data

Inserting Data

Updating Data

Calling a Method Function

Index