1 Introduction to OCCI

Benefits of OCCI

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

• Leverages C++ and the Object Oriented Programming paradigm

• Is easy to use

• Is easy to learn for those familiar with JDBC

• Has a navigational interface to manipulate database objects of user-defined types as C++ class instances

Note that OCCI does not support non-blocking mode.

Building an OCCI Application

As Figure 1-1 shows, you compile and link an OCCI program in the same way that you compile and link an application that does not use the database.

Figure 1-1 The OCCI Development Process

Description of "Figure 1-1 The OCCI Development Process"

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.

Functionality of OCCI

OCCI provides the following functionality:

• APIs to design scalable, multithreaded applications 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 server

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

• Advanced Queuing for message management

• XA compliance for distributed transaction support

• Statement caching of SQL and PL/SQL queries

• Connection pooling for managing multiple connections

• Globalization and Unicode support to customize applications for international and regional language requirement

• Object Type Translator Utility

• Transparent Application Failover support

Procedural and Nonprocedural Elements

Oracle C++ Call Interface (OCCI) enables you to develop scalable, multithreaded applications on multitiered architectures that combine nonprocedural data access power of structured query language (SQL) with 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:

SELECT name FROM employees WHERE employee_id = :empnum

In this SQL statement, empnum 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.

DDL Statements

DDL statements manage schema objects in the database. These statements create new tables, drop old tables, and establish other schema objects. They also control access to schema objects. Example 1-1 illustrates how to create a table, and Example 1-2 shows how to grant and revoke privileges on this table.

CREATE TABLE employee_information ( 
   employee_id NUMBER(6), 
   ssn NUMBER(9), 
   dependents NUMBER(1),
   mail_address VARCHAR(60))

GRANT UPDATE, INSERT, DELETE ON employee_information TO donna
REVOKE UPDATE ON employee_information FROM jamie 

DDL statements also allow you to work with objects in the Oracle database, as in Example 1-3, which illustrates how to create an object table.

CREATE TYPE person_info_type AS OBJECT (
   employee_id NUMBER(6), 
   ssn NUMBER(9), 
   dependents NUMBER(1),
   mail_address VARCHAR(60))

CREATE TABLE person_info_table OF person_info_type 

Control Statements

OCCI applications treat transaction control, connection control, and system control statements (for example, DML statements).

DML SQL Statements

DML statements can change data in database tables. For example, DML statements are used to perform the following actions:

• 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

DML statements can require an application to supply data to the database by using input bind variables, as in Example 1-4. This statement can be executed several times with different bind values, or several rows can be added through array insert in a single round-trip to the server.

DML statements also enable you to work with objects in the Oracle Database, as in Example 1-5, which shows the insertion of an instance of a type into an object table.

INSERT INTO person_info_table
VALUES (person_info_type('450987','123456789','3','146 Winfield Street'))

Queries

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 Example 1-6:

SELECT department_name FROM departments
   WHERE department_id = 30

Queries can require the program to supply data to the database server by using input bind variables, as in Example 1-7:

SELECT first_name, last_name 
   FROM employees
   WHERE employee_id = :emp_id

In this SQL statement, emp_id is a placeholder for a value that will be supplied by the application.

Client-Side Object Cache

The object cache is a client-side memory buffer that provides lookup and memory management support for objects. It stores and tracks objects 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:

• 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

• Automatic garbage collection when cache thresholds are exceeded

Runtime Environment for Objects

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:

• Connecting to an Oracle database server in order to access its object functionality

• Allocating the client-side object cache and tuning its parameters

• Retrieving error and warning messages

• Controlling transactions that access objects in the database

• Associatively accessing objects through SQL

• Describing a PL/SQL procedure or function whose parameters or result are of Oracle object type

Associative and Navigational Interfaces

Applications that use OCCI can access objects in the database through several types of interfaces:

• SQL SELECT, INSERT, and UPDATE statements

• C++ pointers and references to access objects in the client-side object cache by traversing the corresponding references

OCCI provides a set of methods to support object manipulation by 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. OCCI provides methods to access objects by using SQL statements for:

• Binding object type instances and references as input and output variables of SQL statements and PL/SQL stored procedures

• Executing SQL statements that contain object type instances and references

• Fetching object type instances and references

• Retrieving column values from a result set as objects

• Describing a select-list item of an Oracle object type

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.

Metadata Class

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.

Object Type Translator Utility

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

In summary, OCCI supports object handling in an Oracle database by:

• Improving application developer productivity by eliminating the need to write the host language variables that correspond to schema objects

• Maintaining SQL as the data definition language of choice by providing the ability to automatically map Oracle database schema objects created by SQL to host language variables; this allows Oracle to support a consistent, enterprise-wide model of the user's data

• Facilitating schema evolution of object types by regenerating included header files when the schema is changed, allowing Oracle applications to support schema evolution

• Executing SQL statements that manipulate object data and schema information

• Passing 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 properties of SQL statements that return object instances and references

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

• Extending commit and rollback calls to synchronize object and relational functionality

• Advanced queuing of objects

OTT is typically invoked from the command line by specifying the intype file, the outtype file, and the specific database connection.