Contents

List of Figures

List of Tables

Title and Copyright Inf ormation

Send Us Your Comments

Preface

Audience

Organization

Related Documenta tion

Conventions

Documentation Accessibility

What's New in Data Cartridges?

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

Oracle9i New Features in Data Cartridges

Part I Introduction

1 What Is a Data Cartridge?

Wh at Are Data Cartridges?

Why Build Data Cartridges?

Data Cartridge Domains

Extending the Server: Services and Interfaces

Extensibility Services

Extensible Type System

User-Defined Types

Collection Types

Reference Types (REF)

Large Objects

Extensible Server Execution Environment

< dd>Extensible Indexing

Extensible Optimizer

Extensibility Interfaces

DBMS I nterfaces

Cartridge Basic Service Interfaces

Data Cartridge Interfaces

2 Roadmap to Building a Data Cartridge

Data C artridge Development Process

Cartridge Installation and Use

Requirements and Guidelines for Data Cartridge Constituents

Cartridge Schemas

Cartridge Globals

Cartridge Error Message Names or Error Codes

Cartridge Installation Directory

Cartridge Files

Shared Library Names for Exte rnal Procedures

Data Cartridge Deployment Checklist

Data Cartridge Naming Conventions

Need f or Naming Conventions

Unique Name Format

Cartridge Registration

Cartridge Directory Structure and Standards< /dd>

Cartridge Upgrades

Import and Export of C artridge Objects

Cartridge Versioning

Internal Versioning

External Versioning

Cartridge Internationalization

Cartridge Administration

Administering Cartridge Access

I nvoker'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 P L/SQL

Methods

I mplementing Methods

Invoking Methods

Referencing Attributes in a Method

PL/SQL Packages

Pragma RESTRICT_REFERENCES

Privileges Required to Create Proc edures 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 Librari es

Registering an External Procedure

Ho w 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

< dd>

Restrictions on Callbacks

Com mon Potential Errors

Calls to External Functions

RPC Time Out

Debugging External Procedures

Using Package DEBUG_EXTPROC

Debugging C Co de in DLLs on Windows NT Systems

Guidelines for Using External Procedures wi th 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 Funct ions

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 Ext ensible Indexing

Overview of Extensible Indexing

Purpose of Indexes

Purpose of Extensible Indexing< /dd>

When to Use Extensible Indexing

Index Struc tures

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 Inter face

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 Me thod Execution

Creating, Dropping, and Commenting Indextypes

< dl>

Creating Indextypes

Dropping Indextypes< /dd>

Commenting Indextypes

Domain Inde xes

Domain Index Operations

D omain 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 Ind exes 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 Loc al Domain Indexes

Table Operations That Affect Indexes

ODCIIndex Interfaces for Partitioning Domain Indexes

Domain Indexes an d SQL*Loader

9 Defining Operators

User-Defined Operators

< dd>Operator Bindings

Operator Privileges

Creating Operators

Dropping Operators

Altering Operators

Commenting Operators

Invoking Operators

Operators and Indextypes< /dd>

Operators in the WHERE Clause

Opera tors Outside the WHERE Clause

Operators that Return Ancillary Data

10 Using Extensible Op timizer

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

User-Defined Statistics Functions

User-Defined Selectivity Functions

User-Defined Cost Functions for Functions

User- Defined Cost Functions for Domain Indexes

Using User-Defined Statistics, Sel ectivity, and Cost

User-Defined Statistics

Column Statistics

Domain Index Statistics

< dd>User-Defined Selectivity

User-Defined O perators

Standalone Functions

Package F unctions

Type Methods

Default Selectivi ty

User-Defined Cost

User-defined Operators

Standalone Functions

Package Functions

Type Methods

D efault Cost

Declaring a NULL Association for an Index or Column

How Statistics Are Affected by DDL Operations

Predicate Ordering

Dependency Model

Restrictions and Suggestions

Parallel Query

Distributed Execution

Performance

11 Using Cartridge Services< /a>

Introduction to Cartridge Services

Cartridge Handle

Client Side Usage

Cartridge Side Usage

Service Calls

Error Handling

Memory Services

Maintaining Context

Durations

Globalization Support

Globalization Su pport Language Information Retrieval

String Manipulation

Parameter Manager Interface

Input Processi ng

Parameter Manager Behavior Flag

Key Registration

Parameter Storage and Retrieval

Parameter Manager Context

File I/O

String Formatting

12 User-Defined Aggregate Functions

The ODC IAggregate Interface: Overview

Creating a User-Defined Aggregate

Using a User-Defined Aggregate

Parallel Evaluation of User-D efined Aggregates

Handling Large Aggregation Contexts

External Context and Parallel Aggregation

External Context an d User-Defined Analytic Functions

Summary of Steps to Support External Conte xt

User-Defined Aggregates and Materialized Views

User-Defined Aggregates and Analytic Functions

Reusing the Aggregation Context for Analytic Functions

Example: Creating a nd Using a User-Defined Aggregate

13 Using Pipelined and Parallel Table Functions

Over view of Table Functions

Table Function Concepts

Table Functions

Pipelined Table Functions

Pipelined Table Functions with REF CURSOR Arguments

Er rors and Restrictions

Parallel Execution of Table Functions

Pipelined Table Functions

Implement ation Choices for Pipelined Table Functions

Declarations of Pipelined Table Functions< /a>

Implementing the Native PL/SQL Approach

Pipelining Between PL/SQL Table Functions

Combining PIPE ROW with AUTONOMOUS_TRANSACT ION

Implementing the Interface Approach

Scan Context

Start Routine

F etch Routine

Close Routine

Example: Pip elined Table Functions: Interface Approach

Describe Method

Prepare Method

Querying Table Functions

Multiple Calls to Table Functions

PL/SQL< /dd>

Performing DML Operations Inside Table Functions

Performing DML Operations on Table Functions

Handling Exceptions in Ta ble 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

Parallelizi ng 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 Str uctures in LOBs

External Index Structures

Multi-Row Fetch

Designing Operators

Designing for the Extensible Optimizer

Weighing Cost and Selectivi ty

Cost for functions

Selecti vity for Functions

Statistics for Tables

Statistics for Indexes

Designing for Maintenance

Making Your Cartridge Installable

Designing for Portab ility

Part III Scenarios and Examples

15 Power De mand Cartridge Example

Feature Requirements

Modeling the Application

Sample Queries

Queries and Extensible Indexing

Querie s Not Benefiting from Extensible Indexing

Queries Benefiting from Extensible Indexing< /a>

Creating the Domain Index

Creating the Schema to Own the Index

Creating the Object Type (PowerDemand_Typ)

Defining the Object Type Methods

Creat ing the Functions and Operators

Creating the Indextype Implementation Methods

Type Definition

ODCIGetInterfaces Met hod

ODCIIndexCreate Method

ODCIIndexDro p Method

ODCIIndexStart Method (for Specific Queries)

ODCIIndexStart Method (for Any Queries)

ODCIIndexFetch Method

ODCIIndexClose Method

ODCIIndexInsert Method

ODCIIndexDelete Method

ODCIIndexUpdate Method

ODCIIndexGetMetadata Method

< dd>Creating the Indextype

Defining a Type and Methods for Extensible Optimizing

Creating the Statistics Table (PowerCa rtUserStats)

Creating the Extensible Optimizer Methods

Type Definition

ODCIGetInterfaces Method

ODCIStatsCollect Method (for PowerDemand_Typ columns)

ODCIStatsDelete Method (for PowerDemand_Typ columns)

ODC IStatsCollect Method (for power_idxtype Domain Indexes)

ODCIStatsDele te Method (for power_idxtype Domain Indexes)

ODCIStatsSelectivity Met hod (for Specific Queries)

ODCIStatsSelectivity Method (for A ny Queries)

ODCIStatsIndexCost Method (for Specific Queries)

ODCIStatsIndexCost Method (for Any Queries)

ODCIStatsFunctionCost Method

Associating the Extensib le Optimizer Methods with Database Objects

Analyzing the Database Objects

Testing the Domain Index

Creati ng and Populating the Power Demand Table

Querying Without the Index

Creating the Index

Querying with the Index

< /dd>

16 PSBTREE: Ext ensible 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

< /dd>

Implementing the Index Routines

The C Cod e

Common Error Processing Routine

Implementation Of The ODCIIndexInsert Routine

Implementation of the ODCIIndexD elete 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 exampl es

Explain Plan Output

17 Pipelined Table Functions: Interface Approach E xample

Pipelined Table Functions Example: C Implementation

SQL Declarations for C Implementation

C Implementation of the ODCITable Methods

Pipelined Table Functions Ex ample: 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

ContextManager

CountException()

CountException(String)< /a>

InvalidKeyException()

InvalidKeyExc eption(String)

19 Extensibility Constants, Types, and Mappings

System Def ined Constants

System-Defined Types

ODCIArgDesc

ODCIArgDescList

ODCIRidList

ODCIColInfo

ODCIColInfoLi st

ODCICost

ODCIEnv

ODCIFuncInfo

ODCIIndexInfo

ODCIPredInfo

ODCIIndexCtx

ODCIObject

ODCIObjectList

O DCIPartInfo

ODCIQueryInfo

ODCIStatsOp tions

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

EXPL AIN PLAN

INDEX Hint

ORDERED_PREDI CATES Hint

Example

User -Defined ODCIStats Functions

ODCIGetInterfaces

ODCIStatsCollect

ODCIStatsCollect

ODCIStatsDelete

ODCIStatsDelete (Index)

ODCIStatsFunctionCost

ODCIStatsIndexCost

< dd>ODCIStatsSelectivity

ODCIStatsTableFunction

22 User-Defined Aggregates Interface

Aggregate Routines

ODCIAggregateInitialize

ODCIAggregateIterate

ODCIAggregateMerge

ODCIAggregateTerminate

ODCIAggregateDelete

ODCIAggregateWrapCont ext

23 Pipelined and Parallel Table Functions

Routines for Pipelined and Parallel Table Functions in C

ODCITableStart

ODCITableFetch

ODCITableClose

ODCITablePrepare

ODCITableDescribe

Index