1/35

Contents

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Conventions

Changes in This Release for Oracle Database Data Cartridge Developer's Guide

• Changes in Oracle Database 12c Release 2 (12.2.01)

Part I Introduction

1 Introduction to Data Cartridges

• Overview of Data Cartridges
• Uses of Data Cartridges
  • Data Cartridge Domains
• Extending the Server: Services and Interfaces
  • Extensibility Services
    • Extensible Type System
      • User-Defined Types
      • Collection Types
      • Reference Types
      • Large Objects
    • Extensible Server Execution Environment
    • Extensible Indexing
    • Extensible Optimizer
  • Extensibility Interfaces

2 Roadmap to Building a Data Cartridge

• Data Cartridge Development Process
  • Implement the Project
• Installing and Using Data Cartridges
• Requirements and Guidelines for Data Cartridge Components
  • Cartridge Schemas
  • Cartridge Globals
  • Cartridge Error Message Names or Error Codes
  • Cartridge Installation Directory
  • Cartridge Files
  • Shared Library Names for External Procedures
• Data Cartridge Deployment
  • Data Cartridge Naming Conventions
    • Need for Naming Conventions in Data Cartridges
    • Unique Name Format
    • Data Cartridge Naming Conventions
  • Cartridge Registration
  • Cartridge Directory Structure and Standards
  • Cartridge Upgrades
  • Import and Export of Cartridge Objects
  • Cartridge Versioning
  • Cartridge Internationalization
  • Cartridge Administration
  • Data Cartridge Development Approach
    • Creating a Data Cartridge Plan
    • Developing Data Cartridges

Part II Building Data Cartridges

3 Defining Object Types for Data Cartridges

• Objects and Object Types for Data Cartridges
  • Defining a DataStream Data Type
  • Defining the Type Body
• Assigning an Object Identifier to an Object Type in Data Cartridges
  • Specifying an ODI for an Object Type in Data Cartridges
  • Assigning and Using OIDs in Data Cartridges
• Constructor Methods in Data Cartridges
  • Creating a Type
  • Instantiating a Type Object
• Object Comparison in Data Cartridges
  • Implementing a Member Function
  • Implementing Functions for Types Without a Simple Id Attribute

4 Implementing Data Cartridges in PL/SQL

• Methods
  • Implementing Methods
    • Defining an Object Type
    • Defining a “Greatest Common Divisor” Function
    • Implementing Methods for an Object Type
  • Invoking Methods
    • General Syntax for Invoking Methods
    • SQL Syntax for Invoking Methods
    • PL/SQL Syntax for Invoking Methods
    • Using the SELF Build-In Parameter
  • Referencing Attributes in a Method
    • Setting Variable Values
• PL/SQL Packages
  • Creating a Package Specification
• Pragma RESTRICT_REFERENCES
  • Asserting the Purity Level of a Type
  • Asserting the Purity Level of a Package
  • Asserting a Default Purity Level for Methods and Procedures
• Privileges Required to Create Procedures and Functions
• Debugging PL/SQL Code
  • Notes for C and C++ Developers of Data Cartridges
  • Common Potential Errors
    • Signature Mismatches
    • RPC Time Out
    • Package Corruption

5 Implementing Data Cartridges in C, C++, and Java

• Shared Libraries
  • Using Shared Libraries
    • Creating an Alias Library
    • Specifying the Location of the Library
    • Creating an Alias Library through Directory Objects
• External Procedures
  • Registering an External Procedure
    • Defining the Body of a Package
• How PL/SQL Calls an External Procedure
• Configuring Files for External Procedures
  • Updating the Listener Configuration File
  • Directing Network to Refer to External Procedures
  • Passing Parameters to an External Procedure
  • Specifying Data Types
    • Conversion to External Datatypes
    • Conversion from External Datatypes
  • Using the Parameters Clause
  • Using the WITH CONTEXT Clause
• Using Callbacks
  • Restrictions on Callbacks
• Common Potential Errors
  • Calls to External Functions
  • RPC Time Out
• Debugging External Procedures
  • Using Package DEBUG_EXTPROC
  • Debugging C Code in DLLs on Windows NT Systems
• Guidelines for Using External Procedures with Data Cartridges
• Java Methods

6 Working with Multimedia Data Types

• Overview of Cartridges and Multimedia Data Types
• Using DDL for LOBs
  • Creating a LOB Attribute of a Type
  • Creating a LOB Object Table
  • Creating LOB Columns
• LOB Locators
  • Selecting a LOBs and Assigning it to a Local Variable
  • Manipulating LOBs
• Emptying LOBs
  • Using EMPTY_BLOB() in SQL
  • Using EMPTY_CLOB() in PL/SQL
• Using the OCI to Manipulate LOBs
  • OCI Functions for Manipulating LOBs
  • Comparing OCI and PL/SQL Interfaces
  • Selecting a Stored LOB into a Locator
• Using DBMS_LOB Package to Manipulate LOBs
  • DBMS_LOB Package Routines
  • Trimming a CLOB
• LOBs in External Procedures
  • Defining an External Procedure (PL/SQL)
• LOBs and Triggers
• Using Open/Close as Bracketing Operations for Efficient Performance
  • Errors and Restrictions Regarding Open/Close Operations
    • Working with Open() and Close() Code Blocks

7 Using Extensible Indexing

• Overview of Extensible Indexing
  • Purpose of Indexes
  • Purpose of Extensible Indexing
  • When to Use Extensible Indexing
  • Index Structures
    • B-tree
    • Hash
    • k-d tree
    • Point Quadtree
• Extensible Indexing Framework
• Using the Text Indextype
  • Defining the Indextype
    • Non-Index-Based Functional Implementations
    • Index-Based Functional Implementations
  • Using the Indextype
    • Declaring a New Table
    • Building a Text Domain Index for the Table
    • Querying a Table Using a Contains() Operator

8 Building Domain Indexes

• Overview of Indextypes and Domain Indexes
• ODCIIndex Interface
  • Index Definition Methods
    • ODCIIndexCreate()
    • ODCIIndexAlter()
    • ODCIIndexDrop()
  • Index Maintenance Methods
    • ODCIIndexInsert()
    • ODCIIndexDelete()
    • ODCIIndexUpdate()
  • Index Scan Methods
    • ODCIIndexStart()
    • ODCIIndexFetch()
    • ODCIIndexClose()
  • Index Metadata Method
  • Transaction Semantics During Index Method Execution
  • Transaction Semantics for Index Definition Routines
  • Consistency Semantics during Index Method Execution
  • Privileges During Index Method Execution
• Creating, Dropping, and Commenting Indextypes
  • Creating Indextypes
  • Dropping Indextypes
  • Commenting Indextypes
    • INDEXTYPE Comments
• Domain Indexes
  • Domain Index Operations
    • Creating a Domain Index
    • Changing a Domain Index
    • Renaming a Domain Index
    • Rebuilding a Domain Index
    • Truncating a Domain Index
    • Dropping a Domain Index
  • Domain Indexes on Index-Organized Tables
    • About Rowid Storage in a UROWID Column
    • Determining the Size of a UROWID Column
    • DML on Index Storage Tables
    • Start, Fetch, and Close Operations on Index Storage Tables
    • Indexes on Non-Unique Columns
  • Domain Index Metadata
  • Moving Domain Indexes Using Export/Import
  • Moving Domain Indexes Using Transportable Tablespaces
  • Domain Index Views
• Object Dependencies, Drop Semantics, and Validation
  • Object Dependencies
  • Object Drop Semantics
  • Object Validation
• Indextype, Domain Index, and Operator Privileges
• Partitioned Domain Indexes
  • Using Local Domain Index Methods
  • About Partitioned Indexes
  • Creating a Local Domain Index
  • Dropping a Local Domain Index
  • Altering a Local Domain Index
  • Summary of Index States
  • DML Operations with Local Domain Indexes
  • Table Operations that Affect Indexes
  • ODCIIndex Interfaces for Partitioning Domain Indexes
  • Using SQL*Loader for Domain Indexes
• Using System Partitioning
  • Advantages of System Partitioned Tables
  • Implementing System Partitioning
    • Creating a System-Partitioned Table
    • Inserting Data into a System-Partitioned Table
    • Deleting and Updating Data in a System-Partitioned Table
  • Supporting Operations with System-Partitioned Tables
  • Running Partition Maintenance Operations
  • Altering Table Exchange Partitions with Indexes
• Using System-Managed Domain Indexes
• Designing System-Managed Domain Indexes
  • Methods for Non-Partitioned Domain Indexes
  • Methods for Local System-Managed Domain Indexes
• Creating Local Domain Indexes
• Maintaining Local Domain Indexes with INSERT, DELETE, and UPDATE
• Querying Local Domain Indexes
• System Managed Domain Index - Supported Schemes
• Restrictions of System-Managed Domain Indexing
• Migrating Non-Partitioned Indexes
• Migrating Local Partitioned Indexes

9 Defining Operators

• User-Defined Operators
  • Operator Bindings
  • Operator Privileges
  • Creating Operators
  • Dropping Operators
  • Altering Operators
    • Necessary Privileges for ALTER OPERATOR
    • Restrictions of ALTER OPERATOR
  • Commenting Operators
  • About Invoking Operators
    • Creating Contains() Operator
    • Using Contains() Operator in a Query
    • Using Contains() Operator Incorrectly
• Operators and Indextypes
  • Operators in the WHERE Clause
    • Using Operator Predicates
    • Resolving Query Results with the Contains() Operator
    • Setting Up an Index Scan
    • Execution Model for Index Scan Methods
    • Filtering Multiple Table Queries with Contains() Operator
    • Invoking Indextrype Routines for the Contains() Operator
  • Using Operators Outside the WHERE Clause
    • Creating Index-based Functional Implementations
    • Implementing the Contains() Operator in Index-Based Functions
    • Binding the Contains() Operator to the Functional Implementation
    • Operator Resolution
    • Operator Execution
  • Operators that Return Ancillary Data
    • Operator Bindings that Compute Ancillary Data
    • Operator Bindings That Model Ancillary Data
    • Operator Resolution
    • Operator Execution

10 Using Extensible Optimizer

• Overview of Query Optimization
  • Statistics
    • User-Defined Statistics
    • User-Defined Statistics for Partitioned Objects
  • Selectivity
    • User-Defined Selectivity
  • Cost
    • User-Defined Cost
• Defining Statistics, Selectivity, and Cost Functions
  • Defining a Statistics Type
  • User-Defined Statistics Functions
  • User-Defined Selectivity Functions
  • User-Defined Cost Functions for Functions
  • User-Defined Cost Functions for Domain Indexes
  • Generating Statistics for System-Managed Domain Indexes
    • Index-Partition Statistics Storage in an Index Table
    • Index-Partition Statistics Storage in a Separate Table
    • Index-Partition Statistics Storage in a Common Table
• Using User-Defined Statistics, Selectivity, and Cost
  • User-Defined Statistics
    • Column Statistics
    • Implementing Domain Index Statistics
  • User-Defined Selectivity
    • User-Defined Operators
    • Standalone Functions
    • Package Functions
    • Type Methods
    • Default Selectivity
  • User-Defined Cost
    • User-Defined Operators
    • Standalone Functions
    • Package Functions
    • Type Methods
    • Default Cost
  • Declaring a NULL Association for an Index or Column
  • How DDL Operations Affect Statistics
• Predicate Ordering
• Dependency Model
• Restrictions and Suggestions
  • Distributed Execution
  • System-Managed Storage Tables and ASSOCIATE STATISTICS
  • Aggregate Object-Level Statistics
  • System-Managed Domain Indexing
  • Performance

11 Using Cartridge Services

• Introduction to Cartridge Services
• Cartridge Handle
  • Client Side Usage
  • Cartridge Side Usage
  • Making Service Calls
  • Handling Errors
• Memory Services
• Maintaining Context
  • Durations
• Globalization Support
  • Globalization Support Language Information Retrieval
  • String Manipulation
• Parameter Manager Interface
  • Input Processing and Support for Special Characters
  • Parameter Manager Behavior Flag
  • Key Registration
  • Parameter Storage and Retrieval
  • Parameter Manager Context
• File I/O
• String Formatting

12 Using User-Defined Aggregate Functions

• Overview of User-Defined Aggregate Functions
  • Using User-Defined Aggregate Functions
• Creating a User-Defined Aggregate
• Using a User-Defined Aggregate
  • Using the SELECT Statement with User-Defined Aggregate Functions
  • Using the HAVING Clause with User-Defined Aggregate Functions
  • Using Query Options with User-Defined Aggregate Functions
• Evaluating User-Defined Aggregates in Parallel
• Handling Large Aggregation Contexts
  • External Context and Parallel Aggregation
    • Using External Memory to Store Aggregate Context
  • User-Defined Aggregates and Analytic Functions
    • Using User-Defined Aggregates and Analytic Functions
  • Reuse of Aggregation Context for Analytic Functions
  • External Context and User-Defined Analytic Functions
• Using Materialized Views with User-Defined Aggregates
• Creating and Using a User-Defined Aggregate Function

13 Using Pipelined and Parallel Table Functions

• Overview of Table Functions
• Table Function Concepts
  • Table Functions
  • Pipelined Table Functions
  • Pipelined Table Functions with REF CURSOR Arguments
  • Parallel Execution of Table Functions
• Pipelined Table Functions
  • Implementation Choices for Pipelined Table Functions
  • Declaring Pipelined Table Functions
  • Implementing the Native PL/SQL Approach
  • Pipelining Between PL/SQL Table Functions
  • Combining PIPE ROW with AUTONOMOUS_TRANSACTION
  • Implementing the Interface Approach
    • Scan Context
    • Start Routine
    • Fetch Routine
    • Close Routine
    • Describing Returned Data Sructures; Describe Method
    • Preparing a Query for Execution; Prepare Method
  • Querying Table Functions
    • Implementing Multiple Calls to Table Functions
    • Using PL/SQL REF CURSOR Variables
  • Performing DML Operations Inside Table Functions
  • Performing DML Operations on Table Functions
  • Handling Exceptions in Table Functions
• Parallel Table Functions
  • Inputting Data with Cursor Variables
    • Using Multiple REF CURSOR Input Variables
    • Explicitly Opening a REF CURSOR for a Query
    • PL/SQL REF CURSOR Arguments to Java and C/C++ Functions
  • Input Data Partitioning
  • Parallel Execution of Leaf-Level Table Functions
• Input Data Streaming for Table Functions
  • Setting up the Input Stream
  • Parallel Execution: Partitioning and Clustering
• Creating Domain Indexes in Parallel
  • Loading Domain Indexes
• Transient and Generic Types

14 Designing Data Cartridges

• Choosing the Programming Language
• Invoker's Rights
• Callouts and LOBs
• Saving and Passing State
• Designing Indexes
  • Domain Index Performance
  • Domain Index Component Names
  • When to Use Index-Organized Tables
  • Storing Index Structures in LOBs
  • External Index Structures
  • Multi-Row Fetch
• Designing Operators
• Designing for the Extensible Optimizer
  • Weighing Cost and Selectivity
  • Cost for functions
    • Selectivity for Functions
    • Statistics for Tables
    • Statistics for Indexes
• Designing for Maintenance
• Enabling Cartridge Installation
• Designing for Portability

Part III Scenarios and Examples

15 Power Demand Cartridge Example

• Feature Requirements
• Modeling the Application
  • Sample Queries
• Queries and Extensible Indexing
  • Queries Not Benefiting from Extensible Indexing
  • Queries Benefiting from Extensible Indexing
• Creating the Domain Index
  • Creating the Schema to Own the Index
  • Creating the Object Types
  • Defining the Object Type Methods
  • Understanding Functions and Operators
    • Creating Functions and Operators
  • Creating the Indextype Implementation Methods
  • Defining theType
    • ODCIGetInterfaces()
    • ODCIIndexCreate()
    • ODCIIndexDrop()
    • ODCIIndexStart(); Specific Queries
    • ODCIIndexStart(); Any Queries
    • ODCIIndexFetch()
    • ODCIIndexClose()
    • ODCIIndexInsert()
    • ODCIIndexDelete()
    • ODCIIndexUpdate()
    • ODCIIndexGetMetadata()
  • Creating the Indextype
• Defining Types and Methods for Extensible Optimizing
  • Creating the Statistics Table, PowerCartUserStats
  • Creating the Extensible Optimizer Methods
    • Creating the Type Definition
    • ODCIGetInterfaces()
    • ODCIStatsCollect() Method for PowerDemand_Typ Columns
    • ODCIStatsDelete() Method for PowerDemand_Typ Columns
    • ODCIStatsCollect() Method for power_idxtype Domain Indexes
    • ODCIStatsDelete() Method for power_idxtype Domain Indexes
    • ODCIStatsSelectivity() Method for Specific Queries
    • ODCIStatsIndexCost() Method for Specific Queries
    • ODCIStatsIndexCost() Method for Any Queries
    • ODCIStatsFunctionCost() Method
  • Associating the Extensible Optimizer Methods with Database Objects
  • Analyzing the Database Objects
• Testing the Domain Index
  • Creating and Populating the Power Demand Table
  • Querying Without the Index
  • Creating the Index
  • Querying with the Index

16 PSBTREE: Extensible Indexing Example

• About the PSBTREE Example
• Design of the Indextype
• Implementing Operators
  • Functional Implementations
    • Implementing the EQUALS Operator
    • Implementing the LESS THAN Operator
    • Implementing the GREATER THAN Operator
  • Operators
• Implementing the ODCIIndex Interfaces
  • Defining an Implementation Type for PSBTREE
  • Creating the Implementation Type Body
  • Defining PL/SQL Routines in the Implementation Body
    • Implementing ODCIGetInterfaces() for PBSTREE in PL/SQL
    • Implementing ODCIIndexCreate() for PBSTREE in PL/SQL
    • Implementing ODCIIndexDrop() for PBSTREE in PL/SQL
    • Implementing ODCIIndexAlter() for PSBTREE in PL/SQL
    • Implementing ODCIIndexUpdPartMetadata() for PSBTREE in PL/SQL
    • Implementing ODCIIndexExchangePartition() for PSBTREE in PL/SQL
  • Registering the C Implementation of the ODCIIndexXXX() Methods
    • Registering the Implementation of ODCIIndexInsert()
    • Registering the Implementation of ODCIIndexDelete()
    • Registering the Implementation of ODCIIndexUpdate()
    • Registering the Implementation of ODCIIndexStart()
    • Registering the Implementation of ODCIIndexFetch()
    • Registering the Implementation of ODCIIndexClose()
  • Defining Additional Structures in C Implementation
  • Defining C Methods in the Implementation Body
    • Implementing a Common Error Processing Routine in C
    • Implementing ODCIIndexInsert() for PSBTREE in C
    • Implementing ODCIIndexDelete() for PSBTREE in C
    • Implementing ODCIIndexUpdate() for PSBTree in C
    • Implementing ODCIIndexStart() for PSBTREE in C
    • Implementing ODCIIndexFetch() for PSBTREE in C
    • Implementing ODCIIndexClose() for PSBTREE in C
  • Implementing the Indextype for PSBTREE
• Using PSBTREE
  • Creating and Populating a Partitioned Table for PSBTREE
  • Creating a PSBTREE Index on a Column
  • Using PSBTREE Operators in a Query

17 Pipelined Table Functions: Interface Approach Example

• Pipelined Table Functions Example: C Implementation
  • Making SQL Declarations for C Implementation
  • Implementation ODCITable Methods in C
• Pipelined Table Functions Example: Java Implementation
  • Making SQL Declarations for Java Implementation
  • Implementing the ODCITable Methods in Java

Part IV Reference

18 Cartridge Services Using C, C++ and Java

• OCI Access Functions for External Procedures
  • OCIExtProcAllocCallMemory
  • OCIExtProcRaiseExcp
  • OCIExtProcRaiseExcpWithMsg
  • OCIExtProcGetEnv
• Installing Java Cartridge Services Files
• Cartridge Services-Maintaining Context
  • ContextManager
  • CountException()
  • CountException(String)
  • InvalidKeyException()
  • InvalidKeyException(String)

19 Extensibility Constants, Types, and Mappings

• System Defined Constants
  • ODCIArgDesc.ArgType System Defined Constants
  • ODCIEnv.CallProperty System Defined Constants
  • ODCIIndexAlter System Defined Constants
  • ODCIIndexInfo.Flags System Defined Constants
  • ODCIIPartInfo.PartOp System Defined Constants
  • ODCIIPredInfo.Flags System Defined Constants
  • ODCIFuncInfo.Flags System Defined Constants
  • ODCIQueryInfo.Flags System Defined Constants
  • ODCIStatsOptions.Flags System Defined Constants
  • ODCIStatsOptions.Options System Defined Constants
  • Return Status System Defined Constants
  • ScnFlg System Defined Constants
• System-Defined Types
  • ODCIArgDesc
  • ODCIArgDescList
  • ODCIRidList
  • ODCIColInfo
  • ODCIColInfoList
  • ODCICost
  • ODCIEnv
  • ODCIFuncInfo
  • ODCIIndexInfo
  • ODCIIndexCtx
  • ODCIObject
  • ODCIObjectList
  • ODCIPartInfo
  • ODCIPartInfoList
  • ODCIPredInfo
  • ODCIQueryInfo
  • ODCIStatsOptions
  • ODCITabFuncStats
  • ODCITabStats
  • ODCIBFileList
  • ODCITabFuncInfo
  • ODCIDateList
  • ODCINumberList
  • ODCIRawList
  • ODCIVarchar2List
  • ODCIFuncCallInfo
• Mappings of Constants and Types
  • Mappings in PL/SQL
  • Mappings in C

20 Extensible Indexing Interface

• Extensible Indexing - System-Defined Interface Routines
  • ODCIGetInterfaces()
  • ODCIIndexAlter()
  • ODCIIndexClose()
  • ODCIIndexCreate()
  • ODCIIndexDelete()
  • ODCIIndexDrop()
  • ODCIIndexExchangePartition()
  • ODCIIndexFetch()
  • ODCIIndexGetMetadata()
  • ODCIIndexInsert()
  • ODCIIndexStart()
  • ODCIIndexUpdate()
  • ODCIIndexUpdPartMetadata()
  • ODCIIndexUtilCleanup()
  • ODCIIndexUtilGetTableNames()

21 Extensible Optimizer Interface

• Extensible Optimizer Interface
  • Using Statistics Functions in an Extensible Optimizer Interface
  • EXPLAIN PLAN
  • INDEX Hint
  • ORDERED_PREDICATES Hint
• User-Defined ODCIStats Functions
  • ODCIGetInterfaces()
  • ODCIStatsCollect()
  • ODCIStatsDelete()
  • ODCIStatsFunctionCost()
  • ODCIStatsExchangePartition()
  • ODCIStatsIndexCost()
  • ODCIStatsSelectivity()
  • ODCIStatsTableFunction()
  • ODCIStatsUpdPartStatistics()

22 User-Defined Aggregate Functions Interface

• User-Defined Aggregate Functions
  • ODCIAggregateDelete()
  • ODCIAggregateInitialize()
  • ODCIAggregateIterate()
  • ODCIAggregateMerge()
  • ODCIAggregateTerminate()
  • ODCIAggregateWrapContext()

23 Pipelined and Parallel Table Functions

• Routines for Pipelined and Parallel Table Functions in C
  • ODCITableClose()
  • ODCITableDescribe()
  • ODCITableFetch()
  • ODCITablePrepare()
  • ODCITableStart()

A User-Managed Local Domain Indexes

• Comparing User-Managed and System-Managed Domain Indexes
• Truncating Domain Indexes
• Creating Indextypes
• Using Domain Indexes for the Indextype
• Partitioning Domain Indexes
• APIs for User-Managed Domain Indexes
  • ODCIIndexTruncate()
  • ODCIIndexMergePartition()
  • ODCIIndexSplitPartition()

Index