|Oracle C++ Call Interface Programmer's Guide
Release 2 (9.2)
Part Number A96583-01
This chapter provides an overview of Oracle C++ Call Interface (OCCI) and introduces terminology used in discussing OCCI. You are provided with the background information needed to develop C++ applications that run in an Oracle environment.
The following topics are covered:
Oracle C++ Call Interface (OCCI) is an application program interface (API) that provides C++ applications access to data in an Oracle database. OCCI enables C++ programmers to utilize the full range of Oracle database operations, including SQL statement processing and object manipulation.
OCCI provides for:
OCCI provides a library of standard database access and retrieval functions in the form of a dynamic runtime library (OCCI classes) that can be linked in a C++ application at runtime. This eliminates the need to embed SQL or PL/SQL within third-generation language (3GL) programs.
OCCI provides these significant advantages over other methods of accessing an Oracle database:
As Figure 1-1 shows, you compile and link an OCCI program in the same way that you compile and link a nondatabase application.
Oracle supports most popular third-party compilers. The details of linking an OCCI program vary from system to system. On some platforms, it may be necessary to include other libraries, in addition to the OCCI library, to properly link your OCCI programs.
OCCI provides the following functionality:
Oracle C++ Call Interface (OCCI) enables you to develop scalable, shared server applications on multitiered architecture that combine the nonprocedural data access power of structured query language (SQL) with the procedural capabilities of C++.
In a nonprocedural language program, the set of data to be operated on is specified, but what operations will be performed, or how the operations are to be carried out, is not specified. The nonprocedural nature of SQL makes it an easy language to learn and 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, which are not available in SQL. The procedural nature of these languages makes them more complex than SQL, but it also makes them very flexible and powerful.
The combination of both nonprocedural and procedural language elements in an OCCI program provides easy access to an Oracle database in a structured programming environment.
OCCI supports all SQL data definition, data manipulation, query, and transaction control facilities that are available through an Oracle database server. For example, an OCCI program can run a query against an Oracle database. The queries can require the program to supply data to the database by using input (bind) variables, as follows:
In this SQL statement,
:empnumber is a placeholder for a value that will be supplied by the application.
In an OCCI 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. OCCI also provides facilities for accessing and manipulating objects in an Oracle database server.
One of the main tasks of an OCCI 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 OCCI application. Oracle recognizes several types of SQL statements:
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 which 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
OCCI applications treat transaction control, connection control, and system control statements like DML statements.
Data manipulation language (DML) statements can change data in database tables. For example, DML statements are used to perform the following actions:
DML statements can require an application to supply data to the database by using input (bind) variables. Consider the following statement:
Either this statement can be executed several times with different bind values, or an array insert can be performed to insert several rows in one round-trip to the server.
DML statements also enable 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
Queries are statements that retrieve data from tables in 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:
Queries can require the program to supply data to the database server by using input (bind) variables, as in the following example:
In this SQL statement,
:empnumber is a placeholder for a value that will be supplied by the application.
PL/SQL is Oracle's procedural extension to the SQL language. PL/SQL processes tasks that are more complicated than simple queries and SQL data manipulation language statements. PL/SQL allows a number of constructs to be grouped into a single block and executed as a unit. Among these are the following constructs:
IF ... THEN ... ELSEstatements and loops)
In addition to calling PL/SQL stored procedures from an OCCI program, you can use PL/SQL blocks in your OCCI program to perform the following tasks:
FORloops, and exception handling
A PL/SQL procedure or function can also return an output variable. This is called an out bind variable. For example:
Here, the first parameter is an input variable that provides the ID number of an employee. The second parameter, or the out bind variable, contains the return value of employee name.
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.
Note that the placeholders in this statement are not PL/SQL variables. They represent input and output parameters passed to and from the database server when the statement is processed. These placeholders need to be specified in your program.
This guide uses special terms to refer to the different parts of a SQL statement. Consider the following example of a SQL statement:
This example contains these parts:
When you develop your OCCI application, you call routines that specify to the database server the value of, or reference to, input and output variables in your program. In this guide, specifying the placeholder variable for data is called a bind operation. For input variables, this is called an in bind operation. For output variables, this is called an out bind operation.
OCCI 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 type might have attributes--
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. By using the object type as a structural definition, a
person object could be created with the attributes
30. Object types may also contain methods--programmatic functions that represent the behavior of that object type.
OCCI provides a comprehensive API for programmers seeking to use the Oracle database server's object capabilities. These features can be divided into several major categories:
The object cache is a client-side memory buffer that provides lookup and memory management support for objects. It stores and tracks objects which have been fetched by an OCCI application from the server to the client side. The client-side object cache is created when the OCCI environment is initialized in
object mode. Multiple applications running against the same server will each have their own object cache. The client-side object 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 client-side object cache provides the following benefits:
OCCI provides a runtime environment for objects that offers a set of methods for managing how Oracle objects are used on the client side. These methods provide the necessary functionality for performing these tasks:
Applications that use OCCI can access objects in the database through several types of interfaces:
OCCI provides a set of methods to support object manipulation by using SQL
UPDATE statements. To access Oracle objects, these SQL statements use a consistent set of steps as if they were accessing relational tables. OCCI provides methods to access objects by using SQL statements for:
OCCI provides a seamless interface for navigating objects, enabling you to manipulate database objects in the same way that you would operate on transient C++ objects. You can dereference the overloaded arrow (
->) operator on an object reference to transparently materialize the object from the database into the application space.
Each Oracle datatype is represented in OCCI by a C++ class. The class exposes the behavior and characteristics of the datatype by overloaded operators and methods. For example, the Oracle datatype
NUMBER is represented by
the Number class.
OCCI provides a metadata class that enables you to retrieve metadata describing database objects, including object types.
The Object Type Translator (OTT) utility translates schema information about Oracle object types into client-side language bindings. That is, OTT translates object type information into declarations of host language variables, such as structures and classes. OTT takes an
intype file which contains information about Oracle database schema objects as input. OTT generates an
outtype file and the necessary header and implementation files that must be included in a C++ application that runs against the object schema. 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.
In summary, OCCI supports the following methods to handle objects in an Oracle database: