1. Programmer's Guide
  2. OCI: Introduction

1 OCI: Introduction

This chapter contains these topics:

Overview of OCI

Oracle Call Interface (OCI) is an application programming interface (API) that lets you create applications that use function calls to access an Oracle database and control all phases of SQL statement execution and data access.

OCI supports the data types, calling conventions, syntax, and semantics of C and C++.

OCI provides:

  • High performance and scalability through the efficient use of system memory and network connectivity

  • Consistent interfaces for dynamic session and transaction management in a two-tier client/server or multitier environment

  • N-tier authentication

  • Comprehensive support for application development using Oracle Database objects

  • Access to external databases, such as Oracle TimesTen In-Memory Database and Oracle In-Memory Database Cache. See Oracle TimesTen In-Memory Database C Developers Guide.

  • Applications that support an increasing number of users and requests without additional hardware investments

  • Ways to manipulate data and schemas in an Oracle Database using the C programming language and 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 run time.

  • Encapsulated or opaque interfaces, whose implementation details are unknown

  • Simplified user authentication and password management

  • Extensions to improve application performance and scalability

  • Consistent interface for transaction management

  • OCI extensions to support client-side access to Oracle objects

  • Significant advantages over other methods of accessing an Oracle Database:

    • More fine-grained control over all aspects of application design

    • High degree of control over program execution

    • Use of familiar third-generation language programming techniques and application development tools, such as browsers and debuggers

    • Connection pooling, session pooling, and statement caching that enable building of scalable applications

    • Support of dynamic SQL

    • Availability on the broadest range of operating systems of all the Oracle programmatic interfaces

    • Dynamic binding and defining using callbacks

    • Description functionality to expose layers of server metadata

    • Asynchronous event notification for registered client applications

    • Enhanced array data manipulation language (DML) capability for array inserts, updates, and deletes

    • Ability to associate commit requests with executes to reduce round-trips

    • Optimization of queries using transparent prefetch buffers to reduce round-trips

    • Thread safety, which eliminates the need for mutual exclusive locks (mutexes) on OCI handles

    • APIs to design a scalable, multithreaded application that can support large numbers of users securely

    • SQL access functions, for managing database access, processing SQL statements, and manipulating objects retrieved from an Oracle database

    • Data type mapping and manipulation functions, for manipulating data attributes of Oracle types

    • Data loading functions, for loading data directly into the database without using SQL statements

    • External procedure functions, for writing C callbacks from PL/SQL

See Also:

Building an OCI Application

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.

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

Alternatives to OCI

Some alternatives to using the Oracle Call Interface (OCI) include:

  • Oracle Database Programming Interface for C (ODPI-C)

  • Oracle C++ Call Interface (OCCI)

  • Oracle Pro*C/C++ Precompiler

  • Oracle ODBC Driver

Oracle Database Programming Interface for C (ODPI-C)

ODPI-C is an open source library of C code that simplifies the use of common Oracle Call Interface (OCI) features for Oracle Database drivers and user applications. ODPI-C sits on top of OCI and requires Oracle client libraries. ODPI-C:

  • Favors ease of use aimed at driver writers where niche special-case OCI features are not needed.

  • Provides a faster implementation of drivers with considerably less code. Oracle features can be exposed to users rapidly and in a consistent way.

  • Provides simpler memory management for binding variables and fetching.

  • Automatically converts binding and 'defining' (for fetches) to "native" C types so that additional calls do not need to be made. This is beneficial particularly for numbers and dates. The ability to retrieve LONG and LOB columns as strings and buffers is an advantage as well.

  • Provides a "safer" API in that resource handles are validated. Casts are not needed. A reference counting mechanism adds resiliency by stopping applications from destroying in-use OCI resources.

  • Provides an API that simplifies connection and resource management. For example, it automatically does session pool pinging to provide better High Availability.

  • Provides an alternative programming experience from OCI that uses a multiple getter and setter model for handling attributes.

  • Provides a sample Makefile that builds ODPI-C as a shared library. Or, the ODPI-C source code can be included in your project and built as you would build an OCI application.

See Also:

ODPI-C Home Page, for a list of ODPI-C supported features and a list of references including to its home page, code location on github, and documentation

Oracle C++ Call Interface (OCCI)

The Oracle C++ Call Interface (OCCI) is an application programming interface (API) that allows applications written in C++ to interact with one or more Oracle database servers. OCCI gives your programs the ability to perform the full range of database operations that are possible with an Oracle database server, including SQL statement processing and object manipulation.

See Also:

Oracle C++ Call Interface Programmer's Guide

Oracle Pro*C/C++ Precompiler

Oracle Pro*C/C++ Precompiler is a programming tool that enables the user to embed SQL statements in a high-level source program. The precompiler accepts the source program as input, translates the embedded SQL statements into standard Oracle runtime library calls, and generates a modified source program that you can compile, link, and execute in the usual way.

See Also:

Pro*C/C++ Programmer's Guide

Oracle ODBC Driver

The Oracle ODBC Driver enables ODBC applications on Microsoft Windows, as well as UNIX platforms like Linux, Solaris, IBM Advanced Interactive eXecutive (AIX), HP-UX Itaniutm, and IBM Linux on Platform z read and write access to Oracle® databases through the ODBC interface using Oracle Net Services software.

See Also:

Oracle Database Development Guide for more information about the Oracle ODBC Driver

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 Database recognizes several types of SQL statements:

See Also:

Using SQL Statements in OCI

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));

GRANT UPDATE, INSERT, DELETE ON employees TO donna;
REVOKE UPDATE ON employees FROM jamie;

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

CREATE TYPE person_t AS OBJECT (
    name     VARCHAR2(30),
    ssn      VARCHAR2(12),
    address  VARCHAR2(50));

CREATE TABLE person_tab OF person_t;

Control Statements

OCI applications treat transaction control, session control, and system control statements as if they were DML statements.

See Also:

Oracle Database SQL Language Reference for information about these types of statements

Data Manipulation Language

Data manipulation language (DML) statements can change data in the database tables.

For example, DML statements are used to:

  • Insert new rows into a table

  • Update column values in existing rows

  • Delete rows from a table

  • Lock a table in the database

  • Explain the execution plan for a SQL statement

  • Require an application to supply data to the database using input (bind) variables

    See Also:

    About Binding Placeholders in OCI for more information about input bind variables

DML statements also allow you to work with objects in the Oracle database, 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','987-65-4320','146 Winfield Street'));

Queries

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 preceding SQL statement, :empnumber is a placeholder for a value that is to be supplied by the application.

When processing a query, an OCI application also must define output variables to receive the returned results. In the preceding statement, you must define an output variable to receive any name values returned from the query.

See Also:

PL/SQL

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 some constructs to be grouped into a single block and executed as a unit. Among these are:

  • One or more SQL statements

  • Variable declarations

  • Assignment statements

  • Procedural control statements (IF...THEN...ELSE statements and loops)

  • Exception handling

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

  • Call Oracle Database stored procedures and stored functions

  • Combine procedural control statements with several SQL statements, so that they are executed as a unit

  • Access special PL/SQL features such as records, tables, cursor FOR loops, and exception handling

  • Use cursor variables

  • Take advantage of implicit result set capability that allows reuse of existing stored procedure designs that return implicit result sets

  • Access and manipulate objects in an Oracle database

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.

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

Note that the placeholders in this statement are not PL/SQL variables. They represent input values passed to the database when the statement is processed. These placeholders must be bound to C language variables in your program.

See Also:

Embedded SQL

OCI processes SQL statements as text strings that an application passes to the database 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.

It is possible to mix OCI calls and embedded SQL in a precompiler program.

See Also:

Pro*C/C++ Programmer's Guide

Special OCI Terms for SQL

This guide uses special terms to refer to the different parts of a SQL statement.

For example, consider the following SQL statement:

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

It contains the following parts:

  • A SQL command - SELECT

  • Two select-list items - customer and address

  • A table name in the FROM clause - customers

  • Two column names in the WHERE clause - bus_type and sales_volume

  • A literal input value in the WHERE clause - 'SOFTWARE'

  • A placeholder for an input variable in the WHERE clause - :sales

When you develop your OCI application, you call routines that specify to the Oracle database the address (location) of input and output variables of 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.

See Also:

Procedural and Nonprocedural Elements

OCI enables you to develop scalable, multithreaded applications in a multitier architecture that combines the nonprocedural data access power of structured query language (SQL) with the procedural capabilities of C and C++.

  • In a nonprocedural language program, the set of data to be operated on is specified, but what operations are to be performed, or how the operations are to be conducted, is not specified. The nonprocedural nature of SQL makes it an easy language to learn and to use to perform database transactions. It is also the standard language used to access and manipulate data in modern relational and object-relational database systems.

  • In a procedural language program, the execution of most statements depends on previous or subsequent statements and on control structures, such as loops or conditional branches, that are not available in SQL. The procedural nature of these languages makes them more complex than SQL, but it also makes them more flexible and powerful.

The combination of both nonprocedural 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. For example, an OCI program can run a query against an Oracle database. The query 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 preceding SQL statement, :empnumber is a placeholder for a value that is to 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 a database.

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—to represent a person's identifying characteristics.

The object type definition serves as the basis for creating objects that represent instances of the object type by using the object type as a structural definition, you could create a person object with the attribute values 'John', 'Bonivento', and '30'. Object types may also contain methods—programmatic functions that represent the behavior of that object type.

OCI provides a comprehensive application programming interface for programmers seeking to use Oracle Database object capabilities.

These capabilities include:

  • Executing SQL statements that manipulate object data and schema information

  • Passing of object references and instances as input variables in SQL statements

  • Declaring object references and instances as variables to receive the output of SQL statements

  • Fetching object references and instances from a database

  • Describing the properties of SQL statements that return object instances and references

  • Describing PL/SQL procedures or functions with object parameters or results

  • Extension of commit and rollback calls to synchronize object and relational functionality

OCI object features can be divided into the following major categories:

See Also:

Client-Side Object Cache

The object cache is a client-side memory buffer that provides lookup and memory management support for objects.

The object cache stores and tracks object instances that 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. When multiple applications run against the same server, each has its own object cache. The cache tracks the objects that are currently in memory, maintains references to objects, manages automatic object swapping, and tracks the meta-attributes or type information about objects. The object cache provides the following features to OCI applications:

  • Improved application performance by reducing the number of client/server round-trips required to fetch and operate on objects

  • Enhanced scalability by supporting object swapping from the client-side cache

  • Improved concurrency by supporting object-level locking

Associative and Navigational Interfaces

What are the different types of interfaces OCI applications can use to access objects?

Applications using OCI can access objects in an Oracle database through several types of interfaces:

  • Using SQL SELECT, INSERT, and UPDATE statements

  • Using a C-style pointer chasing scheme to access objects in the client-side cache by traversing the corresponding smart pointers or REFs

OCI provides a set of functions with extensions to support object manipulation using SQL SELECT, INSERT, and UPDATE statements. To access Oracle Database 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:

  • Binding and defining object type instances and references as input and output variables of SQL statements

  • Executing SQL statements that contain object type instances and references

  • Fetching object type instances and references

  • Describing select-list items of an Oracle object type

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

  • Instantiating a copy of a referenceable persistent object (that is, of a persistent object with object ID in the client-side cache) by pinning its smart pointer or REF

  • Traversing a sequence of objects that are connected to each other by traversing the REFs that point from one to the other

  • Dynamically getting and setting values of an object's attributes

OCI Runtime Environment for Objects

OCI provides functions for objects to manage how Oracle Database objects are used on the client side.

These functions provide for:

  • Connecting to an Oracle database server to access its object functionality, including initializing a session, logging on to a database server, and registering a connection

  • Setting up the client-side object cache and tuning its parameters

  • Getting errors and warning messages

  • Controlling transactions that access objects in the database

  • Associatively accessing objects through SQL

  • Describing PL/SQL procedures or functions whose parameters or results are Oracle types

Type Management: Mapping and Manipulation Functions

OCI provides two sets of functions to work with Oracle Database objects.

  • Type Mapping functions allow applications to map attributes of an Oracle schema represented in the server as internal Oracle data types to their corresponding host language types.

  • Type Manipulation functions allow host language applications to manipulate individual attributes of an Oracle schema such as setting and getting their values and flushing their values to the server.

Additionally, the OCIDescribeAny() function provides 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 of host language variables, such as structures.

The OTT takes as input an intype file that contains metadata information about Oracle schema objects. It generates an outtype file and the 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 is beneficial because it:

  • Improves application developer productivity: OTT eliminates the need for you to code the host language variables that correspond to schema objects.

  • Maintains SQL as the data definition language of choice: By providing the ability to automatically map Oracle schema objects that are created using SQL to host language variables, OTT facilitates the use of SQL as the data definition language of choice. This in turn allows Oracle Database to support a consistent model of data.

  • Facilitates schema evolution of object types: OTT regenerates included header files when the schema is changed, allowing Oracle applications to support schema evolution.

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

Simple Oracle Document Access (SODA)

SODA for C is a C API that is part of Oracle Call Interface (OCI).

SODA for C implements Simple Oracle Document Access (SODA). You can use it to perform create, read (retrieve), update, and delete (CRUD) operations on documents of any kind, and you can use it to query JSON documents. You compile programs that use SODA for C the same way you compile other OCI programs. SODA is a set of NoSQL-style APIs that let you create and store collections of documents in Oracle Database, retrieve them, and query them, without needing to know Structured Query Language (SQL) or how the data in the documents is stored in the database. Oracle Database supports storing and querying JSON data. To access this functionality, you need structured query language (SQL) with special JSON SQL operators. SODA for C hides the complexities of SQL/JSON programming.

Related Topics

Encapsulated Interfaces

All the data structures that are used by OCI calls 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 certain types of handles, 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 a handle by using accessor functions.

The OCI library manages a hierarchy of handles. Encapsulating the OCI interfaces with these handles has several benefits to the application developer, including:

  • Reduction of server-side state information that must be retained, thereby reducing server-side memory usage

  • Improvement of productivity by eliminating the need for global variables, making error reporting easier, and providing consistency in the way OCI variables are accessed and used

  • Allows changes to be made to the underlying structure without affecting applications

User Authentication and Password Management

OCI provides application developers with user authentication and password management.

This is supported in several ways:

  • OCI enables a single OCI application to authenticate and maintain multiple users.

  • OCI enables the application to update a user's password, which is particularly helpful if an expired password message is returned by an authentication attempt.

OCI supports two types of login sessions:

  • A login function for sessions by which a single user connects to the database using a login name and password

  • A mechanism by which a single OCI application authenticates and maintains multiple sessions by separating the login session (the session created when a user logs in to an Oracle database) from the user sessions (all other sessions created by a user)

Privileged connections, such as SYSDBA, SYSOPER, proxy authentication, external authentication, and others, are also supported.

Features to Improve Application Performance and Scalability

OCI provides several feature extensions 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 made by reducing the amount of state information that must be retained on the server side. Some of these features include:

  • Statement caching to improve performance by caching executable statements that are used repeatedly

  • Client result caching to limit the number of round trips to the database server

  • Implicit prefetching of SELECT statement result sets to eliminate the describe round-trip, reduce round-trips, and reduce memory usage

  • Elimination of open and close cursor round-trips

  • Support for multithreaded environments

  • Session multiplexing over connections

  • Consistent support for a variety of configurations, including standard two-tier client/server configurations, server-to-server transaction coordination, and three-tier transaction processing (TP)-monitor configurations

  • Consistent support for local and global transactions, including support for the XA interface's TM_JOIN operation

  • Improved scalability by providing the ability to concentrate connections, processes, and sessions across users on connections and by eliminating the need for separate sessions to be created for each branch of a global transaction

  • Allowing applications to authenticate multiple users and allow transactions to be started on their behalf

Oracle Database Advanced Queuing

OCI provides an interface to Oracle Database Advanced Queuing (Database AQ) feature.

Database AQ provides message queuing as an integrated part of Oracle Database. Database AQ provides this functionality by integrating the queuing system with the database, thereby creating a message-enabled database. By providing an integrated solution, Database AQ frees you to devote your efforts to your specific business logic rather than having to construct a messaging infrastructure.

See Also:

OCI and Database Advanced Queuing

XA Library Support

OCI supports the Oracle XA library.

The xa.h header file is in the same location as all the other OCI header files. For Linux or UNIX, the path is $ORACLE_HOME/rdbms/public. Users of the demo_rdbms.mk file on Linux or UNIX are not affected because this make file includes the $ORACLE_HOME/rdbms/public directory.

For Windows, the path is ORACLE_BASE\ORACLE_HOME\oci\include.

See Also:

Oracle Instant Client and Oracle Instant client Basic Light

Oracle Instant Client enables applications to connect to a local or remote Oracle Database for development and production deployment

  • About Oracle Instant Client: The Oracle Instant Client libraries provide the necessary network connectivity, as well as Oracle Database client-side files to create and run Oracle Call Interface (OCI), OCCI, ODBC, and JDBC OCI applications to make full use of Oracle Database.
  • About Oracle Instant Client Basic Light: Oracle Instant Client Basic Light further reduces the disk space requirements of a client installation.