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