Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience
Documentation Accessibility
Structure
Related Documents
Conventions

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
Object Types
Objects
Object Methods
Type Inheritance
Type Evolution
Object Tables
Row Objects and Column Objects
Object Views
References
Collections
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
Object Methods
Member Methods
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 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
CAST
CURSOR
DEREF
IS OF type
REF
SYS_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 Storing a Nested Table
Varray Storage
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
In Comparisons
Subset of Multiset Comparison
Member of a Nested Table Comparison
Empty Comparison
Set Comparison
Multisets Operations
CARDINALITY
COLLECT
MULTISET EXCEPT
MULTISET INTERSECTION
MULTISET UNION
POWERMULTISET
POWERMULTISET_BY_CARDINALITY
SET

4 Object Support in Oracle Programming 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
Defining 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)
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
Constraints
Querying SQLJ Objects
Inserting Java Objects
Updating SQLJ Objects
Defining User-Defined Constructors in Java
XML

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 Flat Model
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
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
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
Static Methods
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
Stock_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
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
Deleting
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