The Oracle Call Interface (OCI) is the Oracle software that allows the outside world access to the hidden core of the Oracle database. An open source application—or any other type of non-Oracle program—can use OCI to connect directly to Oracle via its internal SQL engine. OCI is a complex product, and this short chapter can’t do justice to all of its capabilities. We’ll cover only the fundamentals here, examining the basic Application Programming Interface (API) of OCI and how it is typically used with open source software. We’ll introduce OCI and its main functions, and we’ll explain how it relates to Open DataBase Connectivity (ODBC) and Java DataBase Connectivity (JDBC).
Although you can access OCI directly, most developers prefer a simpler and more convenient interface. As an example of how open source applications use such interfaces to communicate with OCI, we’ll take a close look at how Perl applications use the Perl Database Interface (DBI) module (and its Oracle-specific driver, DBD::Oracle) to connect to Oracle databases. We’ve chosen Perl here because it was one of the first open source languages to communicate directly with Oracle; the interface dates from 1990, with Kevin Stock’s original work on Oraperl. We’ll describe other interfaces in their respective chapters (for example, Tcl with its Oratcl interface, and Python with its DBOracle interface, in Chapter 3). Throughout this book, we’ll mention, as appropriate, how various open source tools make use of OCI.
The Oracle Call Interface is the comprehensive API that is used to connect internally to the Oracle database server. OCI is based on C and provides all the requirements you might need to support your Oracle-based applications, including the following:
High performance
Security features, including user authentication
Scalability
Full and dynamic access to Oracle8i objects
User session handles, dynamic connections, and session management
Multi-threaded capabilities
Support for accessing special Oracle8 datatypes, such as large objects (LOB), BFILE, and LONG
Transactions
Full character set support
At the most basic level, virtually all outside programs, from web applications to standalone GUI applications, interact with Oracle through this program layer. (The one major exception is the JDBC client-side driver, which we’ll discuss shortly.)
Fortunately, the OCI libraries are automatically available in every
Oracle database installation; there is no special installation
process. You’ll generally discover the appropriate files under
the $ORACLE_HOME/lib and
$ORACLE_HOME/include directories. Most open
source applications have therefore accepted Oracle’s open
invitation to the database, and they use OCI to gain their front-door
entry into the world of Oracle programming.
As Figure 2-1 indicates, OCI acts as the primary port of destination for every connection to and from the server. To simplify OCI’s sometimes complex operations, other database APIs can also be wrapped around the OCI. Examples of such wrap-around APIs include the popular ODBC, the Java-based JDBC (at least partially, as we’ll explain), and the Perl DBI. These three APIs are described in the following sections.
Open DataBase Connectivity (ODBC) is an API designed to ease the task of communicating with different database types from within the same outside application program. ODBC, which was championed originally by Microsoft and the client/server architectures, following initial work by the SQL Access Group on their CLI standard,[16] provides all the functionality of the standardized SQL language. If a database supports the SQL/92 standard, then once you place the ODBC bridge between your programs and the Oracle server, you need only talk to the ODBC API; you don’t need to worry about any specific Oracle or OCI code requirements.
If OCI is complicated and ODBC is simple, then why not just use ODBC, especially since you can use ODBC identically with other databases? There are several reasons why open source systems have generally tended towards using the home-grown OCI, rather than going through ODBC:
- ODBC’s extra layer reduces performance
Because ODBC merely hides OCI from you rather than bypassing it, it becomes just another wraparound layer you have to wade through in order to get your data in and out of the database. If you can use OCI directly, you can cut out the middleman, increasing performance in the process.
- ODBC provides a “one-size-fits-all” service
Because ODBC is limited to the basic SQL standard (in order to work across every database type), using it means that you won’t have access to some of the special features of Oracle—for example, the various types of LOBs (large objects). If you want to make use of these features, even occasionally, you can’t use ODBC. With ODBC, you must remain entirely, and at all times, within the confines of its restricted set of universal functionality.
- OCI is optimized for Oracle
OCI is specifically designed to reduce Oracle memory usage and data round-trips, optimize the multi-threaded server (MTS), and generally work as efficiently as possible with its native Oracle database type. If you use ODBC’s more generalized API, you won’t reap the benefits of this specialization.
Java DataBase Connectivity (JDBC) is
very similar to ODBC in that it provides a
“generic” database API for manipulating data across a
wide range of database types. Also, you must use it with Java
programs that employ the industry-standard
java.sql packages. Oracle provides access to a
number of different JDBC drivers that connect to the
Oracle database. We’ll discuss all of these in Chapter 7. For now, though, we’ll focus on the two
driver types most often used for Oracle/Java programming:
As Figure 2-2 shows, the Fat JDBC/OCI Driver is similar to ODBC in that it uses OCI directly in order to access Oracle, whereas the Thin JDBC Driver does not. If your client-side Java programs use the Thin Driver (across a TCP/IP bridge), they can access their target Oracle databases directly without troubling OCI. This is achieved by building an OCI-like set of functions into the Thin Driver, thereby making it quite a portable solution.
In most systems, such as Perl, your Oracle access drivers must reside on the same machine as the database to enable you to pick up its OCI libraries. However, the Thin JDBC Driver is always much slower than the Fat Driver, because it has to carry around the baggage of this complex OCI-like burden. If you find that performance becomes a more pressing need than portability, you may therefore become tempted to employ the Fat Driver instead. We’ll describe these tradeoffs in some detail in Chapter 7.
There are hundreds of OCI functions, so we won’t try to present a complete summary here. Table 2-1 lists the OCI functions most often used in the kinds of applications discussed in this book.
Table 2-1. OCI Function Summary
|
OCI Function |
Description |
|---|---|
|
|
Sets a handle attribute |
|
|
Gets a handle attribute |
|
|
Binds a variable with a SQL placeholder by name |
|
|
Binds a program variable and a SQL placeholder by position |
|
|
Defines a selected item by position for the output data buffer |
|
|
Describes an existing schema object |
|
|
Initializes an environment handle |
|
|
Returns an Oracle error message |
|
|
Returns an already allocated handle |
|
|
Deallocates a handl |
|
|
Initializes the OCI environment |
|
|
Reads part of a LOB into a buffer stream |
|
|
Writes a buffer stream into a LOB |
|
|
Logs off an Oracle database connection |
|
|
Creates an Oracle logon connection |
|
|
Executes a prepared SQL statement |
|
|
Fetches rows of data |
|
|
Prepares a SQL statement |
|
|
Commits a transaction |
|
|
Rolls back a transaction |
|
|
Starts a transaction |
|
|
Establishes an access path to a data source |
|
|
Deletes access to a data source |
|
|
Creates a user session on the server |
You’ll find complete documentation on all of the OCI calls and other important programming information on the following web pages:
- http://technet.oracle.com/tech/oci/
The main home page for all things OCI on the Oracle Technology Network (OTN).
- http://technet.oracle.com/doc/server.815/a67846/toc.htm
The comprehensive Oracle Call Interface Programmer’s Guide for Oracle8i (Version 8.1.5) which includes full descriptions for the literally hundreds of OCI functions available.
Our focus in this chapter is on accessing OCI from Perl’s
excellent connectivity tools. What if you want to go head to head
with OCI without an intermediary? In addition to looking at the
documentation listed above, you will learn a lot by examining
existing code wherever possible, and by taking liberal advantage of
the samples you’ll find in the
$ORACLE_HOME/rdbms/demo directory.
For more serious, full-blown examples of OCI programming, you might want to check out the code for some of the Oracle applications and tools we’ll describe in later chapters of this book. Virtually all of them use OCI if you dig deep enough within the source code.
Tip
If you’re working with Oracle 8.1.5 on
Linux, be sure to have the latest Version
8.1.5 patched up to 8.1.5.0.2, and note that some OCI samples have
trouble compiling unless you’re using at least Red Hat 5.2.
Unfortunately, there were a lot of changes in
glibc
between some
versions of Red Hat, necessitating a number of compatibility
libraries (which basically map old glibc calls
to new ones). There is a
glibc
patch from Oracle to cover this; the
easiest way to find the latest version of this patch is to check on
the http://technet.oracle.com web
site. Just carry out a search on “glibc”.
[16] The SQL Access Group was a consortium of vendors formed in the early 1990s. Their CLI (Call Level Interface) was an API attempt to support SQL across disparate systems.