Skip Headers
Oracle® Database Data Cartridge Developer's Guide
11g Release 1 (11.1)
Part Number B28425-03
Home
Book List
Index
Master Index
Contact Us
Next
View PDF
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?
New Features in Oracle 11
g
Release 1 (11.1)
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
Using 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
Using the 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
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
Domain Indexes
Domain Index Operations
Creating a Domain Index
Altering a Domain Index
Truncating a Domain Index
Dropping a Domain Index
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
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
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
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
Creating Local Domain Indexes
Maintaining Local Domain Indexes with INSERT, DELETE, and UPDATE
Querying Local Domain Indexes
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
Commenting Operators
Invoking Operators
Operators and Indextypes
Operators in the WHERE Clause
Operator Predicates
Operator Resolution
Index Scan Setup
Execution Model for Index Scan Methods
Using Operators Outside the WHERE Clause
Creating Index-based Functional Implementations
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
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
Using User-Defined Statistics, Selectivity, and Cost
User-Defined Statistics
Column Statistics
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 Statistics Are Affected by DDL Operations
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 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
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
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
Creating Domain Indexes in Parallel
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 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
Introducing the PSBTREE Example
Designing of the Indextype
Implementing Operators
Create Functional Implementations
Create Operators
Implementing the ODCIIndex Interfaces
Defining an Implementation Type for PSBTREE
Creating the Implementation Type Body
Defining PL/SQL Routines in the Implementation Body
Registering the C Implementation of the ODCIIndex
XXX
() Methods
Defining Additional Structures in C Implementation
Defining C Methods in the Implementation Body
Implementing the Indextype
Using PSBTREE
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
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
The 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