Contents

List of Examples

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Object-Relational Features?

• Oracle Database 11g Release 1 (11.1) New Features 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
    • Object Identifiers
    • Objects and Table Storage
    • 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
    • Creating a Parent or Supertype Object
    • Creating a Subtype Object
    • Generalized Invocation
    • Multiple Subtypes
    • Creating a Table that Contains Supertype and Subtype Objects
  • NOT INSTANTIABLE Types and Methods
  • Inheriting, Overloading, and Overriding Methods
    • Overloading Methods
    • Redefining Methods
    • Restrictions on Overriding Methods
  • How Overloading Works with Inheritance
  • 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 Data Types

• Creating Collection Data Types
  • 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 Data Types
  • 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 Using PL/SQL With Object Types

• Declaring and Initializing Objects in PL/SQL
  • Defining Object Types
  • Declaring Objects in a PL/SQL Block
  • How PL/SQL Treats Uninitialized Objects
• Manipulating Objects in PL/SQL
  • Accessing Object Attributes With Dot Notation
  • Calling Object Constructors and Methods
  • Updating and Deleting Objects
  • Manipulating Objects Through Ref Modifiers
• Defining SQL Types Equivalent to PL/SQL Collection Types
  • Manipulating Individual Collection Elements with SQL
• Using PL/SQL Collections with SQL Object Types
• Using Dynamic SQL With Objects

5 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

6 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

7 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
  • JDeveloper
  • JPublisher
• Utilities Providing Support for Objects
  • Import/Export of Object Types
    • Types
    • Object View Hierarchies
  • SQL*Loader

8 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

9 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
  • Using Roles with Invoker's Rights Subprograms
• 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 Data Types 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