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

Library
Product
Index

Next

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Purpose of this Guide
Audience
Feature Coverage and Availability
How to Use this Guide
VOLUME I
VOLUME II
Where to Begin
How this Guide Is Organized
VOLUME I
VOLUME II
Conventions Used in this Guide
Your Comments Are Welcome

Part I Basic OCI Concepts

1
Introduction and New Features

The Oracle Call Interface
SQL Statements
Data Definition Language
Control Statements
Data Manipulation Language
Queries
PL/SQL
Embedded SQL
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

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
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
Error Handling
Functions Returning Other Values
Additional Coding Guidelines
Parameter Types
Nulls
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
VARCHAR2
NUMBER
INTEGER
FLOAT
STRING
VARNUM
LONG
VARCHAR
ROWID
DATE
RAW
VARRAW
LONG RAW
UNSIGNED
LONG VARCHAR
LONG VARRAW
CHAR
CHARZ
MLSLABEL
New OCI 8.0 External Datatypes
NAMED DATA TYPE
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
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
NCHAR and Character Conversion Issues
NCHAR Issues
OCI_ATTR_MAXDATA_SIZE Attribute
Character Count Attribute
PL/SQL REF CURSORs and Nested Tables

6 Describing Schema Metadata

Overview
Using OCIDescribeAny()
Restrictions
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
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 OCI Programming Advanced Topics

Overview
Transactions
Levels of Transactional Complexity
Transaction Examples
Related Initialization Parameters
User Authentication and Password Management
Authentication
Password Management
Thread Safety
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
FILEs
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 Buffering
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
Nullness
Creating, Freeing, and Copying Objects
Object Reference and Type Reference
Error Handling in Object Applications

9 Object-Relational Datatypes

Overview
Mapping Oracle8 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 and Comparison 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
REF (OCIRef)
REF Manipulation Functions
REF Example
Object Type Information Storage and Access
Descriptor Objects

10 Binding and Defining in Object Applications

Binding
Named Data Type Binds
Binding REFs
Additional Information for Named Data Type and REF Binds
Defining
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
Bind Example
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

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
Restrictions

Part III OCI Reference

13 OCI Relational Functions

Introduction
OCI Quick Reference
Calling OCI Functions
Server Roundtrips for LOB Functions
The OCI Relational Functions
OCIAQDeq()
OCIAQEnq()
Example 4
OCIAttrGet()
OCIAttrSet()
OCIBindArrayOfStruct()
OCIBindByName()
OCIBindByPos()
OCIBindDynamic()
OCIBindObject()
OCIBreak()
OCIDefineArrayOfStruct()
OCIDefineByPos()
OCIDefineDynamic()
OCIDefineObject()
OCIDescribeAny()
OCIDescriptorAlloc()
OCIDescriptorFree()
OCIEnvInit()
OCIErrorGet()
OCIHandleAlloc()
OCIHandleFree()
OCIInitialize()
OCILdaToSvcCtx()
OCILobAppend()
OCILobAssign()
OCILobCharSetForm()
OCILobCharSetId()
OCILobCopy()
OCILobDisableBuffering()
OCILobEnableBuffering()
OCILobErase()
OCILobFileClose()
OCILobFileCloseAll()
OCILobFileExists()
OCILobFileGetName()
OCILobFileIsOpen()
OCILobFileOpen()
OCILobFileSetName()
OCILobFlushBuffer()
OCILobGetLength()
OCILobIsEqual()
OCILobLoadFromFile()
OCILobLocatorIsInit()
OCILobRead()
OCILobTrim()
OCILobWrite()
OCILogoff()
OCILogon()
OCIParamGet()
OCIParamSet()
OCIPasswordChange()
OCIServerAttach()
OCIServerDetach()
OCIServerVersion()
OCISessionBegin()
OCISessionEnd()
OCIStmtExecute()
OCIStmtFetch()
OCIStmtGetBindInfo()
OCIStmtGetPieceInfo()
OCIStmtPrepare()
OCIStmtSetPieceInfo()
OCISvcCtxToLda()
OCITransCommit()
OCITransDetach()
OCITransForget()
OCITransPrepare()
OCITransRollback()
OCITransStart()

14 OCI Navigation and Type Functions

Introduction
Object Types and Lifetimes
Terminology
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()
OCICacheFree()
OCICacheRefresh()
OCICacheUnmark()
OCICacheUnpin()
OCIObjectArrayPin()
OCIObjectCopy()
OCIObjectExists()
OCIObjectFlush()
OCIObjectFree()
OCIObjectGetAttr()
OCIObjectGetInd()
OCIObjectGetObjectRef()
OCIObjectGetProperty()
OCIObjectGetTypeRef()
OCIObjectIsDirty()
OCIObjectIsLocked()
OCIObjectLock()
OCIObjectMarkDelete()
OCIObjectMarkDeleteByRef()
OCIObjectMarkUpdate()
OCIObjectNew()
OCIObjectPin()
OCIObjectPinCountReset()
OCIObjectPinTable()
OCIObjectRefresh()
OCIObjectSetAttr()
OCIObjectUnmark()
OCIObjectUnmarkByRef()
OCIObjectUnpin()
OCITypeArrayByName()
OCITypeArrayByRef()
OCITypeByName()
OCITypeByRef()

15 OCI Datatype Mapping and Manipulation Functions

Introduction
Datatype Mapping and Manipulation Function Return Values
Functions Returning Other Values
Server Roundtrips for Datatype Mapping and Manipulation Functions
Examples
OCI Datatype Mapping Functions Quick Reference
The OCI Datatype Mapping and Manipulation Functions
OCICollAppend()
OCICollAssign()
OCICollAssignElem()
OCICollGetElem()
OCICollMax()
OCICollSize()
OCICollTrim()
OCIDateAddDays()
OCIDateAddMonths()
OCIDateAssign()
OCIDateCheck()
OCIDateCompare()
OCIDateDaysBetween()
OCIDateFromText()
OCIDateGetDate()
OCIDateGetTime()
OCIDateLastDay()
OCIDateNextDay()
OCIDateSetDate()
OCIDateSetTime()
OCIDateSysDate()
OCIDateToText()
OCIDateZoneToZone()
OCIIterCreate()
OCIIterDelete()
OCIIterGetCurrent()
OCIIterInit()
OCIIterNext()
OCIIterPrev()
OCINumberAbs()
OCINumberAdd()
OCINumberArcCos()
OCINumberArcSin()
OCINumberArcTan()
OCINumberArcTan2()
OCINumberAssign()
OCINumberCeil()
OCINumberCmp()
OCINumberCos()
OCINumberDiv()
OCINumberExp()
OCINumberFloor()
OCINumberFromInt()
OCINumberFromReal()
OCINumberFromText()
OCINumberHypCos()
OCINumberHypSin()
OCINumberHypTan()
OCINumberIntPower()
OCINumberIsZero()
OCINumberLn()
OCINumberLog()
OCINumberMod()
OCINumberMul()
OCINumberNeg()
OCINumberPower()
OCINumberRound()
OCINumberSetZero()
OCINumberSign()
OCINumberSin()
OCINumberSqrt()
OCINumberSub()
OCINumberTan()
OCINumberToInt()
OCINumberToReal()
OCINumberToText()
OCINumberTrunc()
OCIRawAllocSize()
OCIRawAssignBytes()
OCIRawAssignRaw()
OCIRawPtr()
OCIRawResize()
OCIRawSize()
OCIRefAssign()
OCIRefClear()
OCIRefFromHex()
OCIRefHexSize()
OCIRefIsEqual()
OCIRefIsNull()
OCIRefToHex()
OCIStringAllocSize()
OCIStringAssign()
OCIStringAssignText()
OCIStringPtr()
OCIStringResize()
OCIStringSize()
OCITableDelete()
OCITableExists()
OCITableFirst()
OCITableLast()
OCITableNext()
OCITablePrev()
OCITableSize()

16 OCI External Procedure Functions

Introduction
Return Codes
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
Compatibility
Upgrading
Application Linking Issues
Non-deferred linking
Single-task linking

B Handle and Descriptor Attributes

Conventions
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_ENV
OCI_ATTR_SERVER
OCI_ATTR_SESSION
OCI_ATTR_TRANS
OCI_ATTR_IN_V8_MODE
Server Handle Attributes
OCI_ATTR_ENV
OCI_ATTR_FNCODE
OCI_ATTR_EXTERNAL_NAME
OCI_ATTR_INTERNAL_NAME
OCI_ATTR_IN_V8_MODE
OCI_ATTR_FOCBK
User Session Handle Attributes
OCI_ATTR_USERNAME
OCI_ATTR_PASSWORD
Transaction Handle Attributes
OCI_ATTR_TRANS_NAME
OCI_ATTR_XID
Statement Handle Attributes
OCI_ATTR_FNCODE
OCI_ATTR_ROW_COUNT
OCI_ATTR_SQLFNCODE
OCI_ATTR_ENV
OCI_ATTR_STMT_TYPE
OCI_ATTR_ROWID
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_PDSCL
OCI_ATTR_PDFMT
OCI_ATTR_ROWS_RETURNED
Define Handle Attributes
OCI_ATTR_FNCODE
OCI_ATTR_CHAR_COUNT
OCI_ATTR_CHARSET_ID
OCI_ATTR_CHARSET_FORM
OCI_ATTR_PDSCL
OCI_ATTR_PDFMT
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
OCI_ATTR_WAIT
OCIAQMsgProperties Descriptor Attributes
OCI_ATTR_ATTEMPTS
OCI_ATTR_CORRELATION
OCI_ATTR_DELAY
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
cdemo82.h
cdemo82.sql
Example 3, DML with RETURNING Clause
cdemodr1.h
Example 4, Describing an Object
cdemodsc.h
Example 5, CLOB/BLOB Operations
Example 6, LOB Buffering
Example 7, REF Pinning and Navigation

E OCI Function Server Roundtrips

Overview
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

Introduction
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

Next
Oracle
Copyright © 1997 Oracle Corporation.
All Rights Reserved.

Library
Product
Index