Frequently Asked Questions


Information Integration

OCI

OCI -- Objects

OCI -- Threads

OCI -- Large Objects LOB and FILEs

OCI -- Transactions

OCI -- Transparent Application Failover

OCI -- Compatibility

OCI -- Instant Client

Answers

What is the Oracle Call Interface (OCI)?

Oracle8i Oracle Call Interface (OCI) is the most comprehensive, commercially-available interface that completely addresses all requirements of high performance, secure, scalable middle tier mission-critical solutions that integrate well with an Oracle server. Using OCI, an application can service increasing number of users and requests without additional hardware investments.

OCI is an application programming interface (API) that allows an application developer to use C procedures or function calls to access the Oracle data server and control all phases of business logic execution. The OCI provides a library of standard database access and retrieval functions in the form of a dynamic runtime library, that can be linked in by the application. OCI in Oracle8i delivers a fundamentally new programming paradigm which improves application performance and scalability through efficient usage of system memory and network connectivity, provides a consistent interface for session and transaction management in a two tier client-server or multi-tier environment, and provides comprehensive support for application development using Oracle8i object features.

Oracle8i OCI builds on the secure foundation established by Oracle8 OCI. It has features that address the following primary areas:

  • Scalability
  • N-tiered authentication (that is, authentication through a middle tier)
  • Dynamic connection and session management
  • Asynchronous event notification
  • Access to other databases
  • Performance

   Modified: 02-APR-01    Ref #: ID-2326


What is a service context handle?

A service context handle defines attributes that determine the operational context for OCI calls to a server. You must allocate and initialize the service context handle with OCIHandleAlloc() or OCILogon() before you can use it.

The service context contains three additional handles that represent a server connection, a user session, and a transaction.

  • A server handle identifies a data source. It translates into a physical connection in a connection-oriented transport mechanism.
  • A user session handle defines a user's roles and privileges (also known as the user's security domain), and the operational context on which the calls execute.
  • A transaction handle defines the transaction that the SQL operations modify.

   Modified: 23-JUL-02    Ref #: ID-2394


What is a handle?

Almost all OCI calls include in their parameter list one or more handles. A handle is an opaque pointer to a storage area allocated by the OCI library. A handle can be used to store context or connection information, (for example, an environment or service context handle), or it can store information about other OCI functions or data (for example, an error or describe handle).

Handles can make programming easier, because the library, rather than the application, maintains this data. Most OCI applications needs to access the information stored in handles. The get and set attribute OCI calls, OCIAttrGet() and OCIAttrSet(), access this information.

   Modified: 23-JUL-02    Ref #: ID-2358


Why is Oracle8i OCI a better API for a scalable, multi-threaded application than Open Database Connectivity (ODBC) 3.0?

The only advantage of ODBC is that it is practically vendor neutral. However, this vendor neutrality reduces the flexibility that a native interface such as OCI allows. ODBC is a "least common denominator" interface and limits a scalable, high performance architecture.

Oracle8i OCI is a much better choice than ODBC for the following reasons:

  • OCI is optimized for queries. Transparent prefetch buffers reduce round-trips and improve performance and scalability. As a result, there is reduced memory usage on the server.
  • OCI is optimized for round-trips. No-reply requests are batched until the next call is generated for the server. This allows certain calls to be lazily propagated.
  • OCI is thread safe. You do not need to mutex (use mutual exclusivity locks) any of the OCI handles. ODBC is not thread safe, so you have to mutex most data structures.
  • OCI provides an asynchronous event notification API for active databases.
  • OCI provides enhanced array data manipulation language (DML) operations that reduce round-trips.
  • OCI returns ROWIDs for all rows selected for update in a single round-trip. Using ROWID allows for more efficient SQL access.
  • ODBC has no concept of sessions. OCI decouples connections, sessions and transactions. Multiple users can use a single connection; they get serialized on the connection for SQL operations. Multiple transactions can exist per user. This allows users to scale and service more users than there are connections available. Sessions and transactions can be migrated between connections to the same server.
  • ODBC does not support object types, large objects (LOBs), and other new Oracle datatypes.
  • ODBC affects server scalability. Using ODBC and having n number of concurrent users forces the server to have n number of processes service the clients if Oracle8i is operating in dedicated server mode. Your operating system may or may not support so many connections and processes.
  • ODBC is a wrapper around OCI so it is slower.

Note: Some of these comments may or may not be applicable to your particular application. For example, if an application invocation is always dedicated for a user, then sessions, transactions, multiplexing, and multi-threading are not issues.

   Modified: 23-JUL-02    Ref #: ID-2329


I am currently setting the value_sz parameter in OCIDefineByPos() to SB4MAXVAL(2147483647) which works successfully. What is the difference between setting value_sz to a small versus a large value? Are internal buffers created and is it more efficient to use small values? Why is this parameter necessary?

The value_sz parameter is used to truncate the data. For any data fetched piecewise, the sum of all pieces can at most be value_sz bytes. For long columns, the server returns up to value_sz bytes. If the long is fetched:

  • piecewise, then the internal buffer size is equal to the size of the largest piece that is specified any time
  • in one single-piece, then the internal buffer is value_sz long

For non-long columns, the internal buffer is equal to the maximum column size for that column, so value_sz has no effect.

   Modified: 23-JUL-02    Ref #: ID-2357


What is the significance of the numbers 21, 33, 52, and 100 in the source code example, CDEMO81.c in Oracle Call Interface Programmer's Guide, Volume II?

The source code example CDEMO81.c in Oracle Call Interface Programmer's Guide, Volume II has several calls to OCIEnvInit() and OCIHandleAlloc() that specify values for the xtramem_sz parameter. For example:

  • OCIEnvInit (... OCI_DEFAULT, 21, ...);
  • OCIHandleAlloc (... OCI_HTYPE_ERROR, 33, ...);
  • OCIHandleAlloc (... OCI_HTYPE_SERVER, 52, ...);

The numbers in the example are random sizes. However, they are available to you to allocate any user defined structure(s) that has the same life time as the handle, so that freeing the handle also frees up the user data structures. When such memory allocation is completed, the handle free call also deallocates this additional piece of memory. The size is the size of the data structure that you want to allocate and usrmempp returns the pointer to the data structure.

   Modified: 27-APR-01    Ref #: ID-2359


What is the default value of OCI_ATTR_TOP_RCNT?

The default value is 1, that is, one extra row per fetch. If you are not using array fetch and set the value to 1, it reduces the round-trips for fetching a result set to half the number of rows. Setting the value to 1 also helps cancel the result set when there is just one row to be fetched.

   Modified: 27-APR-01    Ref #: ID-2361


Is the use of OCI_UCS2ID supported outside of varying width CLOBs? That is, can it be used as a client character NCHAR character set? If it can be used like this, does this cause an Endian problem during an import?

OCI_UCS2ID is not a valid character set for any Oracle8i character parameters. Oracle8i character sets are NLS_LANG and NLS_NCHAR, that is, the database character set and national character set, respectively.

OCI_UCS2ID is used to signal the encoding of bind or define buffers on the OCI or Pro*C client to be Unicode encoded. The data then gets converted to the database character set. Currently, there is no storage format for UCS2 on the Oracle8i database and it is not an interchangeable format. Therefore there is no Endian problem.

Note: 'utext' (ub2) is the client side C data type to hold UCS2 data. OCI_UCS2ID assumes the buffer to be in 'utext' format and therefore chooses the Endian of the underlying platform.

   Modified: 27-APR-01    Ref #: ID-2363


What happens if there are multiple client components (.so) within a process linked with different database versions?

Multiple components linked with multiple client side libraries in a single process are not supported.

   Modified: 27-APR-01    Ref #: ID-2364


What are the main steps when building a Pro*C or Pro*C++ application?

There are three main steps when building a C or C++ application:
  1. Define the object types that correspond to the application objects.
  2. Execute the SQL data definition language (DDL) statements to populate the database with the necessary object types.
  3. To manipulate instances of these object types in a C program, the object types must be represented in the host program language format. This is accomplished by representing the object types as C structs. Oracle Corporation provides a tool called Object Type Translator (OTT) that can be used to generate the C mapping of the object types. OTT puts the equivalent C structs in header (.h) files. The application developer includes these .h files in the .c files containing the C functions implementing the application.

  4. Compile and link the application's .c files with the OCI library to build the application executable.

   Modified: 27-APR-01    Ref #: ID-2362


Can you explain the uses of handles through an example?

This section presents one possible scenario for an application that is managing multiple user, multiple server connections, and multi-threading. This example is intended to help you understand some of the issues involved in programming such an application.

A Connection Example

An application is supporting two users, User1 and User2. The application has completed the following steps:

  • initialized the OCI process in OCI_THREADED mode with a call to OCIInitialize()
  • allocated a single environment handle with OCIEnvInit()
  • in two different threads, connected to two different databases, DB1 and DB2, residing on the same computer

User1 performs the following actions:

  1. Attaches to DB1.
  2. Starts two new transactions, TX1 and TX2.
  3. Prepares and executes a statement in each transaction at the same time in different threads (STMT1 in TX1, and STMT2 in TX2).
  4. Commits TX1 and TX2.
  5. Detaches from DB1.

User2 performs the following actions:

  1. Attaches to DB2.
  2. Starts two new transactions, TX3 and TX4.
  3. Prepares and executes a statement in each transaction at the same time (STMT3 in TX3, and STMT4 in TX4).
  4. Commits TX3 and TX4.
  5. Detaches from DB2.

The following questions and answers relate to the previous connection examples:

How many server handles are required?

Even though DB1 and DB2 reside on the same server computer, two server handles are required. Each server handle represents a database connection, and is identified by its own connect string.

How many service context handles are required?

Four service context handles are required. Each user is executing two transactions simultaneously, so each requires its own service context. Therefore 2 users x 2 transactions = 4 service context handles. If each user had executed the statements in the same transaction, each would require only a single service context.

How many user session handles are required?

Four user session handles are required. Each user needs a user session handle on each server. If each user executed their statements serially, then two sessions would be sufficient. Note that user session handles used to be called authentication handles.

How many transaction handles are required?

Four transaction handles are required; one for each concurrent transaction. However, the application could also take advantage of the implicit transaction created when database changes are made, and avoid allocating transaction handles altogether.

Could the example use multiple environment handles?

Yes. Since there are two databases involved, the application should use two environment handles so that accesses to each database can be completely concurrent.

If a single user in a single environment wants to execute four different statements on 4 transactions concurrently against the same database, how many server handles are required?

Four server handles are required; one for each concurrent transaction. There can be at most a single outstanding call on any one server handle at a time.

   Modified: 27-APR-01    Ref #: ID-2360


Should OCI_PRELIM_AUTH be used with SYSDBA or SYSOPER?

Yes. OCI_PRELIM_AUTH just means that you do not need a user session handle.

Connecting as SYSDBA means that you have OSDBA privilege or you are granted SYSDBA privilege and you can later connect to the SYS schema after starting the database. SYSOPER connection needs OSOPER privilege or you are granted SYSOPER privilege and this privilege only lets you start or stop the database and you cannot create user session handles.

   Modified: 20-APR-01    Ref #: ID-2395


Are there equivalent settings in OCI for the following PRO*C Precompliation Options: ( precompiler directives) - a) MAX_OPEN_CURSORS, b) HOLD_CURSOR, and c) RELEASE_CURSOR. If there is none, can these settings be easily achieved by the developer ( he does not want to use PRO*C, but wants the benefits of these PRO*C settings)

Here is how you can simulate the Pro*C behavior.

MAX_OPEN_CURSORS: In your OCI application cache statement handles and reuse them.

HOLD_CURSOR: Don't free the statement handle. Reuse the statement handle for executing more statements.

RELEASE_CURSOR: Free the statement handle.

   Modified: 17-APR-01    Ref #: ID-2318


With the release of Oracle8i are there any new reasons to choose OCI instead of Pro*C to develop applications?

The key distinctions between OCI and Pro*C are:

  • OCI provides more fine grained control over all aspects of application design. For example, connecting to servers, multiplexing and migrating sessions and transactions over these connections, and performing piecewise operations on SQL data using callbacks, and so on. Pro*C is more suited for pure SQL access functionality. However, OCI calls can be embedded in a Pro*C application.
  • OCI provides dynamic bind and define using callbacks that can be used to bind and define any arbitrary structure including lists.
  • OCI provides a rich describe functionality to drill down any server metadata.
  • OCI allows asynchronous event notification to be received by a client application. It allows clients to register an interest in such notifications. It also provides a way for clients to generate a notification for propagation to other clients.
  • OCI provides an enhanced array data manipulation language (DML) capability that allows for array INSERTS, UPDATES, and DELETEs to complete as many iterations as possible before returning a batch of errors.
  • OCI allows users to piggyback a commit request on an execute to reduce the round-trips.

   Modified: 06-APR-01    Ref #: ID-2328


What is user level connection pooling and can we honestly claim OCI connection pooling is more efficient than something developed by customers for their specific environment (hardware and software)?

OCI connection pooling, a new feature of Oracle9i, optimizes the usage of the physical connections. Since it is internal to OCI, a connection is locked exactly for duration that is needed. So concurrency goes up.

   Modified: 16-MAR-01    Ref #: ID-2320


How does OCI connection pooling compare with alternatives such as JDBC, transaction monitors (Tuxedo, etc), Oracle's MTS (shared servers), Oracle's network connection pooling, and so on? And how does a customer decide which is best option for their own circumstance?

OCI connection pooling is faster than standard (thin) JDBC access. Since the thick JDBC driver uses OCI, it exposes connection pooling.

There are bottlenecks in MTS and and network layer wrt concurrency. When the threads are really high, they get serialized or timed out. OCI Connection Pooling handles the concurrency well.

   Modified: 16-MAR-01    Ref #: ID-2321


Are there any sample programs demonstrating OCI connection pooling usage?

The following sample program illustrates the API usage...
#include 

#define MAXTHREAD 10

static OCIError   *errhp;
static OCIEnv     *envhp;
static OCICPool   *poolhp;

static int employeeNum[MAXTHREAD];

static OraText *poolName;
static sb4 poolNameLen;
static text *database = (text *)"";
static text *username =(text *)"SCOTT";
static text *password =(text *)"TIGER";
static text *appusername =(text *)"APPUSER";
static text *apppassword =(text *)"APPPASSWD";

static ub4 conMin = 2;
static ub4 conMax = 5;
static ub4 conIncr = 1;

static void checkerr (OCIError *errhp, sword status);
static void threadFunction (dvoid *arg);

int main (void)
{
  int i = 0;
 
  OCIEnvCreate (&envhp, OCI_THREADED, (dvoid *)0,  (dvoid * (*)()) 0,
    (dvoid * (*)()) 0, (dvoid (*)()) 0, 0, (dvoid *)0);

  (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR,
                   (size_t) 0, (dvoid **) 0);


  (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **) &poolhp, OCI_HTYPE_CPOOL,
                        (size_t) 0, (dvoid **) 0);

  /* CREATE THE CONNECTION POOL */
  checkerr (errhp, OCIConnectionPoolCreate(envhp, 
                   errhp,poolhp, &poolName, &poolNameLen,
                   database,strlen(database),
                   conMin, conMax, conIncr,
                   appusername,strlen(appusername),
                   apppassword,strlen(apppassword),OCI_DEFAULT));

  /* Multiple threads using the connection pool */
  {
    OCIThreadId     *thrid[MAXTHREAD];
    OCIThreadHandle *thrhp[MAXTHREAD];

    OCIThreadProcessInit ();
    checkerr (errhp, OCIThreadInit (envhp, errhp));
    for (i = 0; i < MAXTHREAD; ++i)
    {
      checkerr (errhp, OCIThreadIdInit (envhp, errhp, &thrid[i]));
      checkerr (errhp, OCIThreadHndInit (envhp, errhp, &thrhp[i]));
    }
    for (i = 0; i < MAXTHREAD; ++i)
    {
      employeeNum[i]=i;
      checkerr (errhp, OCIThreadCreate (envhp, errhp, threadFunction, 
        (dvoid *) &employeeNum[i], thrid[i], thrhp[i]));
    }
    for (i = 0; i < MAXTHREAD; ++i)
    {
      checkerr (errhp, OCIThreadJoin (envhp, errhp, thrhp[i]));
      checkerr (errhp, OCIThreadClose (envhp, errhp, thrhp[i]));
      checkerr (errhp, OCIThreadIdDestroy (envhp, errhp, &(thrid[i])));
      checkerr (errhp, OCIThreadHndDestroy (envhp, errhp, &(thrhp[i])));
    }
    checkerr (errhp, OCIThreadTerm (envhp, errhp));
  } /* ALL THE THREADS ARE COMPLETE */

  checkerr(errhp, OCIConnectionPoolDestroy(poolhp, errhp, OCI_DEFAULT));
  checkerr(errhp, OCIHandleFree((dvoid *)poolhp, OCI_HTYPE_CPOOL));
  checkerr(errhp, OCIHandleFree((dvoid *)errhp, OCI_HTYPE_ERROR));
} /* end of main () */

static void threadFunction (dvoid *arg)
{ 
  int empno = *(int *)arg;
  OCISvcCtx *svchp = (OCISvcCtx *) arg;
  text insertst1[256];
  OCIStmt *stmthp = (OCIStmt *)0;

  checkerr(errhp,OCILogon2(envhp, errhp, &svchp, 
                        (CONST OraText *)username, strlen(username), 
                        (CONST OraText *)password, strlen(password), 
                        (CONST OraText *)poolName, poolNameLen,
                        OCI_CPOOL));

  sprintf(insertst1,"INSERT INTO emp(empno, ename, job, sal, deptno) values\
          (%d,'abc','MANAGER',122,20)",empno);

  OCIHandleAlloc(envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, (size_t)0,
                 (dvoid **)0);

  checkerr(errhp, OCIStmtPrepare (stmthp, errhp, (text *)insertst1,
           (ub4)strlen(insertst1), OCI_NTV_SYNTAX, OCI_DEFAULT)); 

  checkerr(errhp, OCIStmtExecute (svchp, stmthp, errhp, (ub4)1, (ub4)0,
           (OCISnapshot *)0, (OCISnapshot *)0, OCI_DEFAULT ));

  checkerr(errhp, OCITransCommit(svchp,errhp,(ub4)0));

  checkerr(errhp, OCIHandleFree((dvoid *) stmthp, OCI_HTYPE_STMT));
  checkerr(errhp, OCILogoff((dvoid *) svchp, errhp));
} /* end of threadFunction (dvoid *) */

void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
  text errbuf[512];
  sb4 errcode = 0;

  switch (status)
  {
  case OCI_SUCCESS:
    break;
  case OCI_SUCCESS_WITH_INFO:
    (void) printf("Error - OCI_SUCCESS_WITH_INFO\n");
    break;
  case OCI_NEED_DATA:
    (void) printf("Error - OCI_NEED_DATA\n");
    break;
  case OCI_NO_DATA:
    (void) printf("Error - OCI_NODATA\n");
    break;
  case OCI_ERROR:
    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                        errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    (void) printf("Error - %.*s\n", 512, errbuf);
    break;
  case OCI_INVALID_HANDLE:
    (void) printf("Error - OCI_INVALID_HANDLE\n");
    break;
  case OCI_STILL_EXECUTING:
    (void) printf("Error - OCI_STILL_EXECUTE\n");
    break;
  case OCI_CONTINUE:
    (void) printf("Error - OCI_CONTINUE\n");
    break;
  default:
    break;
  }
}

   Modified: 16-MAR-01    Ref #: ID-2322


Can an Oracle Database connect via a dblink to any older Oracle database?

As a general rule, any server can connect to any supported (primary error correction) older server. This means that Oracle8, 8i, and 9.0.1 can connect (via a dblink) to an Oracle 7.3.4 or Oracle8 database. However, since Oracle 7.3.4 was desupported before Oracle9i Release 2 (9.2) was released, 9.2 does not support Oracle 7.3.4.

Beware there are some issues with Oracle8i clients and databases connecting to an Oracle 7.3.4 database. If you experience a hang or other error, search for note 68059.1 in metalink.

   Modified: 23-JUL-02    Ref #: ID-2474


Is the Borland (or other compilers) supported with OCI?

The OCI calls are implemented in dynamic link libraries (DLLs) that Oracle provides. The DLLs are located in the ORACLE_BASE\ORACLE_HOME\bin directory and are part of the Required Support Files (RSFs).

To use the Oracle DLLs to make OCI calls, you can either dynamically load the DLL and function entry points, or you can link your application with the import library oci.lib. Oracle only provides the oci.lib import library for use with the Microsoft Compiler. To use other compilers with an import library, customers must generate your own import library (typically there is a utility shipping with the compiler that can generate this library).

Other compilers, though likely compatible with the Oracle DLLs, are not directly supported by Oracle. Customers must recreate the problem using the MS Compiler before calling support.

   Modified: 14-FEB-02    Ref #: ID-2497


I've been told Oracle9i ships with Oracle8 OCI libraries. Is this correct?

The versions of the OCI libraries shipped with the client are the same as the client version. So, for example, Oracle9i Release 2 (9.2) ships with the 9.2 release of the OCI libraries. The confusion stems from the fact that we use the terms OCI7 and OCI8 to refer to the OCI API. In Oracle8, the OCI API was changed. The new API was called OCI8 and the old one was then called OCI7. All versions of the Oracle OCI client libraries since Oracle8, including 9.2 support both the OCI7 and OCI8 APIs. We currently plan to support both these APIs in all future releases.

   Modified: 31-MAY-02    Ref #: ID-2428


What is the difference between using OCILogon() and OCILogon2()?

OCILogon2() is a newer version of OCILogon() with the added capability of using connections in a connection pool. A mode parameter can be used during logon for connection pooling, session pooling, statement caching, and proxy connections. OCILogon() can thus do a subset of OCILogon2().

   Modified: 05-NOV-03    Ref #: ID-3841


How can I use OCI8i to work with objects?

Oracle8i 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 attributes `John', `Bonivento', and `30'. Object types may also contain methods--programmatic functions that represent the behavior of that object type.

OCI provides functions for managing database access and processing SQL statements. The SQL capabilities of the OCI relational interface allow an application to access objects from an Oracle8i database through SQL statements.

OCI allows applications to access any of the datatypes found in the Oracle8i database, including scalar values, collections, and instances of any object type. In order to take full advantage of Oracle8i server object capabilities, most applications need to do more than just access objects. Once the object has been retrieved, the application must navigate through references from that object to other objects. OCI provides the capability to do this. Through OCI's object navigational calls, an application can perform any of the following functions on Oracle8i objects:

  • creating, accessing, locking, deleting, copying, and flushing objects
  • getting references to the objects and their meta-objects
  • dynamically getting and setting values of objects' attributes

OCI also provides the ability to access type information stored in an Oracle8i database. The OCIDescribeAny() function enables an application to access most information relating to types stored in the database, including information about methods, attributes, and type meta-data.

Applications interacting with Oracle8i objects need a way to represent those objects in a host language format. Oracle8i provides a utility called the Object Type Translator (OTT), which can convert type definitions in the database to C struct declarations. The declarations are stored in a header file that can be included in an OCI application. When type definitions are represented in C, the types of attributes are mapped to special C variable types that are new to Oracle8i.

OCI includes a set of datatype mapping and manipulation functions that enable an application to manipulate these datatypes, and thus manipulate the attributes of objects.

   Modified: 06-APR-01    Ref #: ID-2330


What is the object cache?

Throughout the following questions and answers, you will see many references to the object cache. The following is a brief description of this important OCI feature.

The OCI runtime environment provides an object cache for caching objects in memory to support high-performance navigational access of objects. The object cache supports references (REFs) to database objects in the object cache, the database objects can be looked up (that is, pinned) through their references.

Applications are not impacted by memory allocation when database objects are loaded into the cache. Thus the object cache provides transparent and efficient memory management for database objects. Also, when database objects are loaded into the object cache they are transparently mapped into the host language representation. For example, in C programming language the database object is mapped to its corresponding C structure. The object cache maintains the association between the object copy in the cache to the corresponding database object. Upon transaction commit, changes made to the object copy in the cache are automatically propagated to the database.

The object cache maintains a fast look-up table for mapping REFs to objects. When an application dereferences a REF and the corresponding object is not yet cached in the object cache, the object cache automatically sends a request to the server to fetch the object from the database and load it into the object cache. Subsequent dereferences of the same REF will be faster since they become local cache access and do not incur network round-trips.

To notify the object cache that an application is accessing an object in the object cache, the application pins the object; when it is done with the object, it unpins it. The object cache maintains a pin count for each object in the cache, the count is incremented upon a pin call and unpin call decrements it. When the pin count goes to 0, the object is no longer needed by the application. The object cache uses a least-recently used (LRU) algorithm to manage the size of the cache. The LRU algorithm frees candidate objects when the cache reaches the maximum size. The candidate objects are objects with a pin count of 0.

   Modified: 06-APR-01    Ref #: ID-2331


Tell me about navigational access by object-oriented programs.

There is also a need for navigational access by object-oriented programs. In the object-oriented programming paradigm, applications model their objects as a set of interrelated objects that form graphs of objects. The relationships between objects are implemented as references. An application processes objects by starting at some initial set of objects, using the references in these initial objects to traverse the remaining objects, and performing computations on each object. This style of access to objects is known as navigational access to objects. Oracle8i OCI provides an API for navigational access to objects. Specifically, the following object capabilities have been added for navigational access:

  • a client side object cache is provided for caching objects in memory.
  • support for dereferencing an object reference and pinning the corresponding object in the object cache. The pinned object is transparently mapped in the host language representation.
  • support for informing the object cache when the pinned object is no longer needed.
  • support for fetching a graph of related objects from the database into the client side object cache in one call.
  • support for locking objects.
  • support for creating, updating, and deleting objects in the object cache
  • support for flushing changes made to objects in the client side object cache to the database.

   Modified: 09-APR-01    Ref #: ID-2332


Does OCI provide an external function to free unreferenced objects in the client-side object cache?

No. OCI does not provide an external function to free unreferenced objects in the client-side object cache. OCI does provide a function, OCIObjectFree(), that frees the memory of an object specified by the caller.

Freeing an object copy removes it from the object cache and frees up its memory. The client-side object cache supports two methods for freeing up memory:

  1. Explicit freeing - A program explicitly frees or removes an object copy from the object cache by calling OCIObjectFree(), which takes an option to (forcefully) free either a marked or pinned object copy. The program can also call OCICacheFree() to free all object copies in the object cache.
  2. Implicit freeing - Should the object cache begin to run out of memory, it implicitly frees object copies that are both unpinned and unmarked. Unpinned objects that are marked are eligible for implicitly freeing only when the object copy is flushed or unmarked.

For memory management reasons, it is important that applications unpin objects when they are no longer needed. This makes these objects available for aging out of the object cache, and makes it easier for the object cache to free memory when necessary.

   Modified: 09-APR-01    Ref #: ID-2333


What controls the size of the client-side object cache?

The client-side object cache size is controlled by the following two attributes of the environment handle:

  • OCI_ATTR_CACHE_MAX_SIZE, the maximum object cache size
  • OCI_ATTR_CACHE_OPT_SIZE, the optimal object cache size

These parameters refer to levels of cache memory usage, and they help to determine when the object cache automatically ages out eligible objects to free up memory.

OCI_ATTR_CACHE_MAX_SIZE is specified as a percentage of OCI_ATTR_CACHE_OPT_SIZE. The maximum object cache size (in bytes) is computed by incrementing OCI_ATTR_CACHE_OPT_SIZE by OCI_ATTR_CACHE_MAX_SIZE percentage:

maximum_cache_size = optimal_size + optimal_size * max_size_percentage / 100

or

maximum_cache_size = OCI_ATTR_CACHE_OPT_SIZE + (OCI_ATTR_CACHE_OPT_SIZE * 
                          OCI_ATTR_CACHE_MAX_SIZE) / 100

The default value for:

  • OCI_ATTR_CACHE_MAX_SIZE is 10%.
  • OCI_ATTR_CACHE_OPT_SIZE is 200k bytes.

The object cache size attributes of the environment handle can be set with the OCIAttrSet() call and retrieved with the OCIAttrGet() function.

   Modified: 06-APR-01    Ref #: ID-2334


What are threads?

Threads are lightweight processes that exist within a larger process. Threads share the same code and data segments, but have their own program counters, machine registers, and stack. Global and static variables are common to all threads, and a mutual exclusivity mechanism may be required to manage access to these variables from multiple threads within an application.

Once spawned, threads run asynchronously to one another. They can access common data elements and make OCI calls in any order. Because of this shared access to data elements, a mechanism is required to maintain the integrity of data being accessed by multiple threads.

The mechanism to manage data access takes the form of mutexes (mutual exclusivity locks), which ensure that no conflicts arise between multiple threads that are accessing shared resources within an application. In Oracle8i OCI, mutexes are granted on a per-environment-handle basis.

   Modified: 20-APR-01    Ref #: ID-2391


If a client-side application is multi-threaded or multi-process, do all processes share a single client-side object cache or does each have its own object cache?

No. The application processes do not share the same client-side object cache. Each application process can have one or more client-side object caches. A client-side object cache is allocated for every OCI environment handle initialized in object mode. Multiple threads of a process can share the same client-side object cache by sharing the same OCI environment handle.

   Modified: 20-APR-01    Ref #: ID-2392


Can several threads or processes participate in a single session or transaction?

A multi-threaded client application must communicate to the OCI layer that the application is running in multi-threaded mode. This is done by specifying OCI_THREADED for the mode parameter of the opening call to OCIInitialize(), which must be the first OCI function called in the application.

After doing this, multiple threads can participate in a single session or transaction. However, the OCI library serializes concurrent requests on the same session or transaction. This is accomplished by the OCI library implicitly acquiring mutexes on handles shared by threads.

   Modified: 20-APR-01    Ref #: ID-2393


Can I pass a handle between threads? That is, can I pass control from one thread to another so that the second thread can call OCI using the handle that initially has been used by another thread?

Yes. You can pass handles among threads. A mutual exclusivity lock (mutex) is used internally by OCI on each handle to serialize access from multiple threads, but once the OCI call completes, the mutex is released and any other thread blocked on the mutex on that handle will proceed.

   Modified: 20-APR-01    Ref #: ID-2396


How can thread safety levels be set in OCI?

In order to take advantage of thread safety in Oracle8i OCI, an application must be running on a thread safe platform. Then the application must tell the OCI layer that the application is running in multi-threaded mode, by specifying OCI_THREADED for the mode parameter of the opening call to OCIInitialize(), which must be the first OCI function called in the application.


Note:

Applications running on non-thread-safe platforms should not pass a value of OCI_THREADED to OCIInitialize().


If an application is single-threaded, whether or not the platform is thread safe, the application should pass a value of OCI_DEFAULT to OCIInitialize(). Single-threaded applications that run in OCI_THREADED mode may incur performance hits.

If a multi-threaded application is running on a thread-safe platform, the OCI library manages mutexing for the application on a per-environment-handle basis. If the application programmer desires, this application can override this feature and maintain its own mutexing scheme. This is done by specifying a value of OCI_NO_MUTEX to the OCIEnvInit() call.

The following two scenarios are possible, depending on how many connections exist per environment handle, and how many threads are spawned per connection:

  1. If an application (running in OCI_THREADED mode) maintains multiple environment handles, each of which has one connection that can spawn multiple threads, you have the following options:
  2. If an application has multiple environment handles, but each only has one thread (one session exists per environment handle), no mutexing is required. However, this is essentially a multi-process model as OCI data structures cannot be shared across threads.

   Modified: 20-APR-01    Ref #: ID-2397


Is it feasible to use OCI release 7.3.3 in non-thread safe mode within a multi-threaded program?

No. If you have multiple threads of execution, then you must always call opinit(OCI_EV_TSF).

   Modified: 20-APR-01    Ref #: ID-2398


What level of locking should be maintained between the different threads after I have called opinit(OCI_EV_TSF)?

After you have called opinit(OCI_EV_TSF), if a connection is not shared between multiple threads, then you do not have to serialize calls going to that connection. If, however, more than one thread can make an OCI call on a connection, then you must serialize access to that connection.

   Modified: 20-APR-01    Ref #: ID-2399


Can I link an OCI release 7.3.x multi-threaded program with Oracle8i OCI and obtain correct multi-threading behavior without making any changes?

You do not have to make any changes. Your OCI release 7.3.x multi-threaded program should run when linked with Oracle8i OCI libraries.

   Modified: 20-APR-01    Ref #: ID-2400


What is a LOB locator?

A LOB (large object) is an Oracle datatype that can hold up to 4 GB of binary (BLOB) or character (CLOB) data. In the database, an opaque data structure called a LOB locator is stored in a LOB column of a database row, or in the place of a LOB attribute of an object. The locator serves as a pointer to the actual LOB value, which is stored in a separate location.

The OCI LOB locator is used to perform OCI operations against a LOB (BLOB or CLOB) or FILE (BFILE). OCI functions do not take actual LOB values as parameters; all OCI calls operate on the LOB locator. This descriptor--OCILobLocator--is also used for operations on FILEs.

The LOB locator is allocated with a call to OCIDescriptorAlloc(), by passing OCI_DTYPE_LOB as the type parameter for BLOBs or CLOBs, and OCI_DTYPE_FILE for BFILEs.


Warning:
The two LOB locator types are not interchangeable. When binding or defining a BLOB or CLOB, the application must take care that the locator is properly allocated using OCI_DTYPE_LOB. Similarly, when binding or defining a BFILE, the application must be sure to allocate the locator using OCI_DTYPE_FILE.

An OCI application can retrieve a LOB locator from the server by issuing a SQL statement containing a LOB column or attribute as an element in the select list. In this example, the application first allocates the LOB locator and then uses it to define an output variable. Similarly, a LOB locator can be used as part of a bind operation to create an association between a LOB and a placeholder in a SQL statement. The LOB locator datatype (OCILobLocator) is not a valid datatype when connected to an Oracle7 database.

   Modified: 23-APR-01    Ref #: ID-2401


What is the size of the Oracle8i LOB locator?

If the LOB contents are smaller than 4 K:
  • Oracle8i stores the LOB in the row containing the LOB column. This is because for smaller LOBs there is no extra disk input/output (I/O).
If the LOB contents are larger than 4 K:
  • the LOB column stores a LOB locator (20 bytes) and some additional information for efficient LOB access. If the storage option for the LOB specifies out-of-row storage (that is, the LOB is always stored in a block different from the containing row), then only the LOB locator is stored in the row, which is 20 bytes long.

   Modified: 23-APR-01    Ref #: ID-2402


How many bytes are used when the LOB column value is NULL?

When a LOB column is NULL, only the NULL indication is stored, which is 1 byte.

   Modified: 23-APR-01    Ref #: ID-2403


Are there any other things to note when using a binary FILE (BFILE) other than it is read-only?

Yes. In order to associate an operating system file to a BFILE, it is necessary to first create a DIRECTORY object, which is an alias for the full pathname to the operating system file. The READ privilege on the DIRECTORY object allows you to read files located under that directory. The creator of the DIRECTORY object automatically earns the READ privilege.

It is important to note that the READ privilege is defined only on the DIRECTORY object. The physical directory that it represents may or may not have the corresponding operating system privileges (READ in this case) for the Oracle Server process. It is the database administrator's (DBA's) responsibility to ensure that the physical directory exists, and read permission for the Oracle Server process is enabled on the file, the directory, and the path leading to it. It is also the DBA's responsibility to make sure that the directory remains available, and the read permission remains enabled, for the entire duration of file access by database users.

The READ privilege just implies that as far as the Oracle8i database is concerned, you may read from files in the directory. These privileges are checked and enforced by the PL/SQL DBMS_LOB package and OCI APIs at the time of the actual file operations.

   Modified: 23-APR-01    Ref #: ID-2404


What happens when the operating system file changes? For example, if I delete the file and do not change anything on the BFILE field.

If you delete the operating system file, nothing happens to the BFILE field in the database. If the file is deleted while it is being used, the next operation on the file produces an error. If the deletion occurs after the file usage is complete, your next attempt at FILEOPEN fails.

   Modified: 23-APR-01    Ref #: ID-2405


What happens if the DIRECTORY objects are dropped or replaced when the database is in operation?

DIRECTORY objects should not be arbitrarily dropped or replaced when the database is in operation. If this happens, DBMS_LOB or OCI operations from all sessions on all files associated with this directory object fails. Further, if a DROP or REPLACE command is executed before these files are successfully closed, the references to these files will be lost in the programs, and system resources associated with these files are not released until the session or sessions are shut down.

   Modified: 23-APR-01    Ref #: ID-2406


Can I put several LOBs in the same block or do I have to put each LOB in its own block?

For small LOBs that are stored in the row, it is possible that multiple LOB values can share the same block.

For larger LOBs, the LOB values are stored outside the row. In this case, each LOB starts in a different chunk. Therefore for large LOBs, at most a chunk can be unused. It is not recommended to mix pieces of several LOBs into one chunk because this destroys the locality and contiguity for each LOB data, which degrades the I/O performance.


Note:
A chunk is different from a data block. The chunk size for LOBs can be specified as part of the LOB storage specification.

   Modified: 23-APR-01    Ref #: ID-2407


Is it possible to use OCI to create a new persistent object with a LOB attribute and write to that LOB attribute?

Yes. The application follows these steps:
  1. Call OCIObjectNew() to create a persistent object with a LOB attribute.
  2. Mark the object as dirty.
  3. Flush the object, thereby inserting a row into the table.
  4. Repin the latest version of the object (or refresh the object), thereby retrieving the object from the database and acquiring a valid locator for the LOB.
  5. Call OCILobWrite() using the LOB locator in the object to write the data.

   Modified: 23-APR-01    Ref #: ID-2408


Is there a way to test if the LOB data associated with the OCILobLocator() is NULL before performing an OCILobRead() or OCILobGetLength()?

No. The only way to find out if the LOB data has a length of 0 is by calling OCILobGetLength(). If the LOB is NULL, then the LOB column stores NULL and there is no LOB locator nor is there a LOB. Therefore, a SQL select of a NULL LOB returns NULL instead of a LOB locator.

If the LOB exists, the LOB locator is stored in the LOB column and the LOB data length can range anywhere from 0 to 4 GB. A SQL SELECT of a LOB with a length of 0 returns a valid LOB locator that can be passed to OCILobGetLength().

In order to create a LOB with a length of 0, you can issue a SQL INSERT statement passing EMPTY_BLOB() or EMPTY_CLOB() for the internal LOB column.

   Modified: 23-APR-01    Ref #: ID-2409


What are the recommendations regarding the parameter "Enable/Disable storage in row" when using small BLOBs? For example, if you are using 1K BLOBs and 5K BLOBs.

The default for LOBs is that the system automatically figures out whether or not a particular LOB should be stored inline in the row, or outside the row. If the LOB is greater than approximately 4000 bytes, it is automatically be stored outside the row.

Using 1K BLOBs: Allow the system to keep its default setting and store the LOBs inline in the row. If you explicitly say "Disable storage in row" when you create the LOB column, you cause all those LOBs to be stored in their own separate tablespace.

The Enable/Disable storage in row option is designed to allow you to enforce pure data separation. For example, if you decide that you want to keep all the LOB data on a separate device.

If you are mostly using large LOBs, you may decide to enforce this separation to avoid extra disk contention on the disk that stores the table data.

Using 5K BLOBs: As long as all LOBs are greater than 4000 bytes, the LOBs are stored outside the row in their own tablespace by default. The parameter Enable/Disable storage in row option has no effect.

If the size of these LOBs varies, and are sometimes less than 4000 bytes, then the system keeps the smaller ones inline. Generally, performance is better for LOBs 4000 bytes and smaller if you allow them to be inline because you will not have to go through an extra I/O for the LOB index dereference.

   Modified: 23-APR-01    Ref #: ID-2410


How do you decide the optimal chunk size regarding the block size and the file system physical I/O? For example, if you are using 1K BLOBs, 5K BLOBs, and 40K BLOBs.

The largest chunk allowed is 32k. The smallest chunk is one database block.

Using 1K BLOBs: It is recommended to choose a chunk size of 1 database block size (assuming it is larger than 1k). If you are allowing storage of LOBs inline, the chunk size is actually irrelevant.

Using 5K BLOBs: It is recommended to choose a chunk size of 2k, 4k, or 8k blocks. The chunk size you choose depends on whether you are concerned about wasting disk space. If your chunk size is 8k, that is the increment in which space is allocated. Of course, if your database block is 8k, then you cannot choose 2k or 4k as a chunk size.

Although it seems that chunk size plays a significant role in optimizing access to LOBs, in reality it does not have much influence. The difference in performance between 2k and 32k chunks is not very significant for large LOBs. For 5k LOBs, avoid 8k, 16k, and 32k chunks if possible. But, you may decide you need an 8k database block size for other reasons.

In general, each chunk of a LOB has a separate LOB index entry, so with larger chunks there are smaller index entries and fewer index entries to traverse. Also, there may be a benefit in the retrieval of multiple guaranteed contiguous blocks (all blocks in a chunk are contiguous).

For the 5k choice, the chunk size value is influenced by the database block size. If you have control over the database block size, examine the operating system block size, and then choose 2k or 4k for the chunk size depending on which seems to minimize I/Os.

Using 40K BLOBs: It is recommend to choose a chunk size of 8k.

   Modified: 23-APR-01    Ref #: ID-2411


When reading in stream mode, does Oracle8i use synchronous or asynchronous reads? That is, does control return to the client after the first chunk read or only at the end of a chunk read?

You can read BLOBs in two modes:

  • Piecewise fetch mode.
  • See Oracle8 Application Developer's Guide - Large Objects (LOBs) for more information.

  • Polling or streaming mode
  • For polling or streaming reads, you can specify an offset and an amount. Streaming does not mean you read the complete BLOB at once, and there is no "chunk" mode. Whether or not Oracle8i uses synchronous or asynchronous reads depends on whether the user specifies CACHE or NOCACHE during the creation of the LOB column. If the user specifies NOCACHE, then reads and writes are performed asynchronously, or "directly" and the LOB blocks do not go through the buffer cache.

   Modified: 23-APR-01    Ref #: ID-2412


What are some tips to optimize performance when working with LOBs in OCI?

  1. Use an array bind to perform INSERTs.
  2. Use an array fetch to retrieve all LOB locators.
  3. Open multiple connections to the same database, and parallelize the INSERTS, FETCHES, OCILobWrites(), and OCILobReads() on these connections.
  4. Turn logging off when loading or unloading 300 to 400 GB of LOB data.
  5. Use the maximum Oracle block size and chunk size (32 k) if the individual LOBs are large.
  6. Use a different tablespace for TIFF BLOB DATA, TIFF BLOB INDEX, SGML CLOB DATA, and SGML CLOB INDEX.
  7. Since LOBs are big, you can obtain the best performance by reading and writing large chunks of a LOB value at a time.
  8. This helps in several respects:
    • If accessing the LOB from the client side and the client is at a different node than the server, large reads/writes reduce network overhead.
    • If using the 'NOCACHE' option, each small read/write incurs an I/O. Reading/writing large quantities of data reduces the I/O.
    • Writing to the LOB creates a new version of the LOB CHUNK. Therefore, writing small amounts at a time incurs the cost of a new version for each small write. If logging is on, the CHUNK is also stored in the redo log.
  9. Use LOB buffering if you need to read/write small pieces of LOB data on the client.
  10. Use OCILobWrite() and OCILobRead() with a callback so data is streamed to or from the LOB. Ensure that the length of the entire write is set in the amount parameter on input. Whenever possible, read and write in multiples of the LOB chunk size.
  11. Use a checkout/checkin model for LOBs. LOBs are optimized for the following:
    • SQL UPDATE, which replaces the entire LOB value.
    • Copy the entire LOB data to the client, modify the LOB data on the client side, and copy the entire LOB data back to the database. This can be done using OCILobRead() and OCILobWrite() with streaming.

   Modified: 23-APR-01    Ref #: ID-2415


At what time does an implicit transaction start?

The transaction context is initialized at the end of the OCITransStart() call. However, the transaction context is opaque to the client program and you cannot retrieve information about an implicit transaction. Implicit transaction starts when the client program makes its first database change after OCISessionBegin() or the previous commit/rollback.

   Modified: 27-APR-01    Ref #: ID-2413


If I do not have access to an implicit transaction, how do I find out what features this transaction has?

The transaction has the usual (ACID) properties. Unlike global transactions (that are started explicitly), implicit transactions cannot be migrated between service contexts. Unless you are planning on building a multi-tier application, implicit transactions should be sufficient.

   Modified: 27-APR-01    Ref #: ID-2414


Tell me about OCI features to manage transactions?

Oracle8i OCI provides a set of API calls to support operations on both local and global transactions. These calls include object support, so that if an OCI application is running in object mode, the commit and rollback calls synchronizes the object cache with the state of the transaction.

The functions listed below perform transaction operations. Each call takes a service context handle that should be initialized with the proper server context and user session handle. The transaction handle is the third element of the service context; it stores specific information related to a transaction. When a SQL statement is prepared, it is associated with a particular service context. When the statement is executed, its effects (query, fetch, insert) become part of the transaction that is currently associated with the service context.

  • OCITransStart() - marks the start of a transaction
  • OCITransDetach() - detaches a transaction
  • OCITransCommit() - commits a transaction
  • OCITransRollback() - rolls back a transaction
  • OCITransPrepare() - prepares a transaction to be committed in a distributed processing environment
  • OCITransForget() - causes the server to forget a heuristically completed global transaction.

Depending on the level of transactional complexity in your application, you may need all or only a few of these calls. The following section discusses this in more detail.

Levels of Transactional Complexity

OCI supports three levels of transaction complexity. Each level is described in one of the following sections.

  1. Simple Local Transactions
  2. Serializable or Read-Only Local Transactions
  3. Global Transactions

Simple Local Transactions

Many applications work with only simple local transactions. In these applications, an implicit transaction is created when the application makes database changes. The only transaction-specific calls needed by such applications are:

  • OCITransCommit() - to commit the transaction
  • OCITransRollback() - to roll back the transaction
  • As soon as one transaction has been committed or rolled back, the next modification to the database creates a new implicit transaction for the application.

    Only one implicit transaction can be active at any time on a service context. Attributes of the implicit transaction are opaque to the user.

    If an application creates multiple authorizations, each one can have an implicit transaction associated with it.

    Serializable or Read-Only Local Transactions

    Applications requiring serializable or read-only transactions require an additional OCI call beyond those needed by applications operating on simple local transactions. To initiate a serializable or read-only transaction, the application must create the transaction by calling OCITransStart() to start the transaction.

    The call to OCITransStart() should specify OCI_TRANS_SERIALIZABLE or OCI_TRANS_READONLY, as appropriate, for the flags parameter. If no flag is specified, the default value is OCI_TRANS_READWRITE for a standard read-write transaction.

    Specifying the read-only option in the OCITransStart() call saves the application from performing a server round-trip to execute a SET TRANSACTION READ ONLY statement.

    Global Transactions

    Global transactions are necessary only in more sophisticated transaction-processing applications. As they are not further discussed in this guide, see "OCI Programming Advanced Topics" of Oracle Call Interface Programmer's Guide, Volume I for more information.

       Modified: 27-APR-01    Ref #: ID-2416


    Do I need to allocate the transaction handle before starting an implicit transaction?

    No. You do not need to allocate the transaction handle. But you will not be able to get any attributes of this transaction later in the program.

       Modified: 27-APR-01    Ref #: ID-2417


    What steps should be performed to start a serializable or read-only transaction?

    To initiate a serializable or read-only transactions:

    1. Call OCISessionBegin().
    2. Call OCITransStart() to start the transaction. The call to OCITransStart() should specify OCI_TRANS_SERIALIZABLE or OCI_TRANS_READONLY, as appropriate, for the flags parameter. If no flag is specified, the default value is OCI_TRANS_READWRITE for a standard read-write transaction.
    3. Specifying the read-only option in the OCITransStart() call saves the application from performing a server round-trip to execute a SET TRANSACTION READ ONLY statement.

       Modified: 27-APR-01    Ref #: ID-2418


    How does TAF (Transparent Application Failover) fail over end user connections transparently?

    The failover is handled by the OCI8 libraries. If they detect a failure, they will automatically use the information in the connect string to reestablish the session.

       Modified: 04-FEB-02    Ref #: ID-2488


    Does TAF SELECT failover work with any type of database configuration (replication, standby, cold failover), or is OPS required?

    TAF SELECT failover is supported with any type of database, Real Application Clusters or OPS is not required. A cluster failover (Fail Safe, ServiceGuard, FirstWatch, etc) would work just as well--it's the same database, just running on a different node. Also, SELECT failover will work with a replicated or standby database. However, because these databases are not identical to the primary database (a standby may be a few logs behind and a replicated database can have other records), the likelihood that the first n rows returned by the restarted query not corresponding to the n rows returned before the failure increases. Oracle (OCI library) detects this by calculating a checksum on those first n rows and comparing it to the running checksum it was calculating before the failure. If they are the same, Oracle assumes the first n rows are identical and discards those rows because they have already been returned. If they are not identical, Oracle returns an error message.

       Modified: 04-FEB-02    Ref #: ID-2491


    Does Developer, Forms or Reports support TAF?

    No. Forms does not support TAF. The Forms development group decided not to support it because most of their customers utilize a lot of PLSQL packages running in the DB, and the package state is not preserved with TAF. In the event of a failure, they would lose state and have to go back to the top level screen anyway, so they didn't see this as a big win.

    Reports does not support TAF. In order to failover a reports session, there is important session state that must be restored. In theory, this could be accomplished via a callback function. The callback function is also used to notify the user a failback is in progress and explain there may be a delay while the query is reissued. However, Reports does not support registering such a callback function. They currently do not have plans to offer this support.

       Modified: 04-FEB-02    Ref #: ID-2492


    Does Transparent Application Failover (TAF) support SELECT failover of queries using bind variables?

    Yes. The only caveat is you cannot free or change the bind handles after execute. They will be reused at failover time.

       Modified: 04-FEB-02    Ref #: ID-2493


    Tell me more about JDBC and TAF?

    Any JDBC/OCI driver built using OCI8 or later should work with TAF's basic features. However, if you want to use the callback, you need to use the version of the driver that supports callback functions. Without the callback function, failover happens below the level of the JDBC driver (in the OCI layer). The JDBC client simply sees a delay during failover. After failover any package or session state will be lost. Any transactions in flight will be lost, and the client will be forced to issue a rollback to acknowledge the transaction is dead. If you want to support transactions, the application must cache the DML statements and replay them after failover/rollback.

    In 9i, you can register callback functions. Unfortunately, there are bugs. These bugs have been fixed for 9iR2, but have not been backported yet to 9.0.1 (backport has not been requested). The callback support is all client code, so JDBC and TAF 9i drivers ought to work against an older database.

    The callback can be used to replay session state, and other house cleaning you may require. It doesn't change the fact that any in flight tranactions are dead, and the client must still issue a rollback.

    TAF with or without callbacks requires the JDBC/OCI driver. The thin driver, or the Merant drivers, do not use the client side OCI libraries, and that's where the failure detection and failover work is actually done--hence they will not work with TAF.

       Modified: 04-FEB-02    Ref #: ID-2486


    How do you register a callback function when using Pro* pre-compilers and TAF?

    Below is a simple function to register the callback function...

    void RegisterCallback()
    {
        OCISvcCtx *oschp;
        OCIFocbkStruct failover;                 /*  failover callback structure */
        sword status;
        OCIError *errhp;
        OCIServer *srvhp;
        OCIEnv *envhp;
    
        status= SQLEnvGet((dvoid *)0, &envhp);
      
        if (status != OCI_SUCCESS){
          printf("Error occurred in getting OCI env handle: %ld\n", status);
          exit(-1);
        }
        printf("Got env\n");
      
        /* allocate an error handle */
      
        status = OCIHandleAlloc((dvoid *)envhp, (dvoid **)&errhp, 
                                (ub4) OCI_HTYPE_ERROR, (ub4 )0, (dvoid **) 0);
      
        if(status != OCI_SUCCESS)
        {
          printf("Error occurred in getting OCI err handle: %ld\n", status);
          exit(-1);
        }
        printf("Got err\n");
    
    
        status = SQLSvcCtxGet((dvoid *)0, (text *)"", 0, &oschp);
        if (status != OCI_SUCCESS){
          printf("Error occurred in getting OCI scv ctx handle: %ld\n", status);
          exit(-1);
        }
        printf("Got svcctx\n");
    
    
        status = OCIAttrGet((dvoid *)oschp, (ub4)OCI_HTYPE_SVCCTX, 
                            (dvoid *)&srvhp, (ub4*)0, OCI_ATTR_SERVER, errhp);
        if (status != OCI_SUCCESS){
          printf("Error occurred in getting OCI server handle: %ld\n", status);
          exit(-1);
        }
        printf("Got server\n");
    
        /* set up failover cbk handle */
        failover.callback_function = &callback_fn;
        failover.fo_ctx = (dvoid *)0;
    
        status = OCIAttrSet((dvoid *)srvhp, (ub4) OCI_HTYPE_SERVER,
                            (dvoid *) &failover, (ub4) 0,
                            (ub4) OCI_ATTR_FOCBK, errhp);
        if (status != OCI_SUCCESS){
          printf("Error occurred in setting cbk: %ld\n", status);
          exit(-1);
        }
    
    
        printf("callback registered. \n");
    }
    
    

       Modified: 04-FEB-02    Ref #: ID-2495


    Do database links support TAF?

    DB Links do not use the complete OCI8 code path--thus TAF will not work. If you have a DB Link activiated and pointing to a database that has failed, you will get an ORA-03113 EOF on comm. channel error (even if the database has been recovered). Once you get this error, the link will become invalid, and the next time you attempt to use the link it will be re-established using the connect string with which it was originally defined.

       Modified: 25-SEP-03    Ref #: ID-2496


    What versions of the OCI client libraries are supported with what versions of the database?

    Client and server software is tested for compatibility when new versions are released. We test all new clients with old servers that, on the day the new client releases, are still within the Primary Error Correction phase of their support life. We also test new servers with old clients that, on the day the new server releases, are in the Primary Error Correction phase of their support life, or are in the first two years of the Extended Maintenance phase of their support life. If we do not test the combination, it is not supported.

    When 10.1 was released, client and server versions 8.1.7 and 9.2 were within their Primary Error Correction support life. Those combinations of client and server were tested and are supported.  Version 9.0.1 was tested, but is not supported, since it does not have an Extended Maintenance phase.

    The matrix below summarizes clients and server combinations that were tested.

    Server Version
    Client Version 8.1.7 9.0.1 9.2 10.1
    8.1.7 Yes Was Yes Yes
    9.0.1 Was Was Was Was
    9.2 Yes Was Yes Yes
    10.1 Yes Was Yes Yes

    *=Supported for customers under Extended Maintenance (EMS) only.

    Since new database servers are compatible with a limited set of older OCI clients, it may not be necessary to upgrade the client software when upgrading the database. However, some new features may not work without upgrading the client software. Old features will work with the non-upgraded clients, but newer features have no such guarantees.

    Note this FAQ only includes information on currently supported and terminal releases. For information about official compatibility of older releases, refer to MetaLink note 207303.1.  Also, to check the schedule for database desupport, see MetaLink note 161818.1.

      &nbs