The most popular programmatic interface to Oracle is embedded SQL. However, Oracle also provides an alternative callable API, known as the Oracle Call Interface, or OCI. OCI has been available for many years and remained fairly stable through a number of major Oracle upgrade cycles, including all of the Oracle7 versions. With the introduction of Oracle8, OCI underwent a major revision, and many of the original OCI calls were replaced by new, improved versions. Moving forward with Oracle9 and beyond, this “new OCI” (the Oracle8 version) is effectively the Oracle Call Interface for new programs.
The “old OCI”(from Oracle7 and before) is relevant only for legacy programs that were originally developed using it. For reference, the “old OCI” routines are summarized in Table 19-6, so that you can recognize a program that may be using this old version. Conceptually, the routines closely parallel the embedded dynamic SQL interface, described in Chapter 18.
The new OCI uses many of the same concepts as the SQL/CLI standard and ODBC, including the use of handles to identify interface objects. Several hundred routines are defined in the API, and a complete description of them is beyond the scope of this book. The following sections identify the major routines that will be used by most application programs and their functions.
1. OCI Handles
The new OCI uses a hierarchy of handles to manage interaction with an Oracle database, like the handle hierarchy of the SQL/CLI described earlier in the section “CLI Structures” The handles are:
- Environment handle. The top-level handle associated with an OCI interaction
- Service context handle. Identifies an Oracle server connection for statement processing
- Server handle. Identifies an Oracle database server (for multisession applications)
- Session handle. Identifies an active user session (for multisession applications)
- Statement handle. Identifies an Oracle-SQL statement being processed
- Bind handle. Identifies an Oracle statement input parameter
- Define handle. Identifies an Oracle query results column
- Transaction handle. Identifies a SQL transaction in progress
- Complex object handle. Retrieves data from an Oracle object
- Error handle. Reports and processes OCI errors
An application program manages OCI handles using the routines shown in Table 19-7. The allocate and free routines function like their SQL/CLI counterparts. The get attribute and set attribute functions operate like the similarly named SQL/CLI routines that get and set environment, connection, and statement attributes.
An error handle is used to pass information back from OCI to the application. The error handle to be used for error reporting is typically passed as a parameter to OCI calls. If the return status indicates an error, information about the error can be retrieved from the error handle using OCIErrorGet().
2. Oracle Server Connection
The initialization and connection sequence for OCI parallels those already illustrated for CLI/ODBC and dblib. The OCI routines associated with connection management are shown in Table 19-8. An application program first calls OCIInitialize() to initialize the Oracle Call Interface. This call also indicates whether OCI will be used in multithreaded mode, whether the application program will use OCI object-mode functions, and other options. After initialization, the application program calls OCIEnvInit() to initialize an environment handle. As with CLI/ODBC, all OCI interactions take place within the context of the environment defined by this handle.
After these initial steps, most applications call OCILogon() to establish a session with an Oracle database server. Subsequent OCI calls take place within the context of this session and use the supplied user-id to determine their privileges within the Oracle database. A call to OCILogoff() terminates the session. The other calls provide more advanced session management for multithreaded and multiconnection applications. The OCIServerVersion() call can be used to determine the version of the Oracle server software. The OCIChangePassword() call can be used to change an expired password.
3. Statement Execution
The OCI functions shown in Table 19-9 implement SQL statement execution. OCIStmtPrepare() and OCIStmtExecute() support the two-step prepare/execute process. The OCIStmtExecute() function can also be used to describe query results (similar to the embedded SQL DESCRIBE statement) without actually executing the query by passing a specific flag. OCI automatically provides a description of query results when OCIStmtExecute() is called in the normal statement execution mode. The description is available as an attribute of the statement handle for the executed query.
The OCIBindbyPos() and OCIBindbyName() functions are used to bind application program locations to statement parameters, using either parameter positions or parameter names. These calls automatically allocate bind handles for the parameters when they are called, or they may be called with explicitly allocated bind handles. The other calls implement more advanced binding techniques, including binding of multiple parameter values (arrays) and binding of complex object data types. They also provide execute-time parameter (and query results) processing, corresponding to the deferred parameter mode supported by CLI/ODBC and described earlier in the “CLI Statement Processing” section. The piece info calls support this mode of operation.
4. Query Results Processing
The OCI functions shown in Table 19-10 are used to process query results. The OCIDefineByPos() function is used to bind a query results column (identified by column number) to an application program storage location. (The OCI terminology refers to this as the define process; the term binding is reserved for input parameters.) The other define calls support dynamic (execute-time) binding, array binding (for multirow fetch operations), and binding of complex object data types. The OCIStmtFetch() call retrieves a row of query results, and provides the SQL FETCH statement functionality.
5. Descriptor Handling
OCI uses descriptors to provide information about parameters, Oracle database objects (tables, views, stored procedures, and so on), large objects, complex objects, row-ids, and other OCI objects. A descriptor provides information to the application program and is used in some cases to manage the details of the processing of these objects. The routines shown in Table 19-11 are used to manage descriptors. They allocate and free the descriptors and retrieve and set individual data values within the descriptors.
6. Transaction Management
Application programs use the functions shown in Table 19-12 to implement SQL transaction management. The OCITransCommit() and OCITransRollback() calls provide the basic capability to commit and roll back transactions, and correspond to the usual SQL COMMIT and ROLLBACK statements. The other functions provide a very rich and complex transaction scheme, including the specification of read-only, serializable, and loosely or tightly coupled transactions, and control over distributed transactions. The transaction management routines take a service context handle that identifies a current connection as an input parameter.
6.1. Error Handling
The OCI functions return a status code indicating whether they completed successfully. In addition, most OCI functions accept an error handle as an input parameter. If an error occurs during processing, error information is associated with this handle. Upon return from the function, the application program can call OCIErrorGet() on the error handle to obtain further information about the error, including the error number and error message.
6.2. Catalog Information
The OCIDescribeAny() call provides access to Oracle system catalog information.
An application program calls this routine with the name of a table, view, synonym, stored procedure, data type, or other Oracle schema object. The routine populates a descriptor (identified by a descriptor handle) with information about the attributes of the object. Subsequent calls to OCIAttrGet() on the descriptor handle can be used to obtain complete data about the object at runtime.
6.3. Large Object Manipulation
OCI includes a large group of routines, shown in Table 19-13, for processing Oracle large object (LOB) data types and large objects stored in files referenced in Oracle columns. Because large objects may be tens of thousands to millions of bytes in length, they typically cannot be bound directly to application program buffers in their entirety. Instead, OCI uses a LOB locator, which functions like a handle for the LOB data item.
The locator is returned for LOB data in query results and used as an input parameter for LOB data being inserted or updated. The LOB handling routines support piece-by-piece processing of LOB data, allowing it to be transferred between an Oracle database and an application program. The routines accept one or more LOB locators as parameters.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.