Skip to main content
Oracle Help Center

Sign Out

Sign In

  • Table of Contents
  • Search
  • Print
  • Download
  1. Database
  2. Oracle
  3. Oracle Database
  4. Release 19

Object-Relational Developer's Guide

  • Table of Contents
  • Search
  • Print
  • Download

Table of Contents

Expand AllCollapse All
  • 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 Release 19c, Version 19.1
  • 1 Introduction to Oracle Objects
    • 1.1 About Oracle Objects
    • 1.2 Advantages of Objects
    • 1.3 Key Features of the Object-Relational Model
      • 1.3.1 Database Features of Oracle Objects
        • 1.3.1.1 About Object Types
        • 1.3.1.2 About Object Instances
        • 1.3.1.3 About Object Methods
        • 1.3.1.4 How Objects are Stored in Tables
          • 1.3.1.4.1 Creating and Using Object Tables
          • 1.3.1.4.2 Performing Operations on Object Tables
        • 1.3.1.5 Object Identifiers Used to Identify Row Objects
        • 1.3.1.6 References to Row Objects
          • 1.3.1.6.1 Using Scoped REFs
          • 1.3.1.6.2 Checking for Dangling REFs
        • 1.3.1.7 Dereferencing REFs
          • 1.3.1.7.1 Dereferencing a REF with the DEREF Command
          • 1.3.1.7.2 Dereferencing a Dangling REF
          • 1.3.1.7.3 Dereferencing a REF Implicilty
        • 1.3.1.8 Obtaining a REF to a Row Object
        • 1.3.1.9 REF Variables Compared
        • 1.3.1.10 Oracle Collections Data Types
        • 1.3.1.11 Object Views Used to Access Relational Data
        • 1.3.1.12 Use of Type Inheritance
        • 1.3.1.13 Type Evolution Used to Change an Existing Object Type
      • 1.3.2 Language Binding Features of Oracle Objects
  • 2 Basic Components of Oracle Objects
    • 2.1 SQL Object Types and References
      • 2.1.1 Null Objects and Attributes
      • 2.1.2 Character Length Semantics
      • 2.1.3 Defining Object Tables with Single Constraints
      • 2.1.4 Defining Object Tables with Multiple Constraints
      • 2.1.5 Defining Indexes for Object Tables
      • 2.1.6 Defining Triggers for Object Tables
      • 2.1.7 Rules for REF Columns and Attributes
      • 2.1.8 Name Resolution
        • 2.1.8.1 When Table Aliases Are Required
      • 2.1.9 Restriction on Using User-Defined Types with a Remote Database
    • 2.2 Object Methods
      • 2.2.1 About Object Methods
      • 2.2.2 Member Methods
        • 2.2.2.1 Declaring SELF Parameters in Member Methods
        • 2.2.2.2 Member Methods for Comparing Objects
          • 2.2.2.2.1 About Map Methods
          • 2.2.2.2.2 Creating a Map Method
          • 2.2.2.2.3 Invoking a Map Method
          • 2.2.2.2.4 Order Methods
          • 2.2.2.2.5 Guidelines for Comparison Methods
          • 2.2.2.2.6 Comparison Methods in Type Hierarchies
      • 2.2.3 Declaring and Invoking Static Methods
      • 2.2.4 Constructor Methods
        • 2.2.4.1 System-Defined Constructors
        • 2.2.4.2 Defining User-Defined Constructors
        • 2.2.4.3 Literal Invocation of a Constructor Method
      • 2.2.5 External Implemented Methods
    • 2.3 Inheritance in SQL Object Types
      • 2.3.1 About Inheritance in SQL Object Types
      • 2.3.2 Supertypes and Subtypes
      • 2.3.3 FINAL and NOT FINAL Types and Methods for Inheritance
        • 2.3.3.1 Creating an Object Type as NOT FINAL with a FINAL Member Function
        • 2.3.3.2 Creating a NOT FINAL Object Type
      • 2.3.4 Changing a FINAL TYPE to NOT FINAL
      • 2.3.5 Subtype Creation
        • 2.3.5.1 Creating a Parent or Supertype Object
        • 2.3.5.2 Creating a Subtype Object
        • 2.3.5.3 Generalized Invocation
        • 2.3.5.4 Using Generalized Invocation
        • 2.3.5.5 Using Generalized Expression
        • 2.3.5.6 Creating Multiple Subtypes
        • 2.3.5.7 Creating a Subtype Under Another Subtype
        • 2.3.5.8 Creating Tables that Contain Supertype and Subtype Objects
      • 2.3.6 NOT INSTANTIABLE Types and Methods
      • 2.3.7 Creating a Non-INSTANTIABLE Object Type
      • 2.3.8 Changing an Object Type to INSTANTIABLE
      • 2.3.9 Overloaded and Overridden Methods
        • 2.3.9.1 Overloading Methods
        • 2.3.9.2 Overriding and Hiding Methods
        • 2.3.9.3 Restrictions on Overriding Methods
      • 2.3.10 Dynamic Method Dispatch
      • 2.3.11 Type Substitution in a Type Hierarchy
      • 2.3.12 Column and Row Substitutability
        • 2.3.12.1 About Column and Row Substitutability
        • 2.3.12.2 Using OBJECT_VALUE and OBJECT_ID with Substitutable Rows
        • 2.3.12.3 Subtypes with Attributes of a Supertype
        • 2.3.12.4 Substitution of REF Columns and Attributes
        • 2.3.12.5 Substitution of Collection Elements
      • 2.3.13 Newly Created Subtypes Stored in Substitutable Columns
      • 2.3.14 Dropping Subtypes After Creating Substitutable Columns
      • 2.3.15 Turning Off Substitutability in a New Table
      • 2.3.16 Constraining Substitutability
      • 2.3.17 Modifying Substitutability on a Table
      • 2.3.18 Restrictions on Modifying Substitutability
      • 2.3.19 Assignments Across Types
        • 2.3.19.1 Typical Object to Object Assignment
        • 2.3.19.2 Widening Assignment
        • 2.3.19.3 Narrowing Assignment
        • 2.3.19.4 Collection Assignments
    • 2.4 Functions and Operators Useful with Objects
      • 2.4.1 CAST
      • 2.4.2 CURSOR
      • 2.4.3 DEREF
      • 2.4.4 IS OF type
      • 2.4.5 REF
      • 2.4.6 SYS_TYPEID
      • 2.4.7 TABLE()
      • 2.4.8 TREAT
        • 2.4.8.1 Using TREAT for Narrowing Assignments
        • 2.4.8.2 Using the TREAT Function to Access Subtype Attributes or Methods
      • 2.4.9 VALUE
  • 3 Using PL/SQL With Object Types
    • 3.1 Declaring and Initializing Objects in PL/SQL
      • 3.1.1 Defining Object Types
      • 3.1.2 Declaring Objects in a PL/SQL Block
      • 3.1.3 How PL/SQL Treats Uninitialized Objects
    • 3.2 Object Manipulation in PL/SQL
      • 3.2.1 Accessing Object Attributes With Dot Notation
      • 3.2.2 Calling Object Constructors and Methods
      • 3.2.3 Accessing Object Methods
      • 3.2.4 Updating and Deleting Objects
      • 3.2.5 Manipulating Object Manipulation with Ref Modifiers
    • 3.3 Use of Overloading in PL/SQL with Inheritance
      • 3.3.1 Resolving PL/SQL Functions with Inheritance
      • 3.3.2 Resolving PL/SQL Functions with Inheritance Dynamically
    • 3.4 Using Dynamic SQL With Objects
      • 3.4.1 Using Dynamic SQL with Object Types and Collections
      • 3.4.2 Calling Package Procedures with Object Types and Collections
  • 4 Object Support in Oracle Programming Environments
    • 4.1 SQL and Object Types
    • 4.2 SQL Developer
    • 4.3 PL/SQL
    • 4.4 Oracle Call Interface (OCI)
      • 4.4.1 About Oracle Call Interface (OCI)
      • 4.4.2 Associative Access in OCI Programs
      • 4.4.3 Navigational Access in OCI Programs
      • 4.4.4 Object Cache
      • 4.4.5 Building an OCI Program That Manipulates Objects
      • 4.4.6 Defining User-Defined Constructors in C
    • 4.5 Pro*C/C++
      • 4.5.1 About Pro*C/C++
      • 4.5.2 Associative Access in Pro*C/C++
      • 4.5.3 Navigational Access in Pro*C/C++
      • 4.5.4 Conversion Between Oracle Types and C Types
      • 4.5.5 Oracle Type Translator (OTT)
    • 4.6 Oracle C++ Call Interface (OCCI)
      • 4.6.1 About Oracle C++ Call Interface (OCCI)
      • 4.6.2 OCCI Associative Relational and Object Interfaces
      • 4.6.3 The OCCI Navigational Interface
    • 4.7 Java Tools for Accessing Oracle Objects
      • 4.7.1 JDBC Access to Oracle Object Data
      • 4.7.2 Data Mapping Strategies
      • 4.7.3 Java Object Storage
        • 4.7.3.1 Creating SQLJ Object Types
        • 4.7.3.2 Additional Notes About Mapping
        • 4.7.3.3 SQLJ Type Evolution
        • 4.7.3.4 Constraints
        • 4.7.3.5 Querying SQLJ Objects
        • 4.7.3.6 Inserting Java Objects
        • 4.7.3.7 Updating SQLJ Objects
      • 4.7.4 Defining User-Defined Constructors in Java
      • 4.7.5 JDeveloper
        • 4.7.5.1 Application Development Framework (ADF)
        • 4.7.5.2 TopLink
    • 4.8 XML
    • 4.9 Utilities Providing Support for Objects
      • 4.9.1 Import/Export of Object Types
        • 4.9.1.1 Types
        • 4.9.1.2 Object View Hierarchies
      • 4.9.2 SQL*Loader
  • 5 Support for Collection Data Types
    • 5.1 Collection Data Types
      • 5.1.1 Creating a Collection Type
      • 5.1.2 Creating an Instance of a VARRAY or Nested Table
      • 5.1.3 Using the Constructor Method to Insert Values into a Nested Table
      • 5.1.4 Invoking Constructors Literally to Specify Defaults
      • 5.1.5 About Varrays
      • 5.1.6 Creating and Populating a VARRAY
      • 5.1.7 Nested Tables
        • 5.1.7.1 Creating Nested Tables
        • 5.1.7.2 Storing Elements of Nested Tables
        • 5.1.7.3 Specifying a Tablespace When Storing a Nested Table
      • 5.1.8 Increasing the Size and Precision of VARRAY and Nested Table Elements
      • 5.1.9 Increasing VARRAY Limit Size
      • 5.1.10 Creating a Varray Containing LOB References
    • 5.2 Multilevel Collection Types
      • 5.2.1 Nested Table Storage Tables for Multilevel Collection Types
        • 5.2.1.1 Creating Multilevel Nested Table Storage
        • 5.2.1.2 Creating Multilevel Nested Table Storage Using the COLUMN_VALUE Keyword
        • 5.2.1.3 Specifying Physical Attributes for Nested Table Storage
      • 5.2.2 Varray Storage for Multilevel Collections
      • 5.2.3 Specifying LOB Storage for VARRAY of VARRAY Type
      • 5.2.4 Specifying LOB Storage for a Nested Table of VARRAYs
      • 5.2.5 Constructors for Multilevel Collections
    • 5.3 Operations on Collection Data Types
      • 5.3.1 Collection Querying
        • 5.3.1.1 Nesting Results of Collection Queries
        • 5.3.1.2 Unnesting Results of Collection Queries
        • 5.3.1.3 Unnesting Queries Containing Table Expression Subqueries
        • 5.3.1.4 Using a Table Expression in a CURSOR Expression
        • 5.3.1.5 Unnesting Queries with Multilevel Collections
      • 5.3.2 DML Operations on Collections
        • 5.3.2.1 Performing Piecewise Operations on Nested Tables
          • 5.3.2.1.1 Updating a Nested Table
        • 5.3.2.2 Performing Piecewise Operations on Multilevel Nested Tables
          • 5.3.2.2.1 Performing Piecewise INSERT to Inner Nested Table
        • 5.3.2.3 Performing Atomical Changes on VARRAYs and Nested Tables
        • 5.3.2.4 Updating Collections as Atomic Data Items
      • 5.3.3 Using BULK COLLECT to Return Entire Result Sets
      • 5.3.4 Conditions that Compare Nested Tables
        • 5.3.4.1 Comparing Equal and Not Equal Conditions
        • 5.3.4.2 Comparing the IN Condition
        • 5.3.4.3 Comparing Subset of Multiset Conditions
        • 5.3.4.4 Determing Members of a Nested Table
        • 5.3.4.5 Determining Empty Conditions
        • 5.3.4.6 Determining Set Conditions
      • 5.3.5 Multiset Operations for Nested Tables
        • 5.3.5.1 CARDINALITY
        • 5.3.5.2 COLLECT
        • 5.3.5.3 MULTISET EXCEPT
        • 5.3.5.4 MULTISET INTERSECT
        • 5.3.5.5 MULTISET UNION
        • 5.3.5.6 POWERMULTISET
        • 5.3.5.7 POWERMULTISET_BY_CARDINALITY
        • 5.3.5.8 SET
    • 5.4 Partitioning Tables That Contain Oracle Objects
  • 6 Applying an Object Model to Relational Data
    • 6.1 Why Use Object Views
    • 6.2 Defining Object Views
    • 6.3 Object Views Used in Applications
    • 6.4 Objects Nested in Object Views
    • 6.5 Identifying Null Objects in Object Views
    • 6.6 Nested Tables and Varrays Used in Object Views
      • 6.6.1 Single-Level Collections in Object Views
      • 6.6.2 Multilevel Collections in Object Views
    • 6.7 Object Identifiers for Object Views
    • 6.8 References Created to View Objects
    • 6.9 Creating References to Objects with REF
    • 6.10 Inverse Relationships Modelled with Object Views
    • 6.11 Object View Manipulations
      • 6.11.1 Nested Table Columns Updated in Views
      • 6.11.2 INSTEAD OF Triggers to Control Mutating and Validation
    • 6.12 Applying the Object Model to Remote Tables
    • 6.13 Defining Complex Relationships in Object Views
      • 6.13.1 Tables and Types to Demonstrate Circular View References
      • 6.13.2 Creating Object Views with Circular References
        • 6.13.2.1 Method 1: Re-create First View After Creating Second View
        • 6.13.2.2 Method 2: Create First View Using FORCE Keyword
    • 6.14 Object View Hierarchies
      • 6.14.1 Creating an Object View Hierarchy
        • 6.14.1.1 The Flat Model
        • 6.14.1.2 The Horizontal Model
        • 6.14.1.3 The Vertical Model
      • 6.14.2 About Querying a View in a Hierarchy
      • 6.14.3 Privileges for Operations on View Hierarchies
  • 7 Managing Oracle Objects
    • 7.1 Privileges on Object Types and Their Methods
      • 7.1.1 System Privileges for Object Types
      • 7.1.2 Schema Object Privileges
      • 7.1.3 Types Used in New Types or Tables
      • 7.1.4 Example: Privileges on Object Types
      • 7.1.5 Access Privileges on Objects, Types, and Tables
    • 7.2 Type Dependencies
      • 7.2.1 Creating Incomplete Types
      • 7.2.2 Completing Incomplete Types
      • 7.2.3 Recompiling a Type Manually
      • 7.2.4 Using CREATE OR REPLACE TYPE with Type and Table Dependencies
      • 7.2.5 Creating or Replacing Type with Force
      • 7.2.6 Type Dependencies of Substitutable Tables and Columns
      • 7.2.7 The DROP TYPE FORCE Option
    • 7.3 Synonyms for Object Types
      • 7.3.1 Creating a Type Synonym
      • 7.3.2 Using a Type Synonym
        • 7.3.2.1 Describing Schema Objects That Use Synonyms
        • 7.3.2.2 Dependents of Type Synonyms
        • 7.3.2.3 Restriction on Replacing a Type Synonym
        • 7.3.2.4 Dropping Type Synonyms
        • 7.3.2.5 Renaming Type Synonyms
        • 7.3.2.6 Public Type Synonyms and Local Schema Objects
    • 7.4 Performance Tuning
  • 8 Advanced Topics for Oracle Objects
    • 8.1 Storage of Objects
      • 8.1.1 Leaf-Level Attributes
      • 8.1.2 How Row Objects Are Split Across Columns
      • 8.1.3 Hidden Columns for Tables with Column Objects
      • 8.1.4 Hidden Columns for Substitutable Columns and Object Tables
      • 8.1.5 Querying for Typeids of Objects Stored in Tables
      • 8.1.6 Storage of REFs
      • 8.1.7 Internal Layout of Nested Tables
      • 8.1.8 Internal Layout of VARRAYs
    • 8.2 Creating Indexes on Typeids or Attributes
      • 8.2.1 Indexing a Type-Discriminant Column
      • 8.2.2 Indexing Subtype Attributes of a Substitutable Column
    • 8.3 Type Evolution
      • 8.3.1 About Type Evolution
      • 8.3.2 Type Evolution and Dependent Schema Objects
      • 8.3.3 Options for Updating Data
      • 8.3.4 Effects of Structural Changes to Types
      • 8.3.5 Altering a Type by Adding and Dropping Attributes
      • 8.3.6 Altering a Type by Adding a Nested Table Attribute
      • 8.3.7 About Validating Types That Have Been Altered
      • 8.3.8 ALTER TYPE Statement for Type Evolution
      • 8.3.9 ALTER TABLE Statement for Type Evolution
    • 8.4 Storing XMLTypes and LOBs in an ANYDATA Column
    • 8.5 System-Defined and User-Defined Constructors
      • 8.5.1 The Attribute-Value Constructor
      • 8.5.2 Constructors and Type Evolution
      • 8.5.3 Advantages of User-Defined Constructors
      • 8.5.4 Defining and Implementing User-Defined Constructors
      • 8.5.5 Overloaded and Hidden Constructors
      • 8.5.6 Calling User-Defined Constructors
      • 8.5.7 Constructors for SQLJ Object Types
    • 8.6 Transient and Generic Types
    • 8.7 User-Defined Aggregate Functions
    • 8.8 How Locators Improve the Performance of Nested Tables
  • 9 Design Considerations for Oracle Objects
    • 9.1 General Storage Considerations for Objects
      • 9.1.1 About Storing Objects as Columns or Rows
        • 9.1.1.1 Column Object Storage in Relational Tables
        • 9.1.1.2 Row Object Storage in Object Tables
      • 9.1.2 Storage Considerations for Object Identifiers (OIDs)
        • 9.1.2.1 System-Generated Object Identifiers (OIDs)
        • 9.1.2.2 Primary-Key Based Object Identifiers (OIDs)
        • 9.1.2.3 System-Generated Versus Primary-Key Based OIDs
    • 9.2 Performance of Object Comparisons
    • 9.3 Design Considerations for REFs
      • 9.3.1 Storage Size of REFs
      • 9.3.2 Integrity Constraints for REF Columns
      • 9.3.3 Performance and Storage Considerations for Scoped REFs
        • 9.3.3.1 Indexing for Scoped REFs
      • 9.3.4 Performance Improvement for Object Access Using the WITH ROWID Option
    • 9.4 Design Considerations for Collections
      • 9.4.1 Viewing Object Data in Relational Form with Unnesting Queries
        • 9.4.1.1 Creating Procedures and Functions to Unnest Queries
        • 9.4.1.2 Querying the TABLE Function to Unnest Data
      • 9.4.2 Storage Considerations for Varrays
        • 9.4.2.1 About Propagating VARRAY Size Change
      • 9.4.3 Performance of Varrays Versus Nested Tables
      • 9.4.4 Design Considerations for Nested Tables
        • 9.4.4.1 Nested Table Storage
        • 9.4.4.2 Nested Table Indexes
        • 9.4.4.3 Nested Table Locators
          • 9.4.4.3.1 At Table Creation Time
          • 9.4.4.3.2 As a HINT During Retrieval
        • 9.4.4.4 Set Membership Query Optimization
      • 9.4.5 Design Considerations for Multilevel Collections
        • 9.4.5.1 Creating an Object Table with a Multilevel Collection
        • 9.4.5.2 Creating an Object Table Using REFs
        • 9.4.5.3 Inserting Values into the PEOPLE_OBJTAB Object Table
    • 9.5 Design Considerations for Methods
      • 9.5.1 Choice of Language for Method Functions
      • 9.5.2 Static Methods
      • 9.5.3 About Using SELF IN OUT NOCOPY with Member Procedures
      • 9.5.4 Function-Based Indexes on the Return Values of Type Methods
    • 9.6 Reusable Code Using Invoker Rights
    • 9.7 Roles with Invoker's Rights Subprograms
    • 9.8 Replication Support for Objects
      • 9.8.1 Object Replication Using Oracle Golden Gate
      • 9.8.2 Active Data Guard and Logical Standby Support for Objects
    • 9.9 Materialized View Support for Objects
      • 9.9.1 Object, Collection, or REF Type Columns
      • 9.9.2 Object Tables
    • 9.10 Constraints on Objects
    • 9.11 Considerations Related to Type Evolution
      • 9.11.1 Pushing a Type Change Out to Clients
      • 9.11.2 About Changing Default Constructors
      • 9.11.3 About Altering the FINAL Property of a Type
    • 9.12 Parallel Queries with Oracle Objects
    • 9.13 Design Consideration Tips and Techniques
      • 9.13.1 Whether to Evolve a Type or Create a Subtype
      • 9.13.2 How ANYDATA Differs from User-Defined Types
      • 9.13.3 Polymorphic Views: An Alternative to an Object View Hierarchy
      • 9.13.4 The SQLJ Object Type
        • 9.13.4.1 The Intended Use of SQLJ Object Types
        • 9.13.4.2 Actions Performed When Creating a SQLJ Object Type
        • 9.13.4.3 Uses of SQLJ Object Types
        • 9.13.4.4 Uses of Custom Object Types
        • 9.13.4.5 Differences Between SQLJ and Custom Object Types Through JDBC
      • 9.13.5 Miscellaneous Design Tips
        • 9.13.5.1 Column Substitutability and the Number of Attributes in a Hierarchy
        • 9.13.5.2 Circular Dependencies Among Types
  • Glossary
  • Index

Search

Print

Download

PDF for offline and print

  • Previous
  • Next
  1. Object-Relational Developer's Guide
  2. Introduction to Oracle Objects

1 Introduction to Oracle Objects

There are advantages and key features to the Oracle object-relational model.

Topics:

  • About Oracle Objects

  • Advantages of Objects

  • Key Features of the Object-Relational Model

  • Previous
  • Next
Back to main content
  • About Oracle
  • Contact Us
  • Legal Notices
  • Terms of Use
  • Your Privacy Rights
  • Cookie Preferences
Copyright © 1996, 2019, Oracle and/or its affiliates. All rights reserved.
  • Previous
  • Next
Video