Oracle9i Data Cartridge Developer's Guide

504 Pages·2001·2.924 MB·English

by  Gietz B.

Oracle9i
Data Cartridge Developer’s Guide
Release 1 (9.0.1)
June 2001
Part No. A88896-01
Oracle9i Data Cartridge Developer’s Guide, Release 1 (9.0.1)
Part No. A88896-01
Copyright © 1996, 2001, Oracle Corporation. All rights reserved.
Primary Author: Bill Gietz
Contributors: A. Yoaz, S. Sundara, N. Agarwal, S. Agrawal, H. Baer, D. Das, R. Govindarajan, R.
Kasamsetty, R. Murthy
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent, and other intellectual and industrial property
laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited.
The information contained in this document is subject to change without notice. If you find any problems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark, and Pro*Ada, Pro*COBOL, Pro*FORTRAN, SQL*Loader, SQL*Net,
SQL*Plus, Designer/2000, Developer/2000, Oracle Net, Oracle Call Interface, Oracle7, Oracle8, Oracle8i,
Oracle9i, Oracle Forms, Real Application Clusters, PL/SQL, Pro*C, Pro*C/C++ and Trusted Oracle are
trademarks or registered trademarks of Oracle Corporation. Other names may be trademarks of their
respective owners.
Contents
Send Us Your Comments................................................................................................................. xix
Preface.......................................................................................................................................................... xxi
New Features......................................................................................................................................... xxvii
Part I Introduction
1 What Is a Data Cartridge?
What Are Data Cartridges?............................................................................................................... 1-2
Why Build Data Cartridges?............................................................................................................. 1-3
Data Cartridge Domains.............................................................................................................. 1-4
Extending the Server—Services and Interfaces............................................................................ 1-6
Extensibility Services......................................................................................................................... 1-7
Extensible Type System............................................................................................................... 1-7
Object Types........................................................................................................................... 1-8
Collection Types.................................................................................................................... 1-8
Relationship Types (REF)..................................................................................................... 1-9
Large Objects.......................................................................................................................... 1-9
Extensible Server Execution Environment.............................................................................. 1-10
Extensible Indexing.................................................................................................................... 1-11
Extensible Optimizer.................................................................................................................. 1-13
Extensibility Interfaces.................................................................................................................... 1-14
DBMS Interfaces......................................................................................................................... 1-14
iii
Cartridge Basic Service Interfaces............................................................................................ 1-14
Data Cartridge Interfaces........................................................................................................... 1-14
Cartridges as Software Components............................................................................................. 1-15
The Structure of a Data Cartridge............................................................................................ 1-15
Object Type Specification.......................................................................................................... 1-16
Object Type Body Code............................................................................................................. 1-16
External Library Linkage Specification................................................................................... 1-16
External Library Code................................................................................................................ 1-17
Installing a Data Cartridge........................................................................................................ 1-17
2 Roadmap to Building a Data Cartridge
Development Process......................................................................................................................... 2-2
Installation and Use............................................................................................................................ 2-4
Requirements and Guidelines for Data Cartridge Constituents............................................... 2-5
Schema............................................................................................................................................ 2-5
Globals............................................................................................................................................ 2-5
Error Message Names or Error Codes....................................................................................... 2-6
Cartridge Installation Directory................................................................................................. 2-6
Files................................................................................................................................................. 2-6
Shared Library Names for External Procedures...................................................................... 2-7
Deployment Checklist....................................................................................................................... 2-7
Naming Conventions................................................................................................................... 2-8
Need for Naming Conventions........................................................................................... 2-8
Unique Name Format........................................................................................................... 2-9
Cartridge Registration................................................................................................................ 2-10
Directory Structure and Standards.......................................................................................... 2-10
Cartridge Upgrades.................................................................................................................... 2-11
Import and Export...................................................................................................................... 2-11
Cartridge Versioning.................................................................................................................. 2-11
Internal Versioning.............................................................................................................. 2-11
External Versioning............................................................................................................. 2-12
Internationalization.................................................................................................................... 2-12
External Access.................................................................................................................... 2-12
Internal Access..................................................................................................................... 2-12
Invoker’s Rights................................................................................................................... 2-13
iv
Test and Debug Services.................................................................................................... 2-13
Administration............................................................................................................................ 2-13
Configuration....................................................................................................................... 2-13
Suggested Development Approach......................................................................................... 2-14
Part II Building Data Cartridges
3 Defining Object Types
Objects and Object Types................................................................................................................. 3-2
Assigning an OID to an Object Type.............................................................................................. 3-3
Constructor Methods.......................................................................................................................... 3-5
Object Comparison............................................................................................................................. 3-5
4 Methods: Using C/C++ and Java
External Procedures............................................................................................................................ 4-2
Using Shared Libraries...................................................................................................................... 4-2
Registering an External Procedure.................................................................................................. 4-3
How PL/SQL Calls an External Procedure..................................................................................... 4-4
Configuration Files for External Procedures................................................................................. 4-6
Passing Parameters to an External Procedure.......................................................................... 4-7
Specifying Datatypes.................................................................................................................... 4-7
Using the Parameters Clause...................................................................................................... 4-9
Using the WITH CONTEXT Clause......................................................................................... 4-10
OCIExtProcGetEnv........................................................................................................................... 4-10
Doing Callbacks................................................................................................................................ 4-11
Restrictions on Callbacks........................................................................................................... 4-11
OCI Access Functions for External Procedures........................................................................... 4-12
OCIExtProcAllocCallMemory.................................................................................................. 4-13
OCIExtProcRaiseExcp................................................................................................................ 4-13
OCIExtProcRaiseExcpWithMsg............................................................................................... 4-13
Common Potential Errors................................................................................................................ 4-14
Calls to External Functions........................................................................................................ 4-14
RPC Time Out............................................................................................................................. 4-14
Debugging External Procedures.................................................................................................... 4-14
v
Using Package DEBUG_EXTPROC......................................................................................... 4-15
Debugging C Code in DLLs on Windows NT Systems........................................................ 4-15
Guidelines for Using External Procedures with Data Cartridges........................................... 4-15
Java Methods..................................................................................................................................... 4-16
5 Methods: Using PL/SQL
Methods................................................................................................................................................ 5-2
Implementing Methods............................................................................................................... 5-2
Invoking Methods......................................................................................................................... 5-4
Referencing Attributes in a Method........................................................................................... 5-5
PL/SQL Packages................................................................................................................................ 5-5
Pragma RESTRICT_REFERENCES................................................................................................. 5-6
Privileges Required to Create Procedures and Functions .......................................................... 5-7
Debugging PL/SQL Code.................................................................................................................. 5-8
Notes for C and C++ Programmers........................................................................................... 5-9
Common Potential Errors............................................................................................................ 5-9
Signature Mismatches........................................................................................................... 5-9
RPC Time Out...................................................................................................................... 5-10
Package Corruption............................................................................................................. 5-10
6 Working with Multimedia Datatypes
Overview.............................................................................................................................................. 6-2
DDL for LOBs...................................................................................................................................... 6-2
LOB Locators........................................................................................................................................ 6-3
EMPTY_BLOB and EMPTY_CLOB Functions.............................................................................. 6-4
Using the OCI to Manipulate LOBs................................................................................................ 6-6
Using DBMS_LOB to Manipulate LOBs...................................................................................... 6-10
LOBs in External Procedures.......................................................................................................... 6-11
LOBs and Triggers............................................................................................................................ 6-12
Using Open/Close as Bracketing Operations for Efficient Performance............................... 6-12
Errors and Restrictions Regarding Open/Close Operations............................................... 6-13
7 Building Domain Indexes
Introduction to Extensible Indexing............................................................................................... 7-2
vi
What is Indexing?......................................................................................................................... 7-2
Index Structures............................................................................................................................ 7-3
The Relationship between Logical and Physical Structures........................................... 7-3
The Need for Index Structures that Encompass Unstructured Data............................. 7-3
Kinds of Indexes........................................................................................................................... 7-4
B-tree....................................................................................................................................... 7-4
Hash......................................................................................................................................... 7-5
k-d tree.................................................................................................................................... 7-6
Point Quadtree....................................................................................................................... 7-8
Why is Extensible Indexing Necessary?.................................................................................... 7-9
The Extensible Indexing API.......................................................................................................... 7-10
Concepts: Extensible Indexing.................................................................................................. 7-13
Overview.............................................................................................................................. 7-13
Example: A Text Indextype................................................................................................ 7-15
Indextypes................................................................................................................................... 7-17
Creating Indextypes............................................................................................................ 7-17
Dropping Indextypes.......................................................................................................... 7-18
Commenting on Indextypes.............................................................................................. 7-18
ODCI Index Interface................................................................................................................. 7-18
Index Definition Methods.................................................................................................. 7-19
Index Maintenance Methods............................................................................................. 7-20
Index Scan Methods............................................................................................................ 7-20
Index Metadata Method..................................................................................................... 7-22
Transaction Semantics during Index Method Execution.............................................. 7-22
Transaction Semantics for Index Definition Routines................................................... 7-23
Consistency Semantics during Index Method Execution.............................................. 7-23
Privileges During Index Method Execution.................................................................... 7-23
Domain Indexes.......................................................................................................................... 7-24
Domain Index Operations.................................................................................................. 7-24
Domain Indexes on Index-Organized Tables.................................................................. 7-25
Domain Index Metadata..................................................................................................... 7-27
Export/Import of Domain Indexes................................................................................... 7-27
Moving Domain Indexes Using Transportable Tablespaces........................................ 7-28
Operators..................................................................................................................................... 7-28
Operator Bindings............................................................................................................... 7-28
vii
Creating operators............................................................................................................... 7-29
Invoking Operators............................................................................................................. 7-30
Operator Privileges............................................................................................................. 7-31
Operators and Indextypes......................................................................................................... 7-31
Operators in the WHERE Clause...................................................................................... 7-32
Operators Outside the WHERE Clause............................................................................ 7-35
Ancillary Data...................................................................................................................... 7-37
Object Dependencies, Drop Semantics, and Validation........................................................ 7-40
Dependencies....................................................................................................................... 7-40
Drop Semantics.................................................................................................................... 7-41
Object Validation................................................................................................................. 7-41
Privileges...................................................................................................................................... 7-41
Partitioned Domain Indexes........................................................................................................... 7-42
Dropping a Local Domain Index.............................................................................................. 7-44
Altering a Local Domain Index................................................................................................. 7-44
Summary of Index States........................................................................................................... 7-44
Table Operations That Affect Indexes..................................................................................... 7-45
ODCIIndex Interfaces for Partitioning Domain Indexes...................................................... 7-46
Domain Indexes and SQL*Loader............................................................................................ 7-47
8 Query Optimization
Overview.............................................................................................................................................. 8-2
Statistics.......................................................................................................................................... 8-4
User-Defined Statistics.......................................................................................................... 8-4
User-Defined Statistics for Partitioned Objects................................................................. 8-5
Selectivity....................................................................................................................................... 8-5
User-Defined Selectivity....................................................................................................... 8-5
Cost................................................................................................................................................. 8-6
User-Defined Cost................................................................................................................. 8-7
Defining Statistics, Selectivity, and Cost Functions.................................................................... 8-8
User-Defined Statistics Functions............................................................................................. 8-10
User-Defined Selectivity Functions.......................................................................................... 8-11
User-Defined Cost Functions for Functions............................................................................ 8-13
User-Defined Cost Functions for Domain Indexes................................................................ 8-14
Using User-Defined Statistics, Selectivity, and Cost................................................................. 8-16
viii
User-Defined Statistics............................................................................................................... 8-16
Column Statistics................................................................................................................. 8-16
Domain Index Statistics...................................................................................................... 8-18
User-Defined Selectivity............................................................................................................ 8-19
User-defined Operators...................................................................................................... 8-19
Stand-Alone Functions....................................................................................................... 8-19
Package Functions............................................................................................................... 8-19
Type Methods...................................................................................................................... 8-20
Default Selectivity............................................................................................................... 8-20
User-Defined Cost...................................................................................................................... 8-21
User-defined Operators...................................................................................................... 8-21
Stand-Alone Functions....................................................................................................... 8-21
Package Functions............................................................................................................... 8-21
Type Methods...................................................................................................................... 8-22
Default Cost.......................................................................................................................... 8-22
Declaring a NULL Association for an Index or Column...................................................... 8-23
How Statistics Are Affected by DDL Operations.................................................................. 8-23
Predicate Ordering............................................................................................................................ 8-24
Dependency Model.......................................................................................................................... 8-25
Restrictions and Suggestions......................................................................................................... 8-25
Parallel Query............................................................................................................................. 8-25
Distributed Execution................................................................................................................ 8-26
Performance................................................................................................................................. 8-26
9 Using Cartridge Services
Cartridge Services — Introduction.................................................................................................. 9-2
Cartridge Handle................................................................................................................................ 9-3
Client Side Usage.......................................................................................................................... 9-3
Cartridge Side Usage................................................................................................................... 9-3
Service Calls................................................................................................................................... 9-3
Error Handling.............................................................................................................................. 9-4
Memory Services................................................................................................................................. 9-4
Maintaining Context.......................................................................................................................... 9-5
Durations....................................................................................................................................... 9-6
Globalization Support....................................................................................................................... 9-6
ix
Globalization Support Language Information Retrieval........................................................ 9-7
String Manipulation..................................................................................................................... 9-7
Parameter Manager Interface............................................................................................................ 9-7
Input Processing............................................................................................................................ 9-8
Parameter Manager Behavior Flag............................................................................................. 9-9
Key Registration............................................................................................................................ 9-9
Parameter Storage and Retrieval................................................................................................ 9-9
Parameter Manager Context..................................................................................................... 9-10
File I/O................................................................................................................................................. 9-10
String Formatting.............................................................................................................................. 9-10
Part III Advanced Topics
10 Design Considerations
Designing the Types......................................................................................................................... 10-2
Structured and Unstructured Data.......................................................................................... 10-2
Using Nested Tables or VARRAYs.......................................................................................... 10-2
Nested Tables....................................................................................................................... 10-2
VARRAYs............................................................................................................................. 10-3
Choosing a Language in Which to Write Methods................................................................ 10-3
Invokers Rights — Why, When, How..................................................................................... 10-4
Callouts............................................................................................................................................... 10-4
When to Callout.......................................................................................................................... 10-4
When to Callback........................................................................................................................ 10-5
Callouts and LOB........................................................................................................................ 10-5
Saving and Passing State........................................................................................................... 10-5
Designing Indexes............................................................................................................................ 10-6
Influencing Index Performance......................................................................................... 10-6
Influencing Index Performance......................................................................................... 10-6
When to Use IOTs....................................................................................................................... 10-6
Can Index Structures Be Stored in LOBs................................................................................. 10-6
External Index Structures.......................................................................................................... 10-7
Multi-Row Fetch......................................................................................................................... 10-7
Designing Operators........................................................................................................................ 10-8
Functional and Index Implementations.................................................................................. 10-8
x