Oracle® Call Interface Programmer's Guide 10g Release 1 (10.1) Part Number B10779-01 |
|
|
View PDF |
This chapter contains these topics:
The Oracle Call Interface (OCI) is an application programming interface (API) that lets you create applications that use function calls 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++.
OCI provides:
OCI lets you manipulate data and schemas in an Oracle database using 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 runtime.
OCI has many new features that can be categorized into several primary areas:
OCI provides significant advantages over other methods of accessing an Oracle database:
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 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 system-specific documentation and the installation guide for more information about compiling and linking an OCI application for your operating system.
OCI has the following functionality:
The Oracle Call Interface (OCI) lets you develop scalable, multithreaded applications in a multitier architecture that combines 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 preceding 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.
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.
The object type definition serves as the basis for creating objects, which represent instances of the object type. Using the object type as a structural definition, a person
object could be created 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 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 SQL statements. For a more detailed description of enhancements and new features, refer to "Encapsulated Interfaces".
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:
Note: Queries are often classified as DML statements, but OCI applications process queries differently, so they are considered separately here. |
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 server, as in the following series of statements which creates 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;
OCI applications treat transaction control, session control, and system control statements like DML statements.
See Also:
Oracle Database SQL Reference for information about these types of statements |
Data manipulation language (DML) statements can change data in the database tables. For example, DML statements are used to:
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 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 preceding SQL statement, :empnumber
is a placeholder for a value that will be supplied by the application.
name
values returned from the query.
See Also:
|
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.
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 Oracle when the statement is processed. These placeholders need to be bound to C language variables in your program.
See Also:
|
OCI processes SQL statements as text strings that 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.
It is possible to mix OCI calls and embedded SQL in a precompiler program.
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:
SELECT
customer
and address
FROM
clause - customers
WHERE
clause - bus_type
and sales_volume
WHERE
clause - 'SOFTWARE
'WHERE
clause - :sales
When you develop your OCI application, you call routines that specify to the Oracle database server 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, "Using SQL Statements in OCI".
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 a 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 the handle by using accessor functions.
The OCI library manages a hierarchy of handles. Encapsulating the OCI interfaces by means of these handles has several benefits to the application developer, including:
OCI provides application developers with simplified user authentication and password management in several ways:
OCI supports two types of login sessions:
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 made by reducing the amount of state information that needs to be retained on the server side. Some of these features include:
SELECT
statement result sets to eliminate the describe round trip, reduce round trips, and reduce memory usageOCI provides a comprehensive application programming interface for programmers seeking to use the Oracle server's object capabilities. These features can be divided into five major categories:
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. 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 object cache provides the following to OCI applications:
Applications using OCI can access objects in the Oracle server through several types of interfaces:
SELECT
, INSERT
, and UPDATE
statementsOCI 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:
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:
REF
REF
s that point from one to the otherOCI provides functions for objects that manages how Oracle objects are used on the client-side. These functions provide for:
OCI provides two sets of functions to work with Oracle objects:
Additionally, the OCIDescribeAny()
function provides information about objects stored in the database.
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 which contains metadata information about Oracle schema objects. It 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 structures which can either be used with OCI programs or with the Pro*C/C++ precompiler programs.
OCI provides an interface to Oracle's Streams Advanced Queuing (Streams AQ) feature. Streams AQ provides message queuing as an integrated part of the Oracle server. 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.
OCI supports the Oracle XA library.
See Also:
Oracle Database Application Developer's Guide - Fundamentals for information about support for the Oracle XA library. |
The following sections discuss issues concerning compatibility between different versions of OCI client and server, changes in the OCI library routines, and upgrading an application from the release 7.x OCI to this release of OCI.
OCI has been significantly improved with many features. Applications written to work with OCI release 7 have a smooth migration 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 Oracle database version 7 server.
Specifically:
As a result, when migrating an existing OCI version 7 application you have the following two alternatives:
If you need to use any of the object capabilities of the current server release, you will need to upgrade your client to this release of OCI.
Here are the rules for re-linking for a new release.
Statically-linked applications need to be re-linked for both major and minor version releases, because the linked Oracle 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 will not be compatible with the statically-linked code.
Dynamically-linked applications need to be re-linked for major version releases only. OCI applications which are dynamically linked have a hard reference to the libclntsh.so.n
, where n
is the major release number (such as 9.0).
For minor releases like 9.2 where a new client-side library, libclntsh.so.9.2
, is created, OCI has a symbolic link called libclntsh.so.9.0
which points to libclntsh.so.9.2
.
Applications with a hard link to libclntsh.so.9.0
will continue to work in a 9.2 ORACLE_HOME
. For a major release OCI does not create all the earlier version symbolic links. In 10.1 there is no libclnsth.so.9.0
which points to libclnsth.so.10.1
. The application with a hard reference to libclnsh.so.9.0
cannot run in 10.1 ORACLE_HOME
unless it is re-linked.
See Also:
|
Release 8.0 of the OCI introduced an entirely new set of functions which were not available in release 7.3. Oracle continues to support these release 7.3 functions. Many of the earlier 7.x calls are available, but Oracle strongly recommends that new 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 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.
See Also:
For information about the additional functionality provided by new functions not listed here, see the remaining chapters of this guide. |
Some OCI routines that were available in previous versions of OCI are not supported in later releases. They are listed in Table 1-2, "OCI Routines Not Supported":
OCI Routine | Equivalent or Similar Later OCI Routine |
---|---|
|
|
|
|
|
|
|
Note: see |
|
|
|
Note: see |
This section addresses compatibility between different releases of OCI and Oracle server.
Existing 7.x applications with no new post-release 7.x calls have to be re-linked with the new client-side library.
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.
Programmers who wish to incorporate release post-release 7.x 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.
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:
OCILogon()
instead of olog()
(or other logon call). The service context handle can be used with new OCI calls or can be converted into an Lda_Def
to be used with 7.x OCI calls.
See Also:
See the description of |
Lda_Def
using OCISvcCtxToLda()
, and pass the resulting Lda_Def
to the 7.x calls.
Note: If there are multiple service contexts that share the same server handle, only one can be in Oracle version 7 mode at any one time. |
Lda_Def
back to a server context handle using OCILdaToSvcCtx()
.Lda_Def
and server context as often as necessary in the application.This approach allows an application to use a single connection, but two different APIs, to accomplish different tasks.
You can mix OCI 7.x and post-release 7.x 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 post-release 7.x OCI calls.
Caution: You cannot open a cursor, parse with OCI 7.x calls and then execute the statement with post-release 7.x calls. |
The Instant Client feature makes it extremely easy to deploy OCI, OCCI, ODBC, and JDBC-OCI based customer applications 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 only occupy about one-fourth the disk space of a full client installation.
Table 1-3 shows the Oracle client side files required to deploy an OCI application:
UNIX | Windows | Description |
---|---|---|
|
|
Client Code Library |
|
|
OCI Instant Client Data Shared Library |
|
|
Security Library |
Release 10.1 library names are used in the table. The number part of library names will change in future releases to agree with the release.
To use the Microsoft ODBC and OLEDB driver, ociw32.dll
must also be copied from ORACLE_HOME\bin.
The benefits of Instant Client are:
The Instant Client libraries can also be installed by choosing the Instant Client option from the Oracle Universal Installer. The Instant Client libraries can also be downloaded from the Oracle Technology Network (otn.oracle.com
) Web site. The installation process is as simple as:
instantclient
.instantclient
. On Windows, set PATH to locate the instantclient
directory.After completing the above two steps you are ready to run the OCI application.
The OCI application operates in Instant Client mode when the three OCI shared libraries are accessible through the OS Library Path variable. In this mode, there is no dependency on ORACLE_HOME
and none of the other code and data files provided in ORACLE_HOME
are needed by OCI (except for the tnsnames.ora
file described later).
If you have done a complete client installation (by choosing the Admin option) the Instant Client shared libraries are also installed. The location of the Instant Client shared libraries in a full client installation is:
On UNIX:
libociei.so
library is in $ORACLE_HOME/instantclient
libclnstsh.so.10.1
and libnnz10.so
are in $ORACLE_HOME/lib
On Windows:
oraociei10.dll
library is in ORACLE_HOME\instantclient
oci.dll
, ociw32.dl
l, orannzsbb10.dll
are in ORACLE_HOME\bin
By copying the above libraries to a different directory and setting the OS shared library path to locate this directory you can enable running the OCI application in Instant Client mode.
Note: All the libraries must be copied from the same |
To enable other capabilities such as OCCI and JDBC-OCI, a few other files need to be copied over as well. In particular, for the JDBC OCI driver, in addition to the three OCI shared libraries, you must also download OCI JDBC Library (for example libocijdbc10.so
on UNIX and oraocijdbc10.dll
on Windows) and ojdbcXY.jar
(where XY is the version number, for example, ojdbc14.jar
). All libraries must be able to be loaded from the library path and ojdbcXY.jar
must be able to be loaded from CLASSPATH.
For OCCI, the OCCI Library (libocci.so.10.1
on UNIX and oraocci10.dll
on Windows) must also be installed in a directory on the OS Library Path variable.
Instant Client is a deployment feature and should be used for running production applications. For development, a full installation is necessary to access OCI header files, Makefiles, demonstration programs, and so on. 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.
Because Instant Client is a deployment feature, the emphasis has been on reducing the number and size of files (client footprint) required to run an OCI application. Hence all files needed to patch Instant Client shared libraries are not available in an Instant Client deployment. An ORACLE_HOME
based full client installation is needed to patch the Instant Client shared libraries. The opatch
utility will take care of patching the Instant Client shared libraries.
After patching the Instant Client shared libraries Oracle recommends generating the patch inventory information by executing the following command from the ORACLE_HOME/OPatch
directory:
opatch lsinventory > opatchinv.out
The opatchinv.out
file should be copied along with the patched Instant Client libraries to the deployment directory. The information in opatchinv.out
will indicate all the patches that have been applied.
The opatch
inventory information for Instant Client libraries is not needed on the Windows platform, so this step can be skipped on Windows.
The OCI Instant Client Data Shared Library (libociei.so
) can be regenerated by performing the following steps in an Administrator Install of ORACLE_HOME
:
cd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk ilibociei
A new version of libociei.so
based on the current files in the ORACLE_HOME
is then placed in the ORACLE_HOME/instantclient
directory.
Regeneration of data shared library is not available on Windows platforms.
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 string in the OCIServerAttach()
call can be specified in the following formats:
"(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=dlsun242) (PORT=5521)) (CONNECT_DATA=(SERVICE_NAME=bjava21)))"
Naming methods that require TNS_ADMIN
to locate configuration files continue to work if the TNS_ADMIN
environment variable is set.
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.101/admin
directory.
Please 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
.
The bequeath adapter or the empty connect strings are not supported. However, an alternate way to use the empty connect string is to set the TWO_TASK
environment variable on UNIX, or the LOCAL
variable on Windows, to either a tnsnames.ora
entry or an Oracle Net keyword-value pair. If TWO_TASK
or LOCAL
is set to a tnsnames.ora
entry, then the tnsnames.ora
file must be able to be loaded by TNS_ADMIN
or ORACLE_HOME
setting.
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 have an impact on OCI's operation. OCI will always obtain 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. Even 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 has to reside in ORACLE_HOME/lib
(ORACLE_HOME\bin
on Windows). Therefore, ORACLE_HOME
should be set in this case.
Environment variables ORA_NLS33
, ORA_NLS32
, and ORA_NLS
are ignored in the Instant Client mode.
In the Instant Client mode, if the ORA_TZFILE
variable is not set, then the smaller, default, timezone.dat
file from the Data Shared Library is used. If the larger timezlrg.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. That is, on UNIX:
setenv ORA_TZFILE timezlrg.dat
On Windows:
set ORA_TZFILE timezlrg.dat
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 releases.
If TNSNAMES
entries are used, then, as mentioned earlier, TNS_ADMIN
directory must contain the TNSNAMES
configuration files, and if TNS_ADMIN
is not set, then the ORACLE_HOME/network.101/admin
directory must contain Oracle Net Services configuration files.