Table Of Content(cid:226)
Oracle Call Interface
Programmer’s Guide
Release 9.0.1
June 2001
Part No. A89857-01
Oracle Call Interface Programmer’s Guide, Release 9.0.1
Part No. A89857-01
Copyright © 1996, 2001, Oracle Corporation. All rights reserved.
Primary Authors: Jack Melnick
Contributing Author: Eric Belden, Phil Locke
Contributors: G. Arora, P. Balaji, M. Bastawala, S. Chandrasekaran, D. Chatterjee, E. Chen, L.
Chidambaran, S. Gollapudi, G. Govindarajan, W. He, T. Hoang, R.-S. Hwu, M. Joglekar, S. Kaluskar, R.
Kambo, R. Kasamsetty, A. Katti, S. Kotsovolos, V. Krishnamurthy, S. Krishnaswamy, R. Krishnan, S.
Kumar, S. Lari, C. Lei, C. Lim, Meghna Mehta, Sambit Mishra, Shailendra Mishra, P. Mitra, K. Mohan, R.
Murthy, R. Ratnam, P. Reilly, A. Roy, D. Saha, E. Soylemez, A. Surpur, A. Swaminathan, P. Tucker, S.
Vedala, Wei Wang, R. Wessman, D. Wong
Graphic Designer: Valarie Moore
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.
Theinformationcontainedinthisdocumentissubjecttochangewithoutnotice.Ifyoufindanyproblems
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.
Oracleisaregisteredtrademark,andSQL*Forms,SQL*Net,OracleNetSolutions,OracleSecureNetwork
Services, SQL*Plus, Oracle Call Interface, Oracle7, Oracle7 Server, Oracle8, Oracle8i, Oracle9i, Oracle
Forms, PL/SQL, Pro*C, Pro*C/C++, Pro*COBOL, Net8, Oracle Store, and Trusted Oracleare trademarks
or registered trademarks of Oracle Corporation. Other names may be trademarks of their respective
owners.
Oracle Call Interface Programmer’s Guide
Contents
Send Us Your Comments............................................................................................................. xxxiii
Preface....................................................................................................................................................... xxxv
Audience........................................................................................................................................... xxxvi
Organization..................................................................................................................................... xxxvi
Related Documentation........................................................................................................................ xl
Conventions........................................................................................................................................... xli
Documentation Accessibility............................................................................................................. xliii
What’s New in Oracle Call Interface?........................................................................................ xlv
Oracle9i Release 1 (9.0.1) New Features in Oracle Call Interface................................................ xlvi
Oracle9i Release 9.0.0 New Features in Oracle Call Interface..................................................... xlvii
Oracle8i Release 2 (8.1.6) New Features in Oracle Call Interface..................................................... l
Part I OCI Concepts
1 Introduction and Upgrading
Overview of OCI................................................................................................................................. 1-2
Advantages of OCI....................................................................................................................... 1-3
Building an OCI Application...................................................................................................... 1-3
Parts of OCI................................................................................................................................... 1-4
Procedural and Non-Procedural Elements............................................................................... 1-5
Object Support.............................................................................................................................. 1-6
SQL Statements............................................................................................................................. 1-7
iii
Encapsulated Interfaces............................................................................................................. 1-12
Simplified User Authentication and Password Management.............................................. 1-12
Extensions to Improve Application Performance and Scalability....................................... 1-13
OCI Object Support.................................................................................................................... 1-14
Client-Side Object Cache........................................................................................................... 1-14
Associative and Navigational Interfaces................................................................................. 1-14
Runtime Environment for Objects............................................................................................ 1-15
Type Management, Mapping and Manipulation Functions................................................ 1-16
Object Type Translator............................................................................................................... 1-16
OCI Support for Oracle Advanced Queuing.......................................................................... 1-17
XA Library Support.................................................................................................................... 1-17
Simplified Migration of Existing Applications....................................................................... 1-17
Compatibility, Upgrading, and Migration................................................................................... 1-18
Obsolescent OCI Routines......................................................................................................... 1-18
OCI Routines Not Supported.................................................................................................... 1-20
Compatibility............................................................................................................................... 1-21
Upgrading.................................................................................................................................... 1-22
2 OCI Programming Basics
Overview of OCI Programming....................................................................................................... 2-2
OCI Program Structure...................................................................................................................... 2-2
OCI Data Structures........................................................................................................................... 2-5
Handles................................................................................................................................................. 2-5
Allocating and Freeing Handles................................................................................................. 2-6
Environment Handle.................................................................................................................... 2-9
Error Handle.................................................................................................................................. 2-9
Service Context and Associated Handles.................................................................................. 2-9
Statement Handle, Bind Handle, and Define Handle........................................................... 2-10
Describe Handle.......................................................................................................................... 2-11
Complex Object Retrieval Handle............................................................................................ 2-12
Thread Handle............................................................................................................................ 2-12
Subscription Handle................................................................................................................... 2-12
Direct Path Handles.................................................................................................................... 2-12
Process Handle............................................................................................................................ 2-13
Connection Pool Handle............................................................................................................ 2-13
iv
Handle Attributes....................................................................................................................... 2-13
User Memory Allocation........................................................................................................... 2-14
Descriptors......................................................................................................................................... 2-15
Snapshot Descriptor................................................................................................................... 2-16
LOB/FILE Datatype Locator.................................................................................................... 2-17
Parameter Descriptor................................................................................................................. 2-18
ROWID Descriptor..................................................................................................................... 2-18
Datetime and Interval Descriptors........................................................................................... 2-18
Complex Object Descriptor....................................................................................................... 2-19
Advanced Queuing Descriptors............................................................................................... 2-19
LDAP-based Publish-Subscribe Notification......................................................................... 2-19
User Memory Allocation........................................................................................................... 2-19
OCI Programming Steps................................................................................................................. 2-20
OCI Environment Initialization..................................................................................................... 2-21
Creating the OCI Environment................................................................................................. 2-21
Shared Data Mode...................................................................................................................... 2-22
Allocating Handles and Descriptors........................................................................................ 2-25
Application Initialization, Connection, and Session Creation............................................. 2-26
Processing SQL Statements....................................................................................................... 2-29
Commit or Rollback......................................................................................................................... 2-29
Terminating the Application.......................................................................................................... 2-30
Error Handling.................................................................................................................................. 2-31
Return and Error Codes for Truncation and Null Data........................................................ 2-33
Functions Returning Other Values.......................................................................................... 2-34
Additional Coding Guidelines...................................................................................................... 2-34
Parameter Types......................................................................................................................... 2-35
Inserting Nulls into a Column.................................................................................................. 2-35
Indicator Variables..................................................................................................................... 2-36
Cancelling Calls.......................................................................................................................... 2-38
Positioned Updates and Deletes............................................................................................... 2-39
Reserved Words.......................................................................................................................... 2-39
Application Linking................................................................................................................... 2-41
Non-Blocking Mode................................................................................................................... 2-41
Setting Blocking Modes............................................................................................................. 2-42
Cancelling a Non-blocking Call................................................................................................ 2-42
v
Non-blocking Example.............................................................................................................. 2-42
Using PL/SQL in an OCI Program................................................................................................. 2-44
Globalization Support..................................................................................................................... 2-46
UTF-16 Environment Mode...................................................................................................... 2-46
Character Length Semantics...................................................................................................... 2-48
Character Set Support................................................................................................................ 2-49
3 Datatypes
Oracle Datatypes................................................................................................................................. 3-2
Using External Datatype Codes.................................................................................................. 3-4
Internal Datatypes.............................................................................................................................. 3-4
LONG, RAW, LONG RAW, VARCHAR2................................................................................ 3-5
Character Strings and Byte Arrays............................................................................................. 3-6
UROWID........................................................................................................................................ 3-6
External Datatypes.............................................................................................................................. 3-7
VARCHAR2................................................................................................................................... 3-9
NUMBER...................................................................................................................................... 3-11
INTEGER...................................................................................................................................... 3-12
FLOAT.......................................................................................................................................... 3-12
STRING........................................................................................................................................ 3-13
VARNUM.................................................................................................................................... 3-14
LONG........................................................................................................................................... 3-14
VARCHAR................................................................................................................................... 3-14
DATE........................................................................................................................................... 3-15
RAW.............................................................................................................................................. 3-16
VARRAW..................................................................................................................................... 3-16
LONG RAW................................................................................................................................. 3-17
UNSIGNED.................................................................................................................................. 3-17
LONG VARCHAR...................................................................................................................... 3-17
LONG VARRAW........................................................................................................................ 3-17
CHAR........................................................................................................................................... 3-17
CHARZ......................................................................................................................................... 3-19
New External Datatypes.................................................................................................................. 3-19
Named Data Types (Object, VARRAY, Nested Table).......................................................... 3-19
REF................................................................................................................................................ 3-20
vi
ROWID Descriptor..................................................................................................................... 3-20
LOB Descriptor........................................................................................................................... 3-21
Datetime and Interval Datatype Descriptors.......................................................................... 3-23
C Object-Relational Datatype Mappings................................................................................ 3-26
Data Conversions.............................................................................................................................. 3-26
Data Conversions for LOB Datatype Descriptors.................................................................. 3-28
Data Conversions for Datetime and Interval Datatypes....................................................... 3-29
Datetime and Date Migration Rules........................................................................................ 3-30
Typecodes........................................................................................................................................... 3-31
Relationship Between SQLT and OCI_TYPECODE Values................................................. 3-33
Definitions in oratypes.h................................................................................................................. 3-35
4 Using SQL Statements in OCI
Overview of SQL Statement Processing........................................................................................ 4-2
Processing SQL Statements.............................................................................................................. 4-2
Preparing Statements......................................................................................................................... 4-4
Using Prepared Statements on Multiple Servers..................................................................... 4-5
What is Binding?................................................................................................................................. 4-6
Executing Statements......................................................................................................................... 4-7
Execution Snapshots.................................................................................................................... 4-7
Execution Modes.......................................................................................................................... 4-8
Batch Error Mode for OCIStmtExecute().................................................................................. 4-9
Describing Select-List Items........................................................................................................... 4-12
Implicit Describe......................................................................................................................... 4-13
Explicit Describe of Queries...................................................................................................... 4-14
What is Defining?............................................................................................................................. 4-15
Fetching Results................................................................................................................................ 4-16
Fetching LOB Data..................................................................................................................... 4-17
Setting Prefetch Count............................................................................................................... 4-17
Scrollable Cursors............................................................................................................................. 4-18
Support for Scrollable Cursors in OCI..................................................................................... 4-18
Example of Access on a Scrollable Cursor.............................................................................. 4-19
5 Binding and Defining
Binding................................................................................................................................................. 5-2
vii
Named Binds and Positional Binds............................................................................................ 5-4
OCI Array Interface...................................................................................................................... 5-5
Binding Placeholders in PL/SQL............................................................................................... 5-5
Steps Used in Binding.................................................................................................................. 5-6
PL/SQL Example.......................................................................................................................... 5-8
Advanced Bind Operations............................................................................................................. 5-10
Named Data Type Binds............................................................................................................ 5-10
Binding REFs............................................................................................................................... 5-10
Binding LOBs.............................................................................................................................. 5-11
Binding in OCI_DATA_AT_EXEC Mode............................................................................... 5-17
Binding Ref Cursor Variables................................................................................................... 5-17
Summary of Bind Information.................................................................................................. 5-18
Defining.............................................................................................................................................. 5-19
Steps Used in Defining............................................................................................................... 5-20
Advanced Defines....................................................................................................................... 5-21
Advanced Define Operations......................................................................................................... 5-22
Defining Named Data Type Output Variables....................................................................... 5-22
Defining REF Output Variables................................................................................................ 5-22
Defining LOB Output Variables............................................................................................... 5-22
Defining PL/SQL Output Variables........................................................................................ 5-25
Defining For a Piecewise Fetch................................................................................................. 5-25
Binding and Defining Arrays of Structures................................................................................. 5-26
Skip Parameters.......................................................................................................................... 5-27
OCI Calls Used with Arrays of Structures.............................................................................. 5-29
Arrays of Structures and Indicator Variables......................................................................... 5-29
DML with RETURNING Clause................................................................................................... 5-30
Using DML with RETURNING Clause................................................................................... 5-30
Binding RETURNING...INTO variables.................................................................................. 5-31
Error Handling............................................................................................................................ 5-32
DML with RETURNING REF...INTO clause.......................................................................... 5-32
Additional Notes About Callbacks.......................................................................................... 5-34
Array Interface for DML RETURNING Statements............................................................. 5-34
Character Conversion Issues in Binding and Defining............................................................. 5-35
Choosing Character Set.............................................................................................................. 5-35
Using OCI_ATTR_MAXDATA_SIZE Attribute..................................................................... 5-36
viii
Using OCI_ATTR_MAXCHAR_SIZE Attribute.................................................................... 5-37
Buffer Expansion During Binding............................................................................................ 5-38
Constraint Checking During Defining.................................................................................... 5-39
General Compatibility Issues for Character Length Semantics........................................... 5-40
Code Examples for Binding and Defining with Character Conversion............................. 5-40
PL/SQL REF CURSORs and Nested Tables................................................................................. 5-43
Runtime Data Allocation and Piecewise Operations................................................................ 5-44
Valid Datatypes for Piecewise Operations............................................................................. 5-44
Binding and Defining for LOBs................................................................................................ 5-45
Types of Piecewise Operations................................................................................................. 5-46
Providing INSERT or UPDATE Data at Runtime.................................................................. 5-47
Piecewise Operations With PL/SQL....................................................................................... 5-50
Providing FETCH Information at Runtime............................................................................ 5-50
Additional Information About Piecewise Operations with No Callbacks......................... 5-53
6 Describing Schema Metadata
Describing Schema Metadata........................................................................................................... 6-2
Using OCIDescribeAny().................................................................................................................. 6-2
Restrictions.................................................................................................................................... 6-4
Notes on Types and Attributes................................................................................................... 6-4
Parameter Attributes.................................................................................................................... 6-5
Types OCI_PTYPE_TABLE or OCI_PTYPE_VIEW................................................................. 6-7
Procedure/Function/Subprogram Attributes......................................................................... 6-7
Package Attributes........................................................................................................................ 6-8
Type Attributes............................................................................................................................. 6-8
Type Attribute Attributes.......................................................................................................... 6-10
Type Method Attributes............................................................................................................ 6-11
Collection Attributes.................................................................................................................. 6-12
Synonym Attributes................................................................................................................... 6-14
Sequence Attributes................................................................................................................... 6-14
Column Attributes...................................................................................................................... 6-15
Argument/Result Attributes.................................................................................................... 6-16
List Attributes............................................................................................................................. 6-18
Schema Attributes...................................................................................................................... 6-19
Database Attributes.................................................................................................................... 6-19
ix
Character Length Semantics Support in Describing.............................................................. 6-21
Examples Using OCIDescribeAny().............................................................................................. 6-23
Retrieving Column Data Types For a Table........................................................................... 6-23
Describing the Stored Procedure.............................................................................................. 6-24
Retrieving Attributes of an Object Type.................................................................................. 6-26
Retrieving the Collection Element’s Data Type of a Named Collection Type................... 6-29
Describing with Character Length Semantics........................................................................ 6-30
7 LOB and FILE Operations
OCI and LOBs...................................................................................................................................... 7-2
Creating and Modifying Internal LOBs......................................................................................... 7-2
Associating a FILE in a Table with an OS File.............................................................................. 7-3
LOB Attributes of an Object............................................................................................................. 7-3
Writing to a LOB Attribute of an Object.................................................................................... 7-4
Transient Objects with LOB Attributes..................................................................................... 7-4
Array Interface For LOBs................................................................................................................... 7-4
LOB and FILE Functions.................................................................................................................... 7-5
Functions for Improving LOB Read/Write Performance..................................................... 7-11
LOB Buffering Functions........................................................................................................... 7-12
Functions for Opening and Closing LOBs.............................................................................. 7-12
LOB Read and Write Callbacks...................................................................................................... 7-14
The Callback Interface for Streaming...................................................................................... 7-15
Reading LOBs using Callbacks................................................................................................. 7-15
Writing LOBs using Callbacks.................................................................................................. 7-16
Temporary LOB Support................................................................................................................. 7-18
Creating and Freeing Temporary LOBs.................................................................................. 7-19
Temporary LOB Durations........................................................................................................ 7-19
Temporary LOB Example.......................................................................................................... 7-20
8 Managing Scalable Platforms
OCI Support for Transactions........................................................................................................... 8-2
Levels of Transactional Complexity........................................................................................... 8-2
Transaction Examples.................................................................................................................. 8-9
Related Initialization Parameters............................................................................................. 8-11
Password and Session Management............................................................................................. 8-11
x
