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