Oracle Call Interface Programmer's Guide, Volumes 1 & 2
Release 8.0
A58234-01

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Purpose of this Guide

Feature Coverage and Availability

How to Use this Guide

How this Guide Is Organized

Conventions Used in this Guide

Your Comments Are Welcome

Part I Basic OCI Concepts

1
Introduction and New Features

The Oracle Call Interface

Data Definition Language

Control Statements

Data Manipulation Language

Special OCI/SQL Terms

Object Support in the OCI

Parts of the OCI

Release 8.0 New Features

Obsolescent and Obsolete OCI Calls

Compiling and Linking

2 OCI Programming Basics

OCI Program Structure

OCI Data Structures

Allocating and Freeing Handles

Environment Handle

Service Context and Associated Handles

Statement Handle, Bind Handle, and Define Handle

Describe Handle

Complex Object Retrieval Handle

Security Handle

Handle Attributes

User Memory Allocation

Descriptors and Locators

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

Initialize an OCI Process

Allocate Handles and Descriptors

Application Initialization, Connection, and Session Creation

Understanding Multiple Connections and Handles

A Connection Example

Processing SQL Statements

Commit or Rollback

Terminating the Application

Functions Returning Other Values

Additional Coding Guidelines

Parameter Types

Indicator Variables

Canceling Calls

Positioned Updates and Deletes

Application Linking

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

External Datatypes

New OCI 8.0 External Datatypes

NAMED DATA TYPE

New C Datatype Mappings

Data Conversions

Relationship Between SQLT and OCI_TYPECODE Values

Definitions in oratypes.h

4 SQL Statement Processing

Processing SQL Statements

Preparing Statements

Using Prepared Statements on Multiple Servers

Executing Statements

Execution Snapshots

Execution Modes

Describing Select-List Items

Implicit Describe

Explicit Describe of Queries

Fetching Results

Fetching LOB Data

Setting Prefetch Count

5 Binding and Defining

Named Binds and Positional Binds

OCI Array Interface

Binding Placeholders in PL/SQL

Steps Used in Binding

Advanced Bind Operations

Static Array Binds

Named Data Type Binds

Binding in OCI_DATA_AT_EXEC Mode

Binding Ref Cursor Variables

Summary of Bind Information

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

DML with RETURNING REF...INTO clause

Additional Notes About Callbacks

NCHAR and Character Conversion Issues

OCI_ATTR_MAXDATA_SIZE Attribute

Character Count Attribute

PL/SQL REF CURSORs and Nested Tables

6 Describing Schema Metadata

Using OCIDescribeAny()

Note on Datatype Codes

Note on Describing Types

Note on OCI_ATTR_LIST_ARGUMENTS

Parameter Attributes

Table/View Attributes

Procedure/Function Attributes

Package Attributes

Type Attributes

Type Attribute Attributes

Type Method Attributes

Collection Attributes

Synonym Attributes

Sequence Attributes

Column Attributes

Argument/Result Attributes

List Attributes

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 OCI Programming Advanced Topics

Levels of Transactional Complexity

Transaction Examples

Related Initialization Parameters

User Authentication and Password Management

Password Management

Advantages of OCI Thread Safety

Thread Safety and Three-Tier Architectures

Basic Concepts of Multi-threaded Development

Implementing Thread Safety with OCI 8.0

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

LOB and FILE Operations

LOBs and LOB Locators

Creating and Modifying Internal LOBs

Associating a FILE in a Table with an OS File

Writing to a LOB Attribute of an Object

Transient Objects with LOB Attributes

LOB/FILE Functions

Server Roundtrips for LOB Functions

LOB Read/Write Callbacks

The Callback Interface for Streaming

Reading LOBs using Callbacks

Writing LOBs using Callbacks

OCI Callbacks From External Procedures

Application Failover Callbacks

Failover Callback Overview

Failover Callback Structure and Parameters

Failover Callback Registration

Failover Callback Example

OCI and Advanced Queueing

OCI Advanced Queueing Functions

OCI Advanced Queueing Descriptors

Advanced Queueing in OCI vs. PL/SQL

Writing Oracle Security Services Applications

Part II OCI Object Concepts

8 OCI Object-Relational Programming

Chapter 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

Pin Count and Unpinning

Creating, Freeing, and Copying Objects

Object Reference and Type Reference

Error Handling in Object Applications

9 Object-Relational Datatypes

Mapping Oracle8 Datatypes to C

OCI Type Mapping Methodology

Manipulating C Datatypes With OCI

Precision of Oracle Number Operations

Date Conversion Functions

Date Assignment and Retrieval Functions

Date Arithmetic and Comparison Functions

Date Information Accessor Functions

Date Validity Checking Functions

Number (OCINumber)

Number Arithmetic Functions

Number Conversion Functions

Exponential and Logarithmic Functions

Trigonometric Functions

Number Assignment and Comparison Functions

Fixed or Variable-Length String (OCIString)

String Functions

Collections (OCITable, OCIArray, OCIColl, OCIIter)

Generic Collection Functions

Collection Data Manipulation Functions

Collection Scanning Functions

Varray/Collection Iterator Example

Nested Table Manipulation Functions

REF Manipulation Functions

Object Type Information Storage and Access

Descriptor Objects

10 Binding and Defining in Object Applications

Named Data Type Binds

Additional Information for Named Data Type and REF Binds

Defining Named Data Type Output Variables

Defining REF Output Variables

Additional Information for Named Data Type and REF Defines, and PL/SQL OUT Binds

Binding And Defining Oracle8 C Datatypes

Bind and Define Examples

3 Salary Update Examples

SQLT_NTY Bind/Define Example

11 Object Cache and Object Navigation

Chapter 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

Other 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

12 Using the Object Type Translator

Using the Object Type Translator

Creating Types in the Database

Invoking the OTT

The OTT Command Line

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 Command Line Syntax

Where OTT Parameters Can Appear

Structure of the Intype File

Nested #include File Generation

SCHEMA_NAMES Usage

Default Name Mapping

Part III OCI Reference

13 OCI Relational Functions

OCI Quick Reference

Calling OCI Functions

Server Roundtrips for LOB Functions

The OCI Relational Functions

OCIBindArrayOfStruct()

OCIBindByName()

OCIBindDynamic()

OCIBindObject()

OCIDefineArrayOfStruct()

OCIDefineByPos()

OCIDefineDynamic()

OCIDefineObject()

OCIDescribeAny()

OCIDescriptorAlloc()

OCIDescriptorFree()

OCIHandleAlloc()

OCIHandleFree()

OCIInitialize()

OCILdaToSvcCtx()

OCILobCharSetForm()

OCILobCharSetId()

OCILobDisableBuffering()

OCILobEnableBuffering()

OCILobFileClose()

OCILobFileCloseAll()

OCILobFileExists()

OCILobFileGetName()

OCILobFileIsOpen()

OCILobFileOpen()

OCILobFileSetName()

OCILobFlushBuffer()

OCILobGetLength()

OCILobIsEqual()

OCILobLoadFromFile()

OCILobLocatorIsInit()

OCIPasswordChange()

OCIServerAttach()

OCIServerDetach()

OCIServerVersion()

OCISessionBegin()

OCISessionEnd()

OCIStmtExecute()

OCIStmtGetBindInfo()

OCIStmtGetPieceInfo()

OCIStmtPrepare()

OCIStmtSetPieceInfo()

OCISvcCtxToLda()

OCITransCommit()

OCITransDetach()

OCITransForget()

OCITransPrepare()

OCITransRollback()

OCITransStart()

14 OCI Navigation and Type Functions

Object Types and Lifetimes

Navigational Function Return Values

Navigational Function Error Codes

Server Roundtrips for Cache and Object Functions

OCI Navigational Functions Quick Reference

The OCI Navigational Functions

OCICacheFlush()

OCICacheRefresh()

OCICacheUnmark()

OCICacheUnpin()

OCIObjectArrayPin()

OCIObjectCopy()

OCIObjectExists()

OCIObjectFlush()

OCIObjectFree()

OCIObjectGetAttr()

OCIObjectGetInd()

OCIObjectGetObjectRef()

OCIObjectGetProperty()

OCIObjectGetTypeRef()

OCIObjectIsDirty()

OCIObjectIsLocked()

OCIObjectLock()

OCIObjectMarkDelete()

OCIObjectMarkDeleteByRef()

OCIObjectMarkUpdate()

OCIObjectPinCountReset()

OCIObjectPinTable()

OCIObjectRefresh()

OCIObjectSetAttr()

OCIObjectUnmark()

OCIObjectUnmarkByRef()

OCIObjectUnpin()

OCITypeArrayByName()

OCITypeArrayByRef()

OCITypeByName()

15 OCI Datatype Mapping and Manipulation Functions

Datatype Mapping and Manipulation Function Return Values

Functions Returning Other Values

Server Roundtrips for Datatype Mapping and Manipulation Functions

OCI Datatype Mapping Functions Quick Reference

The OCI Datatype Mapping and Manipulation Functions

OCICollAppend()

OCICollAssign()

OCICollAssignElem()

OCICollGetElem()

OCIDateAddDays()

OCIDateAddMonths()

OCIDateAssign()

OCIDateCompare()

OCIDateDaysBetween()

OCIDateFromText()

OCIDateGetDate()

OCIDateGetTime()

OCIDateLastDay()

OCIDateNextDay()

OCIDateSetDate()

OCIDateSetTime()

OCIDateSysDate()

OCIDateToText()

OCIDateZoneToZone()

OCIIterCreate()

OCIIterDelete()

OCIIterGetCurrent()

OCINumberArcCos()

OCINumberArcSin()

OCINumberArcTan()

OCINumberArcTan2()

OCINumberAssign()

OCINumberCeil()

OCINumberFloor()

OCINumberFromInt()

OCINumberFromReal()

OCINumberFromText()

OCINumberHypCos()

OCINumberHypSin()

OCINumberHypTan()

OCINumberIntPower()

OCINumberIsZero()

OCINumberPower()

OCINumberRound()

OCINumberSetZero()

OCINumberSign()

OCINumberSqrt()

OCINumberToInt()

OCINumberToReal()

OCINumberToText()

OCINumberTrunc()

OCIRawAllocSize()

OCIRawAssignBytes()

OCIRawAssignRaw()

OCIRefFromHex()

OCIRefHexSize()

OCIRefIsEqual()

OCIStringAllocSize()

OCIStringAssign()

OCIStringAssignText()

OCIStringResize()

OCIStringSize()

OCITableDelete()

OCITableExists()

OCITableFirst()

16 OCI External Procedure Functions

With_Context Type

The OCI External Procedure Functions

OCIExtProcAllocCallMemory()

OCIExtProcRaiseExcp()

OCIExtProcRaiseExcpWithMsg()

OCIExtProcGetEnv()

Part IV Appendices

A Upgrading Release 7.x OCI Applications to Release 8.0

Compatibility and Upgrade Overview

Obsolescent OCI Routines

Obsolete OCI Routines

Application Linking Issues

Non-deferred linking

Single-task linking

B Handle and Descriptor Attributes

Environment Handle Attributes

OCI_ATTR_CACHE_MAX_SIZE

OCI_ATTR_CACHE_OPT_SIZE

OCI_ATTR_OBJECT

OCI_ATTR_FNCODE

OCI_ATTR_PINOPTION

OCI_ATTR_ALLOC_DURATION

OCI_ATTR_PIN_DURATION

Service Context Handle Attributes

OCI_ATTR_SQLCODE

OCI_ATTR_SERVER

OCI_ATTR_SESSION

OCI_ATTR_IN_V8_MODE

Server Handle Attributes

OCI_ATTR_FNCODE

OCI_ATTR_EXTERNAL_NAME

OCI_ATTR_INTERNAL_NAME

OCI_ATTR_IN_V8_MODE

User Session Handle Attributes

OCI_ATTR_USERNAME

OCI_ATTR_PASSWORD

Transaction Handle Attributes

OCI_ATTR_TRANS_NAME

Statement Handle Attributes

OCI_ATTR_FNCODE

OCI_ATTR_ROW_COUNT

OCI_ATTR_SQLFNCODE

OCI_ATTR_STMT_TYPE

OCI_ATTR_PARAM_COUNT

OCI_ATTR_PREFETCH_ROWS

OCI_ATTR_PREFETCH_MEMORY

Bind Handle Attributes

OCI_ATTR_FNCODE

OCI_ATTR_CHAR_COUNT

OCI_ATTR_CHARSET_ID

OCI_ATTR_CHARSET_FORM

OCI_ATTR_MAXDATA_SIZE

OCI_ATTR_ROWS_RETURNED

Define Handle Attributes

OCI_ATTR_FNCODE

OCI_ATTR_CHAR_COUNT

OCI_ATTR_CHARSET_ID

OCI_ATTR_CHARSET_FORM

Describe Handle Attributes

OCI_ATTR_PARAM_COUNT

Parameter Descriptor Attributes

LOB Locator Attributes

OCI_ATTR_LOBEMPTY

Complex Object Attributes

Complex Object Retrieval Handle Attributes

OCI_ATTR_COMPLEXOBJECT_LEVEL

OCI_ATTR_COMPLEXOBJECT_COLL_OUTOFLINE

Complex Object Retrieval Descriptor Attributes

OCI_ATTR_COMPLEXOBJECTCOMP_TYPE

OCI_ATTR_COMPLEXOBJECTCOMP_TYPE_LEVEL

Advanced Queueing Descriptor Attributes

OCIAQEnqOptions Descriptor Attributes

OCI_ATTR_RELATIVE_MSGID

OCI_ATTR_SEQUENCE_DEVIATION

OCI_ATTR_VISIBILITY

OCIAQDeqOptions Descriptor Attributes

OCI_ATTR_CONSUMER_NAME

OCI_ATTR_CORRELATION

OCI_ATTR_DEQ_MODE

OCI_ATTR_DEQ_MSGID

OCI_ATTR_NAVIGATION

OCI_ATTR_VISIBILITY

OCIAQMsgProperties Descriptor Attributes

OCI_ATTR_ATTEMPTS

OCI_ATTR_CORRELATION

OCI_ATTR_ENQ_TIME

OCI_ATTR_EXCEPTION_QUEUE

OCI_ATTR_EXPIRATION

OCI_ATTR_MSG_STATE

OCI_ATTR_PRIORITY

OCI_ATTR_RECIPIENT_LIST

OCIAQAgent Descriptor Attributes

OCI_ATTR_AGENT_ADDRESS

OCI_ATTR_AGENT_NAME

OCI_ATTR_AGENT_PROTOCOL

C Oracle Reserved Words, Keywords and Namespaces

Oracle Reserved Words and Keywords

PL/SQL Reserved Words

Oracle Reserved Namespaces

D Code Examples

Example 1, SQL Processing

Example 2, Object Retrieval

Example 3, DML with RETURNING Clause

Example 4, Describing an Object

Example 5, CLOB/BLOB Operations

Example 6, LOB Buffering

Example 7, REF Pinning and Navigation

E OCI Function Server Roundtrips

LOB Function Roundtrips

Object and Cache Function Roundtrips

Describe Operation Roundtrips

Datatype Mapping and Manipulation Function Roundtrips

Other Local Functions

F Oracle8 OCI New Features

Oracle8 OCI Enhancements

Encapsulated/Opaque Interfaces

Simplified User Authentication and Password Management

Extensions to Improve Application Performance and Scalability

Consistent Interface for Transaction Management

Oracle8 OCI Object Support

Runtime Environment for Objects

Type Management, Mapping and Manipulation Functions

Object Type Translator

OCI Support for Oracle Advanced Queueing

Benefits of the OCI's New Features

Comprehensive Support for Oracle8 Objects

Improved Application Performance

Greater Scalability

Simplified Migration of Existing Applications

Enhanced Application Extensibility

Copyright © 1997 Oracle Corporation.
All Rights Reserved.