Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Object-Relational Features?

• Oracle Database 11g Release 2 (11.2) New Features 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
  • Database Features of Oracle Objects
    • About Object Types
    • About Object Instances
    • About Object Methods
    • How Objects are Stored in Tables
    • Using Object Identifiers to Identify Row Objects
    • Using References to Row Objects
    • Using Oracle Collections
    • Using Object Views to Access Relational Data
    • Using Type Inheritance
    • Using Type Evolution to Change an Existing Object Type
  • Language Binding Features of Oracle Objects

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
    • SELF Parameters in Member Methods
    • Member Methods for Comparing Objects
  • Static Methods
  • Constructor Methods
    • System-Defined Constructors
    • User-Defined Constructors
    • Literal Invocation of a Constructor Method
  • External Implemented Methods
• Inheritance in SQL Object Types
  • Supertypes and Subtypes
  • Defining FINAL and NOT FINAL Types and Methods
  • Creating Subtypes
    • Creating a Parent or Supertype Object
    • Creating a Subtype Object
    • Generalized Invocation
    • Multiple Subtypes
    • Creating a Table that Contains Supertype and Subtype Objects
  • Declaring Types and Methods NOT INSTANTIABLE
  • Overloading and Overriding Methods
    • Overloading Methods
    • Overriding and Hiding 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 with Attributes of a Supertype
    • Substitution of REF Columns and Attributes
    • Substitution of Collection Elements
  • Storing Newly Created Subtypes in 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
    • Typical Object to Object Assignment
    • Narrowing Assignment
    • Collection Assignments
• Functions and Operators Useful with Objects
  • CAST
  • CURSOR
  • DEREF
  • IS OF type
  • REF
  • SYS_TYPEID
  • TABLE()
  • TREAT
    • Using TREAT for Narrowing Assignments
    • Using the TREAT Function to Access Subtype Attributes or Methods
  • VALUE

3 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
• Using Overloading in PL/SQL with Inheritance
• Using Dynamic SQL With Objects

4 Object Support in Oracle Programming Environments

• SQL and Object Types
• SQL Developer
• 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 Tools for Accessing Oracle Objects
  • JDBC Access to Oracle Object Data
  • SQLJ Access to Oracle Object Data
  • Choosing a Data Mapping Strategy
  • JPublisher
    • 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
  • JDeveloper
    • Application Development Framework (ADF)
    • TopLink
• XML
• Utilities Providing Support for Objects
  • Import/Export of Object Types
    • Types
    • Object View Hierarchies
  • SQL*Loader

5 Support for Collection Data Types

• Collection Data Types
  • Creating a Collection Type
  • Creating an Instance of a VARRAY or Nested Table
  • Constructor Methods for Collections
  • Varrays
  • Nested Tables
    • Storing Elements of Nested Tables
    • Specifying a Tablespace When Storing a Nested Table
  • Increasing the Size and Precision of VARRAY and Nested Table Elements
  • Increasing VARRAY Limit Size
  • Creating a Varray Containing LOB References
• Multilevel Collection Types
  • Nested Table Storage Tables for Multilevel Collection Types
  • Varray Storage for 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
    • Piecewise Operations on Nested Tables
    • Piecewise Operations on Multilevel Nested Tables
    • Atomical Changes on VARRAYs and Nested Tables
    • Collections as Atomic Data Items
  • Using BULK COLLECT to Return Entire Result Sets
  • Conditions that Compare Nested Tables
    • Equal and Not Equal Comparisons
    • IN Comparison
    • Subset of Multiset Comparison
    • Member of a Nested Table Comparison
    • Empty Comparison
    • Set Comparison
  • Multiset Operations for Nested Tables
    • CARDINALITY
    • COLLECT
    • MULTISET EXCEPT
    • MULTISET INTERSECT
    • MULTISET UNION
    • POWERMULTISET
    • POWERMULTISET_BY_CARDINALITY
    • SET
• Partitioning Tables That Contain Oracle Objects

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
  • Access Privileges on Objects, Types, and Tables
• Type Dependencies
  • Creating Incomplete Types
  • Completing Incomplete Types
  • Manually Recompiling a Type
  • Using CREATE OR REPLACE TYPE with Type and Table Dependencies
  • Type Dependencies of Substitutable Tables and Columns
  • The DROP TYPE 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

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 Object Tables
  • Storage of 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
  • Type Evolution and Dependent Schema Objects
  • Options for Updating Data
  • Effects of Structural Changes to Types
  • Altering a Type by Adding and Dropping Attributes
  • 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
• System-Defined and User-Defined Constructors
  • 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
• 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 in Relational Tables
    • Row Object Storage in Object Tables
  • Storage Considerations for Object Identifiers (OIDs)
    • System-Generated Object Identifiers (OIDs)
    • Primary-Key Based Object Identifiers (OIDs)
    • System-Generated Versus 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
  • 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 Design 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

Glossary

Index