1 OCI: Introduction and Upgrading

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. OCI supports the data types, calling conventions, syntax, and semantics of C and C++.

OCI provides:

  • Improved 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

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

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

You can use OCI to access Oracle TimesTen In-Memory Database and Oracle In-Memory Database Cache. See Oracle TimesTen In-Memory Database C Developer's Guide.

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

  • 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

Advantages of OCI

OCI provides 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

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.

Oracle Database supports most popular third-party 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.

Parts of OCI

OCI has the following functionality:

  • 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

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 attributesfirst_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 includes functions that extend the capabilities of OCI to handle objects in an Oracle Database. 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

Additional OCI calls are provided to support manipulation of objects after they have been accessed by SQL statements. For a more detailed description, see "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 Database 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));

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:

    "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

  • 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.

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. These terms and operations are described in Chapter 4.

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

Simplified User Authentication and Password Management

OCI provides application developers with simplified user authentication and password management 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 simplified 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)

Extensions 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:

  • Increased client-side processing, and reduced server-side requirements on queries

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

  • Elimination of open and closed cursor round-trips

  • Improved 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 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

OCI Object Support

OCI provides a comprehensive application programming interface for programmers seeking to use Oracle Database object capabilities. These features can be divided into the following 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 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

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.

OCI Support for Oracle Streams Advanced Queuing

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

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:

Compatibility and Upgrading

The following sections discuss issues concerning compatibility between different releases of OCI client and server, changes in the OCI library routines, and upgrading an application from the release 7.x OCI to the current release of OCI.

Version Compatibility of Statically Linked and Dynamically Linked Applications

Here are the rules for relinking for a new release.

  • Statically linked OCI applications:

    Statically linked OCI applications must be relinked for both major and minor releases, because the statically linked Oracle Database client-side library code may be incompatible with the error messages in the upgraded Oracle home. For example, if an error message was updated with additional parameters then it is no longer compatible with the statically linked code.

  • Dynamically linked OCI applications:

    Dynamically linked OCI applications from Oracle Database 10g and later releases need not be relinked. That is, the Oracle Database client-side dynamic library is upwardly compatible with the previous version of the library. Oracle Universal Installer creates a symbolic link for the previous version of the library that resolves to the current version. Therefore, an application that is dynamically linked with the previous version of the Oracle Database client-side dynamic library does not need to be relinked to operate with the current version of the Oracle Database client-side library.

    Note:

    If the application is linked with a runtime library search path (such as -rpath on Linux), then the application may still run with the version of Oracle Database client-side library it is linked with. To run with the current version of Oracle Database client-side library, it must be relinked.

    See Also:

Upgrading of Existing OCI Release 7 Applications

OCI has been significantly improved with many features since OCI release 7. Applications that use the OCI release 7.3 API work unchanged against the current release of Oracle Database. They do need to be linked with the current client library. However, OCI release 7.3 API has been deprecated and this option will not be available in future Oracle releases.

OCI release 7 and the OCI calls of this release can be mixed in the same application and in the same transaction provided they are not mixed within the same statement execution. As a result, when migrating an existing OCI version 7 application you have the following two alternatives:

  • Upgrade to the current OCI client but do not modify the application: If you choose to upgrade from an Oracle release 7 OCI client to the current release OCI client, you need only link the new version of the OCI library and need not recompile your application. The relinked Oracle Database release 7 OCI applications work unchanged against a current Oracle Database. This option is deprecated and will not be available in future Oracle releases.

  • Upgrade to the current OCI client and modify the application: To use the performance and scalability benefits provided by the current OCI, however, you must modify your existing applications to use the current OCI programming paradigm, rebuild and relink them with the current OCI library, and run them against the current release of the Oracle database.

If you want to use any of the object capabilities of the current Oracle Database release, you must upgrade your client to the current release of OCI.

Note:

Applications using version 7 API will not be able to connect to Oracle Database 12c by default. Such applications willing to connect to Oracle Database 12c must set sqlnet.allowed_logon_version to 8.

Obsolete OCI Routines

Release 8.0 of the OCI introduced an entirely new set of functions that were not available in release 7.3. Oracle strongly recommends that new applications use the new calls to improve performance and provide increased functionality. Future releases of Oracle will not be supporting the release 7.3 API.

Table 1-1 lists the 7.x OCI calls with their later equivalents. For more information about the OCI calls, see the function descriptions in this guide. For more information about the 7.x calls, see Programmer's Guide to the Oracle Call Interface, Release 7.3.

Note:

In many cases the new or current OCI routines do not map directly onto the 7.x routines, so it almost 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 or current call is made. See the remaining chapters, in particular Chapter 2, "OCI Programming Basics" of this guide for more information.

Table 1-1 Obsolescent OCI Functions

7.x OCI Routine Equivalent or Similar Later OCI Routine

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

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

obreak()

OCIBreak()

ocan()

none

oclose()

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

ocof(), ocon()

OCIStmtExecute() with OCI_COMMIT_ON_SUCCESS mode

ocom()

OCITransCommit()

odefin(), odefinps()

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

odescr()

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

odessp()

OCIDescribeAny()

oerhms()

OCIErrorGet()

oexec(), oexn()

OCIStmtExecute()

oexfet()

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

ofen(), ofetch()

OCIStmtFetch()

oflng()

none

ogetpi()

OCIStmtGetPieceInfo()

olog()

OCILogon()

ologof()

OCILogoff()

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

oopen()

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

oopt()

none

oparse()

OCIStmtPrepare(); however, it is all local

opinit()

OCIEnvCreate()

orol()

OCITransRollback()

osetpi()

OCIStmtSetPieceInfo()

sqlld2()

SQLSvcCtxGet or SQLEnvGet

sqllda()

SQLSvcCtxGet or SQLEnvGet

odsc()

Note: see odescr() preceding

oermsg()

OCIErrorGet()

olon()

OCILogon()

orlon()

OCILogon()

oname()

Note: see odescr() preceding

osql3()

Note: see oparse() preceding


Note:

Applications using size_t to define host area (hda) that is passed to the OCI 7 calls, may crash on some platforms due to misaligned data. Hda_Def should be used instead of size_t in that case. If the application cannot be modified, the compiler and linker flag misalign can be used on a SPARC platform. Refer to the demos and the following documentation for more information: http://docs.oracle.com/cd/E19205-01/819-5267/bkauj/index.html.

OCI Routines Not Supported

Some OCI routines that were available in previous versions of OCI are not supported in the current release. They are listed in Table 1-2.

Table 1-2 OCI Functions Not Supported

OCI Routine Equivalent or Similar Later OCI Routine

obind()

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

obindn()

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

odfinn()

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

odsrbn()

Note: see odescr() in Table 1-1

ologon()

OCILogon()

osql()

Note: see oparse() Table 1-1


OCI Instant Client

The Instant Client feature simplifies the deployment of customer applications based on OCI, OCCI, ODBC, and JDBC OCI by eliminating the need for an Oracle home. The storage space requirement of an OCI application running in Instant Client mode is significantly reduced compared to the same application running in a full client-side installation. The Instant Client shared libraries occupy only about one-fourth the disk space of a full client-side installation.

A README file is included with the Instant Client installation. It describes the version, date and time, and the operating system the Instant Client was generated on.

Table 1-3 shows the Oracle Database client-side files required to deploy an OCI application:

Table 1-3 OCI Instant Client Shared Libraries

Linux and UNIX Description for Linux and UNIX Microsoft Windows Description for Microsoft Windows

libclntsh.so.12.1

libclntshcore.so.12.1

Client Code Library

oci.dll

Forwarding functions that applications link with

libociei.soFoot 1 

OCI Instant Client Data Shared Library

oraociei12.dll

Data and code

libnnz12.so

Security Library

orannzsbb12.dll

Security Library

libons.so

ONS library

oraons.dll

ONS library used by OCI internally

NAFoot 2 

NAFootref 2

oci.sym, oraociei12.sym, orannzsbb12.sym

Symbol tables


Footnote 1 Beginning with Oracle Database 12c Release 1 (12.1), libclntshcore.so.12.1 is separated from libclntsh.so.12.1 and the data shared library. libclntsh.so.12.1, libclntshcore.so.12.1, and libociei.so must reside in the same directory in order to operate in instant client mode.

Footnote 2 NA means not applicable.

A .sym file is provided for each dynamic-link library (DLL). When the .sym file is present in the same location as the DLL, a stack trace with function names is generated when a failure occurs in OCI on Windows.

Oracle Database 12c Release 1 (12.1) library names are used in the table.

To use the Microsoft ODBC and OLEDB driver, you must copy ociw32.dll from the ORACLE_HOME\bin directory.

Benefits of Instant Client

Why use Instant Client?

  • Installation involves copying a small number of files.

  • The Oracle Database client-side number of required files and the total disk storage are significantly reduced.

  • There is no loss of functionality or performance for applications deployed in Instant Client mode.

  • It is simple for independent software vendors to package applications.

OCI Instant Client Installation Process

The Instant Client libraries can be installed by either choosing the Instant Client option from Oracle Universal Installer or by downloading and installing the Instant Client libraries from the OCI page (see the bottom of OCI page for the Instant Client link) on the Oracle Technology Network website:

http://www.oracle.com/technology/tech/oci/instantclient/index.html

To Download and Install the Instant Client Libraries from the Oracle Technology Network Website

  1. Download and install the Instant Client shared libraries to an empty directory, such as instantclient_12_1, for Oracle Database 12c Release 1 (12.1). Choose the Basic package.

  2. Set the operating system shared library path environment variable to the directory from Step 1. For example, on Linux or UNIX, set LD_LIBRARY_PATH to instantclient_12_1. On Windows, set PATH to the instantclient_12_1 directory.

  3. If necessary, set the NLS_LANG environment variable to specify the language and territory used by the client application and database connections opened by the application, and the client's character set, which is the character set for data entered or displayed by a client program. NLS_LANG is set as an environment variable on UNIX platforms and is set in the registry on Windows platforms. See Oracle Database Globalization Support Guide for more information on setting the NLS_LANG environment variable.

After completing the preceding steps you are ready to run the OCI application.

The OCI application operates in Instant Client mode when the OCI shared libraries are accessible through the operating system Library Path variable. In this mode, there is no dependency on the Oracle home and none of the other code and data files provided in the Oracle home are needed by OCI (except for the tnsnames.ora file described later).

To Install the Instant Client from the Oracle Universal Installer

  1. Invoke the Oracle Universal Installer and select the Instant Client option.

  2. Install the Instant Client shared libraries to an empty directory, such as instantclient_12_1, for Oracle Database 12c Release 1 (12.1).

  3. Set the LD_LIBRARY_PATH to the instant client directory to operate in instant client mode.

  4. If necessary, set the NLS_LANG environment variable to specify the language and territory used by the client application and database connections opened by the application, and the client's character set, which is the character set for data entered or displayed by a client program. NLS_LANG is set as an environment variable on UNIX platforms and is set in the registry on Windows platforms. See Oracle Database Globalization Support Guide for more information on setting the NLS_LANG environment variable.

If you did a complete client installation (by choosing the Admin option in Oracle Universal Installer), the Instant Client shared libraries are also installed. The locations of the Instant Client shared libraries in a full client installation are:

On Linux or UNIX:

libociei.so library is in $ORACLE_HOME/instantclient

libclntsh.so.12.1, libclntshcore.so.12.1, and libnnz12.so are in $ORACLE_HOME/lib

On Windows:

oraociei12.dll library is in ORACLE_HOME\instantclient

oci.dll, ociw32.dll, and orannzsbb12.dll are in ORACLE_HOME\bin

To enable running the OCI application in Instant Client mode, copy the preceding libraries to a different directory and set the operating system shared library path to locate this directory.

Note:

All the libraries must be copied from the same Oracle home and must be placed in the same directory. Co-location of symlinks to Instant Client libraries is not a substitute for physical co-location of the libraries.

There should be only one set of Oracle libraries on the operating system Library Path variable. That is, if you have multiple directories containing Instant Client libraries, then only one such directory should be on the operating system Library Path.

Similarly, if an Oracle home-based installation is performed on the same system, then you should not have ORACLE_HOME/lib and the Instant Client directory on the operating system Library Path simultaneously regardless of the order in which they appear on the Library Path. That is, either the ORACLE_HOME/lib directory (for non-Instant Client operation) or Instant Client directory (for Instant Client operation) should be on the operating system Library Path variable, but not both.

To enable other capabilities such as OCCI and JDBC OCI, you must copy a few additional files. To enable OCCI, you must install the OCCI Library (libocci.so.12.1 on Linux or UNIX and oraocci12.dll on Windows) in the Instant Client directory. For the JDBC OCI driver, in addition to the three OCI shared libraries, you must also download OCI JDBC Library (for example libocijdbc12.so on Linux or UNIX and ocijdbc12.dll on Windows). Place all libraries in the Instant Client directory.

Note:

On hybrid platforms, such as Sparc64, to operate the JDBC OCI driver in the Instant Client mode, copy the libociei.so library from the ORACLE_HOME/instantclient32 directory to the Instant Client directory. Copy all other Sparc64 libraries needed for the JDBC OCI Instant Client from the ORACLE_HOME/lib32 directory to the Instant Client directory.

When to Use Instant Client

Instant Client is a deployment feature and should be used for running production applications. In general, all OCI functionality is available to an application being run in the Instant Client mode, except that the Instant Client mode is for client-side operation only. Therefore, server-side external procedures cannot operate in the Instant Client mode.

For development you can also use the Instant Client SDK.

Patching Instant Client Shared Libraries on Linux or UNIX

Because Instant Client is a deployment feature, the number and size of files (client footprint) required to run an OCI application has been reduced. Hence, all files needed to patch Instant Client shared libraries are not available in an Instant Client deployment. A complete client installation based on Oracle home is needed to patch the Instant Client shared libraries. Use the opatch utility to patch the Instant Client shared libraries.

After you apply the patch in an Oracle home environment, copy the files listed in Table 1-3 to the instant client directory, as described in "OCI Instant Client Installation Process".

Instead of copying individual files, you can generate Instant Client zip and RPM files for OCI and OCCI, JDBC, and SQL*Plus as described in "Regeneration of Data Shared Library and Zip and RPM Files". Then, you can copy the zip and RPM files to the target system and unzip them as described in "OCI Instant Client Installation Process".

The opatch utility stores the patching information of the ORACLE_HOME installation in libclntsh.so. This information can be retrieved by the following command:

genezi -v

If the Instant Client deployment system does not have the genezi utility, you can copy it from the ORACLE_HOME/bin directory.

Note:

The opatch utility is not available on Windows.

Regeneration of Data Shared Library and Zip and RPM Files

The process to regenerate the data shared library and the zip and RPM files has changed for Oracle Database 12c Release 1 (12.1) and later. Separate targets are added to create the data shared libraries, zip, and RPM files either individually or all at once. In previous releases, one target, ilibociei, was provided to build the data shared libraries, zip, and RPM files. Now ilibociei builds only the zip and RPM files. Regeneration of data shared libraries requires both a compiler and linker, which may not be available on all installations. Therefore, separate targets have been added to regenerate the data shared libraries.

Note:

The regenerated Instant Client binaries contain only the Instant Client files installed in the Oracle Client Administrator Home from which the regeneration is done. Therefore, error messages, character set encodings, and time zone files that are present in the regeneration environment are the only ones that are packaged in the data shared libraries. Error messages, character set encodings, and time zone files depend on which national languages were selected for the installation of the Oracle Client Administrator Home.

Regenerating Data Shared Library libociei.so

The OCI Instant Client Data Shared Library (libociei.so) can be regenerated by using the following commands in an Administrator Install of ORACLE_HOME:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk igenlibociei

The new regenerated libociei.so is placed in the ORACLE_HOME/instantclient directory. The original existing libociei.so located in this same directory is renamed to libociei.so0.

Regenerating Data Shared Library libociicus.so

To regenerate Instant Client Light data shared library (libociicus.so), use the following commands:

mkdir -p $ORACLE_HOME/rdbms/install/instantclient/light
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk igenlibociicus

The newly regenerated libociicus.so is placed in the ORACLE_HOME/instantclient/light directory. The original existing libociicus.so located in this same directory is renamed to libociicus.so0.

Regenerating Data Shared Libraries libociei.so and libociicus.so in One Step

To regenerate the data shared libraries libociei.so and libociicus.so, use the following commands:

mkdir -p $ORACLE_HOME/rdbms/install/instantclient/light
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk igenliboci

The newly regenerated libociei.so is placed in the ORACLE_HOME/instantclient directory. The original existing libociei.so located in this same directory is renamed to libociei.so0.

The newly regenerated libociicus.so is placed in the ORACLE_HOME/instantclient/light directory. The original existing libociicus.so located in this same directory is renamed to libociicus.so0.

Regenerating Zip and RPM Files for the Basic Package

To regenerate the zip and RPM files for the basic package, use the following commands:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk ic_basic_zip

Regenerating Zip and RPM Files for the Basic Light Package

To regenerate the zip and RPM files for the basic light package, use the following commands:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk ic_basiclite_zip

Regenerating Zip and RPM Files for the JDBC Package

To regenerate the zip and RPM files for the JDBC package, use the following commands:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk ic_jdbc_zip

Regenerating Zip and RPM Files for the ODBC Package

To regenerate the zip and RPM files for the ODBC package, use the following commands:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk ic_odbc_zip

Regenerating Zip and RPM Files for the SQL*Plus Package

To regenerate the zip and RPM files for the SQL*Plus package, use the following commands:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk ic_sqlplus_zip

Regenerating Zip and RPM Files for the Tools Package

To regenerate the zip and RPM files for the tools package, use the following commands:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk ic_tools_zip

Regenerating Zip and RPM Files for All Packages

To regenerate the zip and RPM files for all packages, use the following commands:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk ilibociei

The new zip and RPM files are generated under the following directory:

$ORACLE_HOME/rdbms/install/instantclient

Regeneration of the data shared library and the zip and RPM files is not available on Windows platforms.

Database Connection Strings for OCI Instant Client

OCI Instant Client can make remote database connections in all the ways that ordinary SQL clients can. However, because Instant Client does not have the Oracle home environment and directory structure, some database naming methods require additional configuration steps.

All Oracle Net naming methods that do not require use of ORACLE_HOME or TNS_ADMIN (to locate configuration files such as tnsnames.ora or sqlnet.ora) work in the Instant Client mode. In particular, the connect_identifier in the OCIServerAttach() call can be specified in the following formats:

  • A SQL Connect URL string of the form:

    [//]host[:port][/service name]
    

    For example:

    //dlsun242:5521/bjava21
    
  • As an Oracle Net connect descriptor. For example:

    "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=dlsun242) (PORT=5521))
    (CONNECT_DATA=(SERVICE_NAME=bjava21)))"
    
  • A Connection Name that is resolved through Directory Naming where the site is configured for LDAP server discovery.

For naming methods such as tnsnames and directory naming to work, the TNS_ADMIN environment variable must be set.

See Also:

Oracle Database Net Services Administrator's Guide chapter on "Configuring Naming Methods" for more about connect descriptors

If the TNS_ADMIN environment variable is not set, and TNSNAMES entries such as inst1, and so on, are used, then the ORACLE_HOME variable must be set, and the configuration files are expected to be in the $ORACLE_HOME/network/admin directory.

Note that the ORACLE_HOME variable in this case is only used for locating Oracle Net configuration files, and no other component of Client Code Library (OCI, NLS, and so on) uses the value of ORACLE_HOME.

If a NULL string, "", is used as the connection string in the OCIServerAttach() call, then the TWO_TASK environment variable can be set to the connect_identifier. On a Windows operating system, the LOCAL environment variable is used instead of TWO_TASK.

Similarly, for OCI command-line applications such as SQL*Plus, the TWO_TASK (or LOCAL on Windows) environment variable can be set to the connect_identifier. Its value can be anything that would have gone to the right of the '@' on a typical connect string.

Examples of Instant Client Connect Identifiers

If you are using SQL*Plus in Instant Client mode, then you can specify the connect identifier in the following ways:

If the listener.ora file on the Oracle database contains the following:

LISTENER = (ADDRESS_LIST=
 (ADDRESS=(PROTOCOL=tcp)(HOST=server6)(PORT=1573))
)
 
SID_LIST_LISTENER = (SID_LIST= 
 (SID_DESC=(SID_NAME=rdbms3)(GLOBAL_DBNAME=rdbms3.server6.us.alchemy.com)
(ORACLE_HOME=/home/dba/rdbms3/oracle))
)

The SQL*Plus connect identifier is:

"(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server6)(PORT=1573))(CONNECT_DATA=
(SERVICE_NAME=rdbms3.server6.us.alchemy.com)))"

The connect identifier can also be specified as:

"//server6:1573/rdbms3.server6.us.alchemy.com"

Alternatively, you can set the TWO_TASK environment variable to any of the previous connect identifiers and connect without specifying the connect identifier. For example:

setenv TWO_TASK "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server6)(PORT=1573))
(CONNECT_DATA=(SERVICE_NAME=rdbms3.server6.us.alchemy.com)))"

You can also specify the TWO_TASK environment variable as:

setenv TWO_TASK //server6:1573/rdbms3.server6.us.alchemy.com

Then you can invoke SQL*Plus with an empty connect identifier (you are prompted for the password):

sqlplus user

The connect descriptor can also be stored in the tnsnames.ora file. For example, if the tnsnames.ora file contains the following connect descriptor:

conn_str = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server6)(PORT=1573))(CONNECT_DATA=
(SERVICE_NAME=rdbms3.server6.us.alchemy.com)))

The tnsnames.ora file is located in the /home/webuser/instantclient directory, so you can set the variable TNS_ADMIN (or LOCAL on Windows) as:

setenv TNS_ADMIN /home/webuser/instantclient

Then you can use the connect identifier conn_str for invoking SQL*Plus, or for your OCI connection.

Note:

TNS_ADMIN specifies the directory where the tnsnames.ora file is located and TNS_ADMIN is not the full path of the tnsnames.ora file.

If the preceding tnsnames.ora file is located in an installation based Oracle home, in the /network/server6/home/dba/oracle/network/admin directory, then the ORACLE_HOME environment variable can be set as follows and SQL*Plus can be invoked as previously, with the identifier conn_str:

setenv ORACLE_HOME /network/server6/home/dba/oracle

Finally, if tnsnames.ora can be located by TNS_ADMIN or ORACLE_HOME, then the TWO_TASK environment variable can be set as follows enabling you to invoke SQL*Plus without a connect identifier:

setenv TWO_TASK conn_str

Environment Variables for OCI Instant Client

The ORACLE_HOME environment variable no longer determines the location of NLS, CORE, and error message files. An OCI-only application should not require ORACLE_HOME to be set. However, if it is set, it does not affect OCI. OCI always obtains its data from the Data Shared Library. If the Data Shared Library is not available, only then is ORACLE_HOME used and a full client installation is assumed. Though ORACLE_HOME is not required to be set, if it is set, then it must be set to a valid operating system path name that identifies a directory.

If Dynamic User callback libraries are to be loaded, then as this guide specifies, the callback package must reside in ORACLE_HOME/lib (ORACLE_HOME\bin on Windows). Set ORACLE_HOME in this case.

Environment variables ORA_NLS10 and ORA_NLS_PROFILE33 are ignored in the Instant Client mode.

In the Instant Client mode, if the ORA_TZFILE variable is not set, then the larger, default, timezlrg_n.dat file from the Data Shared Library is used. If the smaller timezone_n.dat file is to be used from the Data Shared Library, then set the ORA_TZFILE environment variable to the name of the file without any absolute or relative path names.

On Linux or UNIX:

setenv ORA_TZFILE timezone_n.dat

On Windows:

set ORA_TZFILE=timezone_n.dat

In these examples, n is the time zone data file version number.

To determine the versions of small and large timezone files that are packaged in the Instant Client Data Shared Library, enter the following command to run the genezi utility:

genezi -v

If OCI is not operating in the Instant Client mode (because the Data Shared Library is not available), then ORA_TZFILE variable, if set, names a complete path name as it does in previous Oracle Database releases.

If TNSNAMES entries are used, then, as mentioned earlier, TNS_ADMIN directory must contain the TNSNAMES configuration files. If TNS_ADMIN is not set, then the ORACLE_HOME/network/admin directory must contain Oracle Net Services configuration files.

Instant Client Light (English)

The Instant Client Light (English) version of Instant Client further reduces the disk space requirements of the client installation. The size of the library has been reduced by removing error message files for languages other than English and leaving only a few supported character set definitions out of around 250.

This Instant Client Light version is geared toward applications that use either US7ASCII, WE8DEC, WE8ISO8859P1, WE8MSWIN1252, or a Unicode character set. There is no restriction on the LANGUAGE and the TERRITORY fields of the NLS_LANG setting, so the Instant Client Light operates with any language and territory settings. Because only English error messages are provided with the Instant Client Light, error messages generated on the client side, such as Net connection errors, are always reported in English, even if NLS_LANG is set to a language other than AMERICAN. Error messages generated by the database side, such as syntax errors in SQL statements, are in the selected language provided the appropriate translated message files are installed in the Oracle home of the database instance.

Globalization Settings

Instant Client Light supports the following client character sets:

Single-byte

  • US7ASCII

  • WE8DEC

  • WE8MSWIN1252

  • WE8ISO8859P1

Unicode

  • UTF8

  • AL16UTF16

  • AL32UTF8

Instant Client Light can connect to databases having one of these database character sets:

  • US7ASCII

  • WE8DEC

  • WE8MSWIN1252

  • WE8ISO8859P1

  • WE8EBCDIC37C

  • WE8EBCDIC1047

  • UTF8

  • AL32UTF8

Instant Client Light returns an error if a character set other than those in the preceding lists is used as the client or database character set.

Instant Client Light can also operate with the OCI Environment handles created in the OCI_UTF16 mode.

See Also:

Oracle Database Globalization Support Guide for more information about National Language Support (NLS) settings

Operation of Instant Client Light

OCI applications, by default, look for the OCI Data Shared Library, libociei.so (or Oraociei12.dll on Windows) on the LD_LIBRARY_PATH (PATH on Windows) to determine if the application should operate in the Instant Client mode. If this library is not found, then OCI tries to load the Instant Client Light Data Shared Library (see Table 1-4), libociicus.so (or Oraociicus12.dll on Windows). If the Instant Client Light library is found, then the application operates in the Instant Client Light mode. Otherwise, a full installation based on Oracle home is assumed.

Table 1-4 OCI Instant Client Light Shared Libraries

Linux and UNIX Description for Linux and UNIX Windows Description for Windows

libclntsh.so.12.1

libclntshcore.so.12.1

Client Code Library

oci.dll

Forwarding functions that applications link with

libociicus.so

OCI Instant Client Light Data Shared Library

oraociicus12.dll

Data and code

libnnz12.so

Security Library

orannzsbb12.dll

Security Library

   

oci.sym, oraociicus12.sym, orannzsbb12.sym

Symbol tables


Installation of Instant Client Light

Instant Client Light can be installed in one of these ways:

  • From OTN

    Go to the Instant Client link from the OCI URL (see the bottom of OCI page for the Instant Client link) on the Oracle Technology Network website:

    http://www.oracle.com/technology/software/tech/oci/instantclient/
    

    For Instant Client Light, download and unzip the basiclite.zip package in to an empty instantclient_12_1 directory.

  • From Client Admin Install

    From the ORACLE_HOME/instantclient/light subdirectory, copy libociicus.so (or Oraociicus12.dll on Windows). The Instant Client directory on the LD_LIBRARY_PATH (PATH on Windows) should contain the Instant Client Light Data Shared Library, libociicus.so (Oraociicus12.dll on Windows), instead of the larger OCI Instant Client Data Shared Library, libociei.so (Oraociei12.dll on Windows).

  • From Oracle Universal Installer

    When you select the Instant Client option from the Oracle Universal Installer, libociei.so (or Oraociei12.dll on Windows) is installed in the base directory of the installation, which means these files are placed on the LD_LIBRARY_PATH (PATH on Widows).

    The Instant Light Client Data Shared Library, libociicus.so (or Oraociicus12.dll on Windows), is installed in the light subdirectory of the base directory and not enabled by default. Therefore, to operate in the Instant Client Light mode, the OCI Data Shared Library, libociei.so (or Oraociei12.dll on Windows) must be deleted or renamed and the Instant Client Light library must be copied from the light subdirectory to the base directory of the installation.

    For example, if Oracle Universal Installer has installed the Instant Client in my_oraic_12_1 directory on the LD_LIBRARY_PATH (PATH on Windows), then use the following command sequence to operate in the Instant Client Light mode:

    cd my_oraic_12_1
    rm libociei.so
    mv light/libociicus.so .
    

    Note:

    To ensure that no incompatible binaries exist in the installation, always copy and install the Instant Client files in to an empty directory.

SDK for Instant Client

The SDK can be downloaded from the Instant Client link on the OCI URL (see the bottom of OCI page for the Instant Client link) on the Oracle Technology Network website:

http://www.oracle.com/technology/tech/oci/instantclient/
  • The Instant Client SDK package has both C and C++ header files and a makefile for developing OCI and OCCI applications while in an Instant Client environment. Developed applications can be deployed in any client environment.

  • The SDK contains C and C++ demonstration programs.

  • On Windows, libraries required to link the OCI or OCCI applications are also included. Make.bat is provided to build the demos.

  • On UNIX or Linux, the makefile demo.mk is provided to build the demos. The instantclient_12_1 directory must be on the LD_LIBRARY_PATH before linking the application. The OCI and OCCI programs require the presence of libclntsh.so and libocci.so symbolic links in the instantclient_12_1 directory. demo.mk creates these before the link step. These symbolic links can also be created in a shell:

    cd instantclient_12_1
    ln -s libclntsh.so.12.1 libclntsh.so
    ln -s libocci.so.12.1 libocci.so
    
  • The SDK also contains the Object Type Translator (OTT) utility and its classes to generate the application header files.