Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Conventions

What's New in Data Cartridges?

• Oracle 10g Release 2 (10.2) New Features in Data Cartridges
• Oracle 10g Release 1 (10.1) New Features in Data Cartridges

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
    • Extensible Server Execution Environment
    • Extensible Indexing
    • Extensible Optimizer
  • Extensibility Interfaces
  • • DBMS Interfaces
    • Cartridge Basic Service Interfaces
    • Data Cartridge Interfaces

2 Roadmap to Building a Data Cartridge

• Data Cartridge Development Process
• • Understanding the purpose
  • Understand the users
  • Plan the project
  • Implement the project
  • Test and Installation
• Cartridge Installation and Use
• 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 Checklist
• • Data Cartridge Naming Conventions
  • • Need for Naming Conventions
    • Unique Name Format
  • Cartridge Registration
  • Cartridge Directory Structure and Standards
  • Cartridge Upgrades
  • Import and Export of Cartridge Objects
  • Cartridge Versioning
  • • Internal Versioning
    • External Versioning
  • Cartridge Internationalization
  • Cartridge Administration
  • • Administering Cartridge Access
    • Invoker's Rights
    • Configuration
  • Suggested Development Approach

Part II Building Data Cartridges

3 Defining Object Types

• Objects and Object Types
• Assigning an Object Identifier to an Object Type
• Constructor Methods
• Object Comparison

4 Implementing Data Cartridges in PL/SQL

• Methods
• • Implementing Methods
  • Invoking Methods
  • Referencing Attributes in a Method
• PL/SQL Packages
• Pragma RESTRICT_REFERENCES
• Privileges Required to Create Procedures and Functions
• Debugging PL/SQL Code
• • Notes for C and C++ Programmers
  • Common Potential Errors
  • • Signature Mismatches
    • RPC Time Out
    • Package Corruption

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

• External Procedures
• Using Shared Libraries
• Registering an External Procedure
• How PL/SQL Calls an External Procedure
• Configuration Files for External Procedures
• • Passing Parameters to an External Procedure
  • Specifying Datatypes
  • Using the Parameters Clause
  • Using the WITH CONTEXT Clause
• Doing 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 Datatypes

• Overview of Cartridges and Multimedia Datatypes
• DDL for LOBs
• LOB Locators
• EMPTY_BLOB and EMPTY_CLOB Functions
• Using the OCI to Manipulate LOBs
• Using DBMS_LOB to Manipulate LOBs
• LOBs in External Procedures
• LOBs and Triggers
• Using Open/Close as Bracketing Operations for Efficient Performance
• • Errors and Restrictions Regarding Open/Close Operations

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
• Example: A Text Indextype
• • Defining the Indextype
  • • Non-Index-Based Functional Implementations
    • Index-Based Functional Implementations
  • Using the Indextype

8 Building Domain Indexes

• Overview of Indextypes and Domain Indexes
• ODCIIndex Interface
• • Index Definition Methods
  • Index Maintenance Methods
  • Index Scan Methods
  • 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
• Domain Indexes
• • Domain Index Operations
  • Domain Indexes on Index-Organized Tables
  • • Storing Rowids in 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
• Object Dependencies, Drop Semantics, and Validation
• • Object Dependencies
  • Object Drop Semantics
  • Object Validation
• Indextype, Domain Index, and Operator Privileges
• Partitioned Domain Indexes
• • 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
  • Domain Indexes and SQL*Loader

9 Defining Operators

• User-Defined Operators
• • Operator Bindings
  • Operator Privileges
  • Creating Operators
  • Dropping Operators
  • Altering Operators
  • Commenting Operators
  • Invoking Operators
• Operators and Indextypes
• • Operators in the WHERE Clause
  • Operators Outside the WHERE Clause
  • Operators that Return Ancillary Data

10 Extensible Optimizer Interface

• The Extensible Optimizer Interface
• • EXPLAIN PLAN
  • INDEX Hint
  • ORDERED_PREDICATES Hint
• User-Defined ODCIStats Functions
• • ODCIGetInterfaces()
  • ODCIStatsCollect()
  • ODCIStatsCollect()
  • ODCIStatsDelete()
  • ODCIStatsDelete()
  • ODCIStatsFunctionCost()
  • ODCIStatsIndexCost()
  • ODCIStatsSelectivity()
  • ODCIStatsTableFunction()

11 User-Defined Aggregate Functions

• Overview of User-Defined Aggregate Functions
• Creating a User-Defined Aggregate
• Using a User-Defined Aggregate
• Evaluating User-Defined Aggregates in Parallel
• Handling Large Aggregation Contexts
• • External Context and Parallel Aggregation
  • User-Defined Aggregates and Analytic Functions
  • Reusing the 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; an Example

12 Using Cartridge Services

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

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
  • • Errors and Restrictions
  • Parallel Execution of Table Functions
• Pipelined Table Functions
• • Implementation Choices for Pipelined Table Functions
  • Declarations of 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
    • Example: Pipelined Table Functions: Interface Approach
    • Describe Method
    • Prepare Method
  • Querying Table Functions
  • • Multiple Calls to Table Functions
    • PL/SQL
  • 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
• • Parallel Execution: Partitioning and Clustering
  • Input Data Partitioning
  • Parallel Execution of Leaf-level Table Functions
• Parallelizing Creation of a Domain Index
• Transient and Generic Types

14 Designing Data Cartridges

• Choosing a Language in Which to Write Methods
• 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
• Making Your Cartridge Installable
• 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 Type (PowerDemand_Typ)
  • Defining the Object Type Methods
  • Creating the Functions and Operators
  • Creating the Indextype Implementation Methods
  • • Type Definition
    • ODCIGetInterfaces Method
    • ODCIIndexCreate Method
    • ODCIIndexDrop Method
    • ODCIIndexStart Method (for Specific Queries)
    • ODCIIndexStart Method (for Any Queries)
    • ODCIIndexFetch Method
    • ODCIIndexClose Method
    • ODCIIndexInsert Method
    • ODCIIndexDelete Method
    • ODCIIndexUpdate Method
    • ODCIIndexGetMetadata Method
  • Creating the Indextype
• Defining a Type and Methods for Extensible Optimizing
• • Creating the Statistics Table (PowerCartUserStats)
  • Creating the Extensible Optimizer Methods
  • • Type Definition
    • ODCIGetInterfaces Method
    • 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

• Introduction to the PSBTREE Example
• Design of the Indextype
• Implementing Operators
• • Create Functional Implementations
  • • Functional Implementation of EQ (EQUALS)
    • Functional Implementation of LT (LESS THAN)
    • Functional Implementation of GT (GREATER THAN)
  • Create Operators
  • • Operator EQ
    • Operator LT
    • Operator GT
• Implementing the Index Routines
• The C Code
• • Common Error Processing Routine
  • Implementation Of The ODCIIndexInsert Routine
  • Implementation of the ODCIIndexDelete Routine
  • Implementation of the ODCIIndexUpdate Routine
  • Implementation of the ODCIIndexStart Routine
  • Implementation of the ODCIIndexFetch Routine
  • Implementation of the ODCIIndexClose Routine
• Implementing the Indextype
• Usage Examples
• • Explain Plan Output

17 Pipelined Table Functions: Interface Approach Example

• Pipelined Table Functions Example: C Implementation
• • SQL Declarations for C Implementation
  • C Implementation of the ODCITable Methods
• Pipelined Table Functions Example: Java Implementation
• • SQL Declarations for Java Implementation
  • Java Implementation of the ODCITable Methods

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 with ContextManager Class
• • ContextManager()
  • CountException()
  • CountException(String)
  • InvalidKeyException()
  • InvalidKeyException(String)

19 Extensibility Constants, Types, and Mappings

• System Defined Constants
• System-Defined Types
• • ODCIArgDesc
  • ODCIArgDescList
  • ODCIRidList
  • ODCIColInfo
  • ODCIColInfoList
  • ODCICost
  • ODCIEnv
  • ODCIFuncInfo
  • ODCIIndexInfo
  • ODCIPredInfo
  • ODCIIndexCtx
  • ODCIObject
  • ODCIObjectList
  • ODCIPartInfo
  • ODCIQueryInfo
  • ODCIStatsOptions
  • ODCITabFuncStats
  • ODCITabStats
  • ODCIBFileList
  • ODCITabFuncInfo
  • ODCIDateList
  • ODCINumberList
  • ODCIRawList
  • ODCIVarchar2List
  • ODCIFuncCallInfo
• Mappings of Constants and Types
• • Mappings in PL/SQL
  • Mappings in C
  • Mappings in Java

20 Extensible Indexing Interface

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

21 Extensible Optimizer Interface

• The Extensible Optimizer Interface
• • EXPLAIN PLAN
  • INDEX Hint
  • ORDERED_PREDICATES Hint
• User-Defined ODCIStats Functions
• • ODCIGetInterfaces()
  • ODCIStatsCollect()
  • ODCIStatsCollect()
  • ODCIStatsDelete()
  • ODCIStatsDelete()
  • ODCIStatsFunctionCost()
  • ODCIStatsIndexCost()
  • ODCIStatsSelectivity()
  • ODCIStatsTableFunction()

22 User-Defined Aggregate Functions Interface

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

23 Pipelined and Parallel Table Methods

• Methods for Pipelined and Parallel Tables Functions in C
• • ODCITableStart()
  • ODCITableFetch()
  • ODCITableClose()
  • ODCITablePrepare()
  • ODCITableDescribe()

Index