1/80

Contents

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

Changes in This Release for Oracle Database Object-Relational Developer's Guide

• Changes in Oracle Database 12c Release 2 (12.2.0.1)
  • New Features
  • Deprecated Features
  • Desupported 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
      • Creating and Using Object Tables
      • Performing Operations on Object Tables
    • Object Identifiers Used to Identify Row Objects
    • References to Row Objects
      • Using Scoped REFs
      • Checking for Dangling REFs
    • Dereferencing REFs
      • Dereferencing a REF with the DEREF Command
      • Dereferencing a Dangling REF
      • Dereferencing a REF Implicilty
    • Obtaining a REF to a Row Object
    • REF Variables Compared
    • Oracle Collections Data Types
    • Object Views Used to Access Relational Data
    • Use of Type Inheritance
    • Type Evolution Used 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
  • Defining Object Tables with Single Constraints
  • Defining Object Tables with Multiple Constraints
  • Defining Indexes for Object Tables
  • Defining 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
  • About Object Methods
  • Member Methods
    • Declaring SELF Parameters in Member Methods
    • Member Methods for Comparing Objects
      • About Map Methods
      • Creating a Map Method
      • Invoking a Map Method
      • Order Methods
      • Guidelines for Comparison Methods
      • Comparison Methods in Type Hierarchies
  • Declaring and Invoking Static Methods
  • Constructor Methods
    • System-Defined Constructors
    • Defining User-Defined Constructors
    • Literal Invocation of a Constructor Method
  • External Implemented Methods
• Inheritance in SQL Object Types
  • About Inheritance in SQL Object Types
  • Supertypes and Subtypes
  • FINAL and NOT FINAL Types and Methods for Inheritance
    • Creating an Object Type as NOT FINAL with a FINAL Member Function
    • Creating a NOT FINAL Object Type
  • Changing a FINAL TYPE to NOT FINAL
  • Subtype Creation
    • Creating a Parent or Supertype Object
    • Creating a Subtype Object
    • Generalized Invocation
    • Using Generalized Invocation
    • Using Generalized Expression
    • Creating Multiple Subtypes
    • Creating a Subtype Under Another Subtype
    • Creating Tables that Contain Supertype and Subtype Objects
  • NOT INSTANTIABLE Types and Methods
  • Creating a Non-INSTANTIABLE Object Type
  • Changing an Object Type to INSTANTIABLE
  • Overloaded and Overridden Methods
    • Overloading Methods
    • Overriding and Hiding Methods
    • Restrictions on Overriding Methods
  • Dynamic Method Dispatch
  • Type Substitution in a Type Hierarchy
  • Column and Row Substitutability
    • About 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
  • Newly Created Subtypes Stored in Substitutable Columns
  • Dropping Subtypes After Creating Substitutable Columns
  • Turning Off Substitutability in a New Table
  • Constraining Substitutability
  • Modifying Substitutability on a Table
  • Restrictions on Modifying Substitutability
  • Assignments Across Types
    • Typical Object to Object Assignment
    • Widening 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
• Object Manipulation in PL/SQL
  • Accessing Object Attributes With Dot Notation
  • Calling Object Constructors and Methods
  • Accessing Object Methods
  • Updating and Deleting Objects
  • Manipulating Object Manipulation with Ref Modifiers
• Use of Overloading in PL/SQL with Inheritance
  • Resolving PL/SQL Functions with Inheritance
  • Resolving PL/SQL Functions with Inheritance Dynamically
• Using Dynamic SQL With Objects
  • Using Dynamic SQL with Object Types and Collections
  • Calling Package Procedures with Object Types and Collections

4 Object Support in Oracle Programming Environments

• SQL and Object Types
• SQL Developer
• PL/SQL
• Oracle Call Interface (OCI)
  • About 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++
  • About Pro*C/C++
  • Associative Access in Pro*C/C++
  • Navigational Access in Pro*C/C++
  • Conversion Between Oracle Types and C Types
  • Oracle Type Translator (OTT)
• Oracle C++ Call Interface (OCCI)
  • About Oracle C++ Call Interface (OCCI)
  • OCCI Associative Relational and Object Interfaces
  • The OCCI Navigational Interface
• Java Tools for Accessing Oracle Objects
  • JDBC Access to Oracle Object Data
  • Data Mapping Strategies
  • JPublisher
    • JPublisher Used to Create Java Classes for JDBC and SQLJ Programs
    • What JPublisher Produces for a User-Defined Object Type
  • Java Object Storage
    • Creating SQLJ Object Types
    • Additional Notes About Mapping
    • SQLJ Type Evolution
    • 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
  • Using the Constructor Method to Insert Values into a Nested Table
  • Invoking Constructors Literally to Specify Defaults
  • About Varrays
  • Creating and Populating a VARRAY
  • Nested Tables
    • Creating 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
    • Creating Multilevel Nested Table Storage
    • Creating Multilevel Nested Table Storage Using the COLUMN_VALUE Keyword
    • Specifying Physical Attributes for Nested Table Storage
  • Varray Storage for Multilevel Collections
  • Specifying LOB Storage for VARRAY of VARRAY Type
  • Specifying LOB Storage for a Nested Table of VARRAYs
  • Constructors for Multilevel Collections
• Operations on Collection Data Types
  • Collection Querying
    • Nesting Results of Collection Queries
    • Unnesting Results of Collection Queries
    • Unnesting Queries Containing Table Expression Subqueries
    • Using a Table Expression in a CURSOR Expression
    • Unnesting Queries with Multilevel Collections
  • DML Operations on Collections
    • Performing Piecewise Operations on Nested Tables
      • Updating a Nested Table
    • Performing Piecewise Operations on Multilevel Nested Tables
      • Performing Piecewise INSERT to Inner Nested Table
    • Performing Atomical Changes on VARRAYs and Nested Tables
    • Updating Collections as Atomic Data Items
  • Using BULK COLLECT to Return Entire Result Sets
  • Conditions that Compare Nested Tables
    • Comparing Equal and Not Equal Conditions
    • Comparing the IN Condition
    • Comparing Subset of Multiset Conditions
    • Determing Members of a Nested Table
    • Determining Empty Conditions
    • Determining Set Conditions
  • 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
• Object Views Used in Applications
• Objects Nested in Object Views
• Identifying Null Objects in Object Views
• Nested Tables and Varrays Used in Object Views
  • Single-Level Collections in Object Views
  • Multilevel Collections in Object Views
• Object Identifiers for Object Views
• References Created to View Objects
• Creating References to Objects with REF
• Inverse Relationships Modelled with Object Views
• Object View Manipulations
  • Nested Table Columns Updated in Views
  • 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
    • Method 1: Re-create First View After Creating Second View
    • Method 2: Create First View Using FORCE Keyword
• Object View Hierarchies
  • Creating an Object View Hierarchy
    • The Flat Model
    • The Horizontal Model
    • The Vertical Model
  • About 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
  • Types Used 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
  • Recompiling a Type Manually
  • Using CREATE OR REPLACE TYPE with Type and Table Dependencies
  • Creating or Replacing Type with Force
  • 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
  • Querying for Typeids of Objects Stored in 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
  • About 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
  • About Validating Types That Have Been Altered
  • ALTER TYPE Statement for Type Evolution
  • ALTER TABLE Statement for Type Evolution
• Storing XMLTypes and LOBs in an ANYDATA Column
• 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
  • Overloaded and Hidden 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
  • About 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 for Scoped REFs
  • Performance Improvement for Object Access Using the WITH ROWID Option
• Design Considerations for Collections
  • Viewing Object Data in Relational Form with Unnesting Queries
    • Creating Procedures and Functions to Unnest Queries
    • Querying the TABLE Function to Unnest Data
  • Storage Considerations for Varrays
    • About Propagating VARRAY Size Change
  • Performance of Varrays Versus Nested Tables
  • Design Considerations for Nested Tables
    • Nested Table Storage
    • Nested Table Indexes
    • Nested Table Locators
      • At Table Creation Time
      • As a HINT During Retrieval
    • Set Membership Query Optimization
  • Design Considerations for Multilevel Collections
    • Creating an Object Table with a Multilevel Collection
    • Creating an Object Table Using REFs
    • Inserting Values into the PEOPLE_OBJTAB Object Table
• Design Considerations for Methods
  • Choice of Language for Method Functions
  • Static Methods
  • About Using SELF IN OUT NOCOPY with Member Procedures
  • Function-Based Indexes on the Return Values of Type Methods
• Reusable Code Using Invoker Rights
• Roles with Invoker's Rights Subprograms
• Replication Support for Objects
  • Object Replication Using Oracle Golden Gate
  • Active Data Guard and Logical Standby Support for Objects
• Materialized View Support for Objects
  • Object, Collection, or REF Type Columns
  • Object Tables
• Constraints on Objects
• Considerations Related to Type Evolution
  • Pushing a Type Change Out to Clients
  • About Changing Default Constructors
  • About Altering the FINAL Property of a Type
• Parallel Queries with Oracle Objects
• Design Consideration Tips and Techniques
  • 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

Glossary

Index