Oracle Call Interface Programmer's Guide
Release 8.1.6
A76975-01
Library
Product
Index
Contents
Title and Copyright Information
Send Us Your Comments
Preface
Part I Basic OCI Concepts
1 Introduction, New Features, and Upgrading
Overview of OCI
Advantages of OCI
Building an OCI Application
Parts of the OCI
Procedural and Non-Procedural Elements
Object Support
SQL Statements
New Features
Encapsulated Interfaces
Simplified User Authentication and Password Management
Extensions to Improve Application Performance and Scalability
Oracle OCI Object Support
Client-side Object Cache
Associative and Navigational Interfaces
Runtime Environment for Objects
Type Management, Mapping and Manipulation Functions
Object Type Translator
OCI Support for Oracle Advanced Queueing
XA Library Support
Simplified Migration of Existing Applications
Compatibility, Upgrading, and Migration
Obsolescent OCI Routines
OCI Routines Not Supported
Compatibility
Upgrading
Application Linking Issues
2 OCI Programming Basics
Overview
OCI Program Structure
OCI Data Structures
Handles
Allocating and Freeing Handles
Environment Handle
Error Handle
Service Context and Associated Handles
Statement Handle, Bind Handle, and Define Handle
Describe Handle
Complex Object Retrieval Handle
Thread Handle
Subscription Handle
Direct Path Handles
Process Handle
Handle Attributes
User Memory Allocation
Descriptors
Snapshot Descriptor
LOB/FILE Datatype Locator
Parameter Descriptor
ROWID Descriptor
Complex Object Descriptor
Advanced Queueing Descriptors
User Memory Allocation
OCI Programming Steps
Initialization, Connection, and Session Creation
Initializing an OCI Environment
Shared Data Mode
Allocate Handles and Descriptors
Application Initialization, Connection, and Session Creation
Processing SQL Statements
Commit or Rollback
Terminating the Application
Error Handling
Return and Error Codes for Truncation and Null Data
Functions Returning Other Values
Additional Coding Guidelines
Parameter Types
Nulls
Indicator Variables
Cancelling Calls
Positioned Updates and Deletes
Reserved Words
Application Linking
Non-Blocking Mode
Setting Blocking Modes
Cancelling a Non-blocking Call
Non-blocking Example
Using PL/SQL in an OCI Program
3 Datatypes
Oracle Datatypes
Internal Datatype Codes
External Datatype Codes
Internal Datatypes
LONG, RAW, LONG RAW, VARCHAR2
Character Strings and Byte Arrays
Universal ROWID
External Datatypes
VARCHAR2
NUMBER
INTEGER
FLOAT
STRING
VARNUM
LONG
VARCHAR
ROWID
DATE
RAW
VARRAW
LONG RAW
UNSIGNED
LONG VARCHAR
LONG VARRAW
CHAR
CHARZ
New Oracle External Datatypes
NAMED DATA TYPE (Object, VARRAY, Nested Table)
REF
LOB
New C Datatype Mappings
Data Conversions
Typecodes
Relationship Between SQLT and OCI_TYPECODE Values
Definitions in oratypes.h
4 SQL Statement Processing
Overview
Processing SQL Statements
Preparing Statements
Using Prepared Statements on Multiple Servers
Binding
Executing Statements
Execution Snapshots
Execution Modes
Batch Error Mode for OCIStmtExecute()
Describing Select-List Items
Implicit Describe
Explicit Describe of Queries
Defining
Fetching Results
Fetching LOB Data
Setting Prefetch Count
5 Binding and Defining
Binding
Named Binds and Positional Binds
OCI Array Interface
Binding Placeholders in PL/SQL
Steps Used in Binding
PL/SQL Example
Advanced Binds
Advanced Bind Operations
Static Array Binds
Named Data Type Binds
Binding REFs
Binding LOBs
Binding in OCI_DATA_AT_EXEC Mode
Binding Ref Cursor Variables
Summary of Bind Information
Defining
Steps Used in Defining
Advanced Defines
Advanced Define Operations
Defining Named Data Type Output Variables
Defining REF Output Variables
Defining LOB Output Variables
Defining PL/SQL Output Variables
Defining For a Piecewise Fetch
Defining Arrays of Structures
Arrays of Structures
Skip Parameters
OCI Calls Used with Arrays of Structures
Arrays of Structures and Indicator Variables
DML with RETURNING Clause
Using DML with RETURNING Clause
Binding RETURNING...INTO variables
Error Handling
DML with RETURNING REF...INTO clause
Additional Notes About Callbacks
Array Interface for DML RETURNING Statements
NCHAR and Character Conversion Issues
NCHAR Issues
OCI_ATTR_MAXDATA_SIZE Attribute
Character Count Attribute
Fixed Width Unicode Support
PL/SQL REF CURSORs and Nested Tables
Run Time Data Allocation and Piecewise Operations
Providing INSERT or UPDATE Data at Run Time
Piecewise Operations With PL/SQL
Providing FETCH Information at Run Time
Additional Information About Piecewise Operations with No Callbacks
6 Describing Schema Metadata
Overview
Using OCIDescribeAny()
Restrictions
Notes on Types and Attributes
Parameter Attributes
Table/View Attributes
Procedure/Function/Subprogram Attributes
Package Attributes
Type Attributes
Type Attribute Attributes
Type Method Attributes
Collection Attributes
Synonym Attributes
Sequence Attributes
Column Attributes
Argument/Result Attributes
List Attributes
Schema Attributes
Database Attributes
Examples
Retrieving Column Data Types For a Table
Describing the Stored Procedure
Retrieving Attributes of an Object Type
Retrieving the Collection Element's Data Type of a Named Collection Type
7 LOB and FILE Operations
Overview
Creating and Modifying Internal LOBs
Associating a FILE in a Table with an OS File
LOB Attributes of an Object
Writing to a LOB Attribute of an Object
Transient Objects with LOB Attributes
Array Interface For LOBs
LOB and FILE Functions
Functions for Improving LOB Read/Write Performance
LOB Buffering Functions
Functions for Opening and Closing LOBs
Server Roundtrips for LOB Functions
LOB Read and Write Callbacks
The Callback Interface for Streaming
Reading LOBs using Callbacks
Writing LOBs using Callbacks
Temporary LOB Support
Creating and Freeing Temporary LOBs
Temporary LOB Durations
Temporary LOB Example
8 Managing Scalable Platforms
Overview
Transactions
Levels of Transactional Complexity
Transaction Examples
Related Initialization Parameters
Password and Session Management
Authentication Management
Password Management
Session Management
Middle-tier Applications
Attributes for Middle-tier Applications
Middle-tier Example
Thread Safety
Advantages of OCI Thread Safety
Thread Safety and Three-Tier Architectures
Basic Concepts of Multithreaded Development
Implementing Thread Safety
Multithreading Example
9 OCI Programming Advanced Topics
The OCIThread Package
Initialization and Termination
Passive Threading Primitives
Active Threading Primitives
Using the OCIThread Package
Example using OCIThread
User-defined Callback Functions
Registering User Callbacks
OCI Callbacks From External Procedures
Application Failover Callbacks
Failover Callback Overview
Failover Callback Structure and Parameters
Failover Callback Registration
Failover Callback Example
Handling OCI_FO_ERROR
OCI and Advanced Queuing
OCI Advanced Queuing Functions
OCI Advanced Queuing Descriptors
Advanced Queuing in OCI vs. PL/SQL
Publish-Subscribe Notification
Publish-Subscribe Functions
Notification Callback
Publish-Subscribe Example
Direct Path Loading
Limitations and Restrictions
Datatypes Supported
Direct Path Handles
Direct Path Interface Functions
Direct Path Load Example
Part II OCI Object Concepts
10 OCI Object-Relational Programming
Overview
OCI Object Overview
Working with Objects in the OCI
Basic Object Program Structure
Persistent Objects, Transient Objects, and Values
Developing an OCI Object Application
Representing Objects in C Applications
Initializing Environment and Object Cache
Making Database Connections
Retrieving an Object Reference from the Server
Pinning an Object
Manipulating Object Attributes
Marking Objects and Flushing Changes
Fetching Embedded Objects
Object Meta-Attributes
Complex Object Retrieval
COR Prefetching
OCI vs. SQL Access to Objects
Pin Count and Unpinning
Nullness
Creating Objects
Freeing and Copying Objects
Object Reference and Type Reference
Creating Objects Based on Object Views or User-defined OIDs
Error Handling in Object Applications
11 Object-Relational Datatypes
Overview
Mapping Oracle Datatypes to C
OCI Type Mapping Methodology
Manipulating C Datatypes With OCI
Precision of Oracle Number Operations
Date (OCIDate)
Date Conversion Functions
Date Assignment and Retrieval Functions
Date Arithmetic and Comparison Functions
Date Information Accessor Functions
Date Validity Checking Functions
Date Example
Number (OCINumber)
Number Arithmetic Functions
Number Conversion Functions
Exponential and Logarithmic Functions
Trigonometric Functions
Number Assignment, Comparison, and Evaluation Functions
Number Example
Fixed or Variable-Length String (OCIString)
String Functions
String Example
Raw (OCIRaw)
Raw Functions
Raw Example
Collections (OCITable, OCIArray, OCIColl, OCIIter)
Generic Collection Functions
Collection Data Manipulation Functions
Collection Scanning Functions
Varray/Collection Iterator Example
Nested Table Manipulation Functions
Nested Table Locators
REF (OCIRef)
REF Manipulation Functions
REF Example
Object Type Information Storage and Access
Descriptor Objects
12 Binding and Defining in Object Applications
Binding
Named Datatype Binds
Binding REFs
Information for Named Datatype and REF Binds
Defining
Defining Named Datatype Output Variables
Defining REF Output Variables
Information for Named Datatype and REF Defines, and PL/SQL OUT Binds
Binding And Defining Oracle C Datatypes
Bind and Define Examples
Salary Update Examples
SQLT_NTY Bind/Define Example
Bind Example
Define Example
13 Object Cache and Object Navigation
Overview
The Object Cache and Memory Management
Cache Consistency and Coherency
Object Cache Parameters
Object Cache Operations
Operations for Loading and Removing Object Copies
Operations for Making Changes to Object Copies
Operations for Synchronizing Object Copies with Server
Object Locking Operations
Commit and Rollback in Object Applications
Object Duration
Memory Layout of an Instance
Object Navigation
Simple Object Navigation
OCI Navigational Functions
Pin/Unpin/Free Functions
Flush and Refresh Functions
Mark and Unmark Functions
Object Meta-Attribute Accessor Functions
Other Functions
14 Using the Object Type Translator
OTT Overview
Using the Object Type Translator
Creating Types in the Database
Invoking the OTT
The OTT Command Line
OTT
userid
intype
outtype
code
hfile
initfile
initfunc
The Intype File
OTT Datatype Mappings
Null Indicator Structs
The Outtype File
Using the OTT with OCI Applications
Accessing and Manipulating Objects with OCI
Calling the Initialization Function
Tasks of the Initialization Function
OTT Reference
OTT Command Line Syntax
OTT Parameters
Where OTT Parameters Can Appear
Structure of the Intype File
Nested #include File Generation
SCHEMA_NAMES Usage
Default Name Mapping
Restriction
Part III OCI Reference
15 OCI Relational Functions
Introduction
Function Syntax
Calling OCI Functions
Server Roundtrips for LOB Functions
Advanced Queuing and Publish-Subscribe Functions
OCIAQDeq()
OCIAQEnq()
OCIAQListen()
OCISubscriptionDisable()
OCISubscriptionEnable()
OCISubscriptionPost()
OCISubscriptionRegister()
OCISubscriptionUnRegister()
Handle and Descriptor Functions
OCIAttrGet()
OCIAttrSet()
OCIDescriptorAlloc()
OCIDescriptorFree()
OCIHandleAlloc()
OCIHandleFree()
OCIParamGet()
OCIParamSet()
Bind, Define, and Describe Functions
OCIBindArrayOfStruct()
OCIBindByName()
OCIBindByPos()
OCIBindDynamic()
OCIBindObject()
OCIDefineArrayOfStruct()
OCIDefineByPos()
OCIDefineDynamic()
OCIDefineObject()
OCIDescribeAny()
OCIStmtGetBindInfo()
Direct Path Loading Functions
OCIDirPathAbort()
OCIDirPathColArrayEntryGet()
OCIDirPathColArrayEntrySet()
OCIDirPathColArrayRowGet()
OCIDirPathColArrayReset()
OCIDirPathColArrayToStream()
OCIDirPathFinish()
OCIDirPathLoadStream()
OCIDirPathPrepare()
OCIDirPathStreamReset()
Connect, Authorize, and Initialize Functions
OCIEnvCreate()
OCIEnvInit()
OCIInitialize()
OCILogoff()
OCILogon()
OCIServerAttach()
OCIServerDetach()
OCISessionBegin()
OCISessionEnd()
OCITerminate()
LOB Functions
OCIDurationBegin()
OCIDurationEnd()
OCILobAppend()
OCILobAssign()
OCILobCharSetForm()
OCILobCharSetId()
OCILobClose()
OCILobCopy()
OCILobCreateTemporary()
OCILobDisableBuffering()
OCILobEnableBuffering()
OCILobErase()
OCILobFileClose()
OCILobFileCloseAll()
OCILobFileExists()
OCILobFileGetName()
OCILobFileIsOpen()
OCILobFileOpen()
OCILobFileSetName()
OCILobFlushBuffer()
OCILobFreeTemporary()
OCILobGetChunkSize()
OCILobGetLength()
OCILobIsEqual()
OCILobIsOpen()
OCILobIsTemporary()
OCILobLoadFromFile()
OCILobLocatorAssign()
OCILobLocatorIsInit()
OCILobOpen()
OCILobRead()
OCILobTrim()
OCILobWrite()
OCILobWriteAppend()
Statement Functions
OCIStmtExecute()
OCIStmtFetch()
OCIStmtGetPieceInfo()
OCIStmtPrepare()
OCIStmtSetPieceInfo()
Thread Management Functions
OCIThreadClose()
OCIThreadCreate()
OCIThreadHandleGet()
OCIThreadHndDestroy()
OCIThreadHndInit()
OCIThreadIdDestroy()
OCIThreadIdGet()
OCIThreadIdInit()
OCIThreadIdNull()
OCIThreadIdSame()
OCIThreadIdSet()
OCIThreadIdSetNull()
OCIThreadInit()
OCIThreadIsMulti()
OCIThreadJoin()
OCIThreadKeyDestroy()
OCIThreadKeyGet()
OCIThreadKeyInit()
OCIThreadKeySet()
OCIThreadMutexAcquire()
OCIThreadMutexDestroy()
OCIThreadMutexInit()
OCIThreadMutexRelease()
OCIThreadProcessInit()
OCIThreadTerm()
Transaction Functions
OCITransCommit()
OCITransDetach()
OCITransForget()
OCITransMultiPrepare()
OCITransPrepare()
OCITransRollback()
OCITransStart()
Miscellaneous Functions
OCIBreak()
OCIErrorGet()
OCILdaToSvcCtx()
OCIPasswordChange()
OCIReset()
OCIServerVersion()
OCISvcCtxToLda()
OCIUserCallbackGet()
OCIUserCallbackRegister()
16 OCI Navigational and Type Functions
Introduction
Object Types and Lifetimes
Terminology
The Function Syntax
Navigational Function Return Values
Server Roundtrips for Cache and Object Functions
Navigational Function Error Codes
OCI Flush or Refresh Functions
OCICacheFlush()
OCICacheRefresh()
OCIObjectFlush()
OCIObjectRefresh()
OCI Mark or Unmark Object and Cache Functions
OCICacheUnmark()
OCIObjectMarkDelete()
OCIObjectMarkDeleteByRef()
OCIObjectMarkUpdate()
OCIObjectUnmark()
OCIObjectUnmarkByRef()
OCI Get Object Status Functions
OCIObjectExists()
OCIObjectGetProperty()
OCIObjectIsDirty()
OCIObjectIsLocked()
OCI Miscellaneous Object Functions
OCIObjectCopy()
OCIObjectGetAttr()
OCIObjectGetInd()
OCIObjectGetObjectRef()
OCIObjectGetTypeRef()
OCIObjectLock()
OCIObjectLockNoWait()
OCIObjectNew()
OCIObjectSetAttr()
OCI Pin, Unpin, and Free Functions
OCICacheFree()
OCICacheUnpin()
OCIObjectArrayPin()
OCIObjectFree()
OCIObjectPin()
OCIObjectPinCountReset()
OCIObjectPinTable()
OCIObjectUnpin()
OCI Type Information Accessor Functions
OCITypeArrayByName()
OCITypeArrayByRef()
OCITypeByName()
OCITypeByRef()
17 OCI Datatype Mapping and Manipulation Functions
Introduction
The Function Syntax
Datatype Mapping and Manipulation Function Return Values
Functions Returning Other Values
Server Roundtrips for Datatype Mapping and Manipulation Functions
Examples
OCI Collection and Iterator Functions
OCICollAppend()
OCICollAssign()
OCICollAssignElem()
OCICollGetElem()
OCICollIsLocator()
OCICollMax()
OCICollSetUpdateStatus()
OCICollSize()
OCICollTrim()
OCIIterCreate()
OCIIterDelete()
OCIIterGetCurrent()
OCIIterInit()
OCIIterNext()
OCIIterPrev()
OCI Date Functions
OCIDateAddDays()
OCIDateAddMonths()
OCIDateAssign()
OCIDateCheck()
OCIDateCompare()
OCIDateDaysBetween()
OCIDateFromText()
OCIDateGetDate()
OCIDateGetTime()
OCIDateLastDay()
OCIDateNextDay()
OCIDateSetDate()
OCIDateSetTime()
OCIDateSysDate()
OCIDateToText()
OCIDateZoneToZone()
OCI Number Functions
OCINumberAbs()
OCINumberAdd()
OCINumberArcCos()
OCINumberArcSin()
OCINumberArcTan()
OCINumberArcTan2()
OCINumberAssign()
OCINumberCeil()
OCINumberCmp()
OCINumberCos()
OCINumberDec()
OCINumberDiv()
OCINumberExp()
OCINumberFloor()
OCINumberFromInt()
OCINumberFromReal()
OCINumberFromText()
OCINumberHypCos()
OCINumberHypSin()
OCINumberHypTan()
OCINumberInc()
OCINumberIntPower()
OCINumberIsInt()
OCINumberIsZero()
OCINumberLn()
OCINumberLog()
OCINumberMod()
OCINumberMul()
OCINumberNeg()
OCINumberPower()
OCINumberPrec()
OCINumberRound()
OCINumberSetPi()
OCINumberSetZero()
OCINumberShift()
OCINumberSign()
OCINumberSin()
OCINumberSqrt()
OCINumberSub()
OCINumberTan()
OCINumberToInt()
OCINumberToReal()
OCINumberToText()
OCINumberTrunc()
OCI Raw Functions
OCIRawAllocSize()
OCIRawAssignBytes()
OCIRawAssignRaw()
OCIRawPtr()
OCIRawResize()
OCIRawSize()
OCI Ref Functions
OCIRefAssign()
OCIRefClear()
OCIRefFromHex()
OCIRefHexSize()
OCIRefIsEqual()
OCIRefIsNull()
OCIRefToHex()
OCI String Functions
OCIStringAllocSize()
OCIStringAssign()
OCIStringAssignText()
OCIStringPtr()
OCIStringResize()
OCIStringSize()
OCI Table Functions
OCITableDelete()
OCITableExists()
OCITableFirst()
OCITableLast()
OCITableNext()
OCITablePrev()
OCITableSize()
18 OCI Cartridge Functions
Introduction
The Function Syntax
Return Codes
With_Context Type
Cartridge Services -- OCI External Procedures
OCIExtProcAllocCallMemory()
OCIExtProcRaiseExcp()
OCIExtProcRaiseExcpWithMsg()
OCIExtProcGetEnv()
Cartridge Services -- Memory Services
OCIDurationBegin()
OCIDurationEnd()
OCIMemoryAlloc()
OCIMemoryResize()
OCIMemoryFree()
Cartridge Services -- Maintaining Context
OCIContextSetValue()
OCIContextGetValue()
OCIContextClearValue()
OCIContextGenerateKey()
Cartridge Services -- Parameter Manager Interface
OCIExtractInit()
OCIExtractTerm()
OCIExtractReset()
OCIExtractSetNumKeys()
OCIExtractSetKey()
OCIExtractFromFile()
OCIExtractFromStr()
OCIExtractToInt()
OCIExtractToBool()
OCIExtractToStr()
OCIExtractToOCINum()
OCIExtractToList()
OCIExtractFromList()
Cartridge Services -- File I/O Interface
OCIFileObject
OCIFileInit()
OCIFileTerm()
OCIFileOpen()
OCIFileClose()
OCIFileRead()
OCIFileWrite()
OCIFileSeek()
OCIFileExists()
OCIFileGetLength()
OCIFileFlush()
Cartridge Services -- String Formatting Interface
OCIFormatInit()
OCIFormatTerm()
OCIFormatString()
Format Modifiers
Format Codes
Example
Part IV Appendixes
A Handle and Descriptor Attributes
Conventions
Environment Handle Attributes
Error Handle Attributes
Service Context Handle Attributes
Server Handle Attributes
User Session Handle Attributes
Transaction Handle Attributes
Statement Handle Attributes
Bind Handle Attributes
Define Handle Attributes
Describe Handle Attributes
Parameter Descriptor Attributes
LOB Locator Attributes
Complex Object Attributes
Complex Object Retrieval Handle Attributes
Complex Object Retrieval Descriptor Attributes
Advanced Queueing Descriptor Attributes
OCIAQEnqOptions Descriptor Attributes
OCIAQDeqOptions Descriptor Attributes
OCIAQMsgProperties Descriptor Attributes
OCIAQAgent Descriptor Attributes
Subscription Handle Attributes
Direct Path Loading Handle Attributes
Direct Path Context Handle Attributes
Direct Path Column Array Handle Attributes
Direct Path Stream Handle Attributes
Direct Path Column Parameter Attributes
Process Handle Attributes
B OCI Demonstration Programs
C OCI Function Server Roundtrips
Overview
Relational Function Roundtrips
LOB Function Roundtrips
Object and Cache Function Roundtrips
Describe Operation Roundtrips
Datatype Mapping and Manipulation Function Roundtrips
Other Local Functions
Index
Copyright © 1999 Oracle Corporation.
All Rights Reserved.
Library
Product
Index