Skip Headers

Oracle Call Interface Programmer's Guide
Release 2 (9.2)

Part Number A96584-01
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page

Introduction and Upgrading

This chapter introduces you to the Oracle Call Interface (OCI). It provides background information that you need to develop applications using OCI. This chapter also introduces special terms that are used in discussing OCI. Compatibility and upgrading are also covered.

The following sections are in this chapter:

Overview of OCI

The Oracle Call Interface (OCI) is an application programming interface (API) that lets you create applications that use the native procedures or function calls of a third-generation language to access an Oracle database server and control all phases of SQL statement execution. OCI supports the datatypes, calling conventions, syntax, and semantics of C and C++.

See Also:

OCI provides:

OCI lets you manipulate data and schemas in an Oracle database using a host programming language, such as C. It provides a library of standard database access and retrieval functions in the form of a dynamic runtime library (OCI library) that can be linked in an application at runtime. This eliminates the need to embed SQL or PL/SQL within 3GL programs.

OCI has many new features that can be categorized into several primary areas:

Advantages of OCI

OCI provides significant advantages over other methods of accessing an Oracle database:

Building an OCI Application

As Figure 1-1 shows, you compile and link an OCI program in the same way that you compile and link a non-database application. There is no need for a separate preprocessing or precompilation step.

Figure 1-1 The OCI Development Process

Text description of lnoci007.gif follows
Text description of the illustration lnoci007.gif

Oracle supports most popular third-party compilers. The details of linking an OCI program vary from system to system. On some platforms, it may be necessary to include other libraries, in addition to the OCI library, to properly link your OCI programs. See your Oracle system-specific documentation and the installation guide for more information about compiling and linking an OCI application for your specific platform.

Parts of OCI

OCI has this functionality:

Procedural and Non-Procedural Elements

The Oracle Call Interface (OCI) lets you develop scalable, multithreaded applications on multitier architecture that combine the non-procedural data access power of Structured Query Language (SQL) with the procedural capabilities of C and C++.

The combination of both non-procedural and procedural language elements in an OCI program provides easy access to an Oracle database in a structured programming environment.

OCI supports all SQL data definition, data manipulation, query, and transaction control facilities that are available through an Oracle database server. For example, an OCI program can run a query against an Oracle database. The queries can require the program to supply data to the database using input (bind) variables, as follows:

SELECT name FROM employees WHERE empno = :empnumber

In the above SQL statement, :empnumber is a placeholder for a value that will be supplied by the application.

You can also take advantage of PL/SQL, Oracle's procedural extension to SQL. The applications you develop can be more powerful and flexible than applications written in SQL alone. OCI also provides facilities for accessing and manipulating objects in an Oracle database server.

Object Support

OCI has facilities for working with object types and objects. An object type is a user-defined data structure representing an abstraction of a real-world entity. For example, the database might contain a definition of a person object. That object might have attributes--first_name, last_name, and age--which represent a person's identifying characteristics.

OCI includes functions that extend the capabilities of OCI to handle objects in an Oracle database server. Specifically, the following capabilities have been added to OCI:

Additional OCI calls are provided to support manipulation of objects after they have been accessed by way of SQL statements. For a more detailed description of enhancements and new features, refer to "Encapsulated Interfaces".

SQL Statements

One of the main tasks of an OCI application is to process SQL statements. Different types of SQL statements require different processing steps in your program. It is important to take this into account when coding your OCI application. Oracle recognizes several types of SQL statements:

Data Definition Language

Data Definition Language (DDL) statements manage schema objects in the database. DDL statements create new tables, drop old tables, and establish other schema objects. They also control access to schema objects.

The following is an example of creating and specifying access to a table:

CREATE TABLE employees
    (name       VARCHAR2(20),
     ssn        VARCHAR2(12),
     empno      NUMBER(6),
     mgr        NUMBER(6),
     salary     NUMBER(6))

REVOKE UPDATE ON employees FROM jamie

DDL statements also allow you to work with objects in the Oracle database server, as in the following series of statements which creates an object table:

    name     VARCHAR2(30),
    ssn      VARCHAR2(12),
    address  VARCHAR2(50))

CREATE TABLE person_tab OF person_t

Control Statements

Data Manipulation Language

Data manipulation language (DML) statements can change data in the database tables. For example, DML statements are used to

DML statements also allow you to work with objects in the Oracle database server, as in the following example, which inserts an instance of type person_t into the object table person_tab:

INSERT INTO person_tab
    VALUES (person_t('Steve May','123-45-6789','146 Winfield Street'))


Queries are statements that retrieve data from a database. A query can return zero, one, or many rows of data. All queries begin with the SQL keyword SELECT, as in the following example:

SELECT dname FROM dept
     WHERE deptno = 42

Queries access data in tables, and they are often classified with DML statements. However, OCI applications process queries differently, so they are considered separately in this guide.

Queries can require the program to supply data to the database using input (bind) variables, as in the following example:

SELECT name 
    FROM employees
    WHERE empno = :empnumber

In the above SQL statement, :empnumber is a placeholder for a value that will be supplied by the application.


PL/SQL is Oracle's procedural extension to the SQL language. PL/SQL processes tasks that are more complicated than simple queries and SQL data manipulation language statements. PL/SQL allows a number of constructs to be grouped into a single block and executed as a unit. Among these are:

You can use PL/SQL blocks in your OCI program to:

The following PL/SQL example issues a SQL statement to retrieve values from a table of employees, given a particular employee number. This example also demonstrates the use of placeholders in PL/SQL statements.

    SELECT ename, sal, comm INTO :emp_name, :salary, :commission
    FROM emp
    WHERE ename = :emp_number;

Embedded SQL

OCI processes SQL statements as text strings, which an application passes to Oracle on execution. The Oracle precompilers (Pro*C/C++, Pro*COBOL, Pro*FORTRAN) allow you to embed SQL statements directly into your application code. A separate precompilation step is then necessary to generate an executable application.

Special OCI/SQL Terms

This guide uses special terms to refer to the different parts of a SQL statement. For example, a SQL statement such as

SELECT customer, address
FROM customers
WHERE bus_type = 'SOFTWARE'
AND sales_volume = :sales

contains the following parts:

When you develop your OCI application, you call routines that specify to the Oracle database server the address (location) of input and output variables in your program. In this guide, specifying the address of a placeholder variable for data input is called a bind operation. Specifying the address of a variable to receive select-list items is called a define operation.

For PL/SQL, both input and output specifications are called bind operations. These terms and operations are described in Chapter 4, "Using SQL Statements in OCI".

Encapsulated Interfaces

All the data structures that are used by OCI are encapsulated in the form of opaque interfaces that are called handles. A handle is an opaque pointer to a storage area allocated by the OCI library that stores context information, connection information, error information, or bind information about a SQL or PL/SQL statement. A client allocates a certain type of handle, populates one or more of those handles through well-defined interfaces, and sends requests to the server using those handles. In turn, applications can access the specific information contained in the handle by using accessor functions. The OCI library manages a hierarchy of handles. Encapsulating the OCI interfaces using these handles has several benefits to the application developer including:

Simplified User Authentication and Password Management

OCI provides application developers simplified user authentication and password management in several ways:

OCI supports two types of login sessions:

Extensions to Improve Application Performance and Scalability

OCI has several enhancements to improve application performance and scalability. Application performance has been improved by reducing the number of client to server round trips required and scalability improvements have been facilitated by reducing the amount of state information that needs to be retained on the server side. Some of these features include:

OCI Object Support

OCI provides the most comprehensive application programming interface for programmers seeking to use the Oracle server's object capabilities. These features can be divided into five major categories:

Client-Side Object Cache

The object cache is a client-side memory buffer that provides lookup and memory management support for objects. It stores and tracks objects instances which have been fetched by an OCI application from the server to the client side. The object cache is created when the OCI environment is initialized. Multiple applications running against the same server will each have their own object cache. The cache tracks the objects which are currently in memory, maintains references to objects, manages automatic object swapping and tracks the meta-attributes or type information about objects. The cache provides the following to OCI applications:

Associative and Navigational Interfaces

Applications using OCI can access objects in the Oracle server through several types of interfaces:

OCI provides a set of functions with extensions to support object manipulation using SQL SELECT, INSERT, and UPDATE statements. To access Oracle objects these SQL statements use a consistent set of steps as if they were accessing relational tables. OCI provides the following sets of functions required to access objects using SQL statements for:

OCI also provides a set of functions using a C-style pointer chasing scheme to access objects once they have been fetched into the client-side cache by traversing the corresponding smart pointers or REFs. This navigational interface provides functions for:

Runtime Environment for Objects

OCI provides a runtime environment for objects that offers a set of functions for managing how Oracle objects are used on the client-side. These functions provide the necessary functionality for:

Type Management, Mapping and Manipulation Functions

OCI provides two sets of functions to work with Oracle objects:

Additionally, the OCIDescribeAny() function can provide information about objects stored in the database.

Object Type Translator

The Object Type Translator (OTT) utility translates schema information about Oracle object types into client-side language bindings. That is, the Oracle OTT translates type information into declarations of host language variables, such as structures and classes. The OTT takes an intype file which contains metadata information about Oracle schema objects as input. The OTT generates an outtype file and the necessary header and implementation files that must be included in a C application that runs against the object schema. Both OCI applications and Pro*C/C++ precompiler applications may include code generated by the OTT. The OTT has many benefits including:

OTT is typically invoked from the command line by specifying the intype file, the outtype file and the specific database connection. With Oracle, OTT can only generate C structs which can either be used with OCI programs or with the Pro*C/C++ precompiler programs.

OCI Support for Oracle Advanced Queuing

OCI provides an interface to Oracle's Advanced Queuing (AQ) feature. Oracle AQ provides message queuing as an integrated part of the Oracle server. Oracle AQ provides this functionality by integrating the queuing system with the database, thereby creating a message-enabled database. By providing an integrated solution Oracle AQ frees you to devote your efforts to your specific business logic rather than having to construct a messaging infrastructure.

See Also:

For more information about the OCI AQ features, refer to "OCI and Advanced Queuing".

XA Library Support

See Also:

Oracle9i Application Developer's Guide - Fundamentals for information about support for the Oracle XA library.

Simplified Upgrading of Existing Applications

OCI has been significantly improved with many features. Applications written to work with OCI release 7 have a smooth upgrade path to this OCI release because of the interoperability of OCI release 7 clients with this release of the server, and of clients of this release with an Oracle7 database server.


As a result, when upgrading an existing OCI release 7 application you have the following three alternatives:

Further, if you need to use any of the object capabilities of the current server release, you will need to upgrade your client to use the this release of OCI.

Compatibility and Upgrading

This OCI release provides support for applications written with either the 7.x OCI or the 8.x or later OCI. This section discusses issues concerning compatibility between different versions of OCI and server, changes in the OCI library routines, and upgrading an application from the release 7.x OCI to this release of OCI.

See Also:

For the most recently updated information about compatibility and upgrading, refer to Oracle9i Database Migration

Obsolescent OCI Routines

Release 8.0 of the Oracle Call Interface introduced an entirely new set of functions which were not available in release 7.3. Release 8.1 added more new functions. Oracle9i OCI continues to support these new functions, and adds more new calls. The earlier 7.x calls are still available, but Oracle strongly recommends that existing applications use the new calls to improve performance and provide increased functionality.

Table 1-1, "Obsolescent OCI Routines" lists the 7.x OCI calls with their release 8.x or later equivalents. For more information about the OCI calls, see the function descriptions in Part III of this guide. For more information about the 7.x calls, see the Programmer's Guide to the Oracle Call Interface, Release 7.3. These 7.x calls are obsoleted, meaning that OCI has replaced them with newer calls. While the obsoleted calls are supported at this time, they may not be supported in all future versions of OCI.


In many cases the new OCI routines do not map directly onto the 7.x routines, so it may not be possible to simply replace one function call and parameter list with another. Additional program logic may be required before or after the new call is made. See the remaining chapters of this guide for more information.

Table 1-1 Obsolescent OCI Routines  
7.x OCI Routine Equivalent or Similar 8.x or Later OCI Routine

obindps(), obndra(), obndrn(), obndrv()

OCIBindByName(), OCIBindByPos() (Note: additional bind calls may be necessary for some data types.)






Note: cursors are not used in release 8.x or later

ocof(), ocon()

OCIStmtExecute() with OCI_COMMIT_ON_SUCCESS mode



odefin(), odefinps()

OCIDefineByPos() (Note: additional define calls may be necessary for some data types.)


Note: schema objects are described with OCIDescribeAny(). A describe, as used in release 7.x, will most often be done by calling OCIAttrGet() on the statement handle after SQL statement execution.





oexec(), oexn()



OCIStmtExecute(), OCIStmtFetch() (Note: result set rows can be implicitly prefetched.)

ofen(), ofetch()










onbclr(), onbset(), onbtst()

Note: nonblocking mode can be set or checked by calling OCIAttrSet() or OCIAttrGet() on the server context handle or service context handle


Note: cursors are not used in release 8.x or later




OCIStmtPrepare(); however, it is all local








xaoSvcCtx() or xaoEnv()


SQLSvcCtxGet() or SQLEnvGet()


Note: see odescr() above








Note: see odescr() above


Note: see oparse() above

See Also:

For information about the additional functionality provided by new functions not listed here, see the remaining chapters of this guide.

OCI Routines Not Supported

Some OCI routines that were available in previous versions of OCI are not supported in Oracle8i or Oracle9i. They are listed in Table 1-2, "OCI Routines Not Supported":

Table 1-2 OCI Routines Not Supported  
OCI Routine Equivalent or Similar 8.x or Later OCI Routine


OCIBindByName(), OCIBindByPos() (Note: additional bind calls may be necessary for some data types.)


OCIBindByName(), OCIBindByPos() (Note: additional bind calls may be necessary for some data types.)


OCIDefineByPos() (Note: additional define calls may be necessary for some data types.)


Note: see odescr() in Table 1-1




Note: see oparse() in Table 1-1


This section addresses compatibility between different versions of OCI and Oracle server.

Existing 7.x applications with no new release 8.x or later OCI calls have two choices:

In either case, the application will work against both Oracle7 and Oracle8i or later, with the exception that the function ocom() should be substituted for ocon(). ocon() enables AUTOCOMMIT (automatic commit of every DML statement), and thus leads to an error in a subsequent fetch statement.

The application will not be able to use the object features of Oracle8i or later, and will not get any of the performance or scalability benefits provided by those OCI releases.

New applications written completely in OCI will work seamlessly against both Oracle7 and Oracle8i or later, with the following exceptions:


Programmers who wish to incorporate release 8.x or later functionality into existing OCI applications have two options:

This manual should provide the information necessary to rewrite an existing application to use only new OCI calls.

Adding 8.x or Later OCI Calls to 7.x Applications

The following guidelines apply to programmers who want to incorporate new Oracle datatypes and features by using new OCI calls, while keeping 7.x calls for some operations:

This approach allows an application to use a single connection, but two different APIs, to accomplish different tasks.

You can mix and match OCI 7.x and OCI 8.x or later calls within a transaction, but not within a statement. This lets you execute one SQL or PL/SQL statement with OCI 7.x calls and the next SQL or PL/SQL statement within that transaction with Oracle8.x or later OCI calls.


You cannot open a cursor, parse with OCI 7.x calls and then execute the statement with OCI 8.x or later calls.

Go to previous page Go to next page
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index