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)
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.
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.
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.
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.
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.
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.
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.
There are three main steps when building a C or C++ application:
Define the object types that correspond to the application objects.
Execute the SQL data definition language (DDL) statements to populate the database with the necessary object types.
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.
Compile and link the application's .c files with the OCI library to build the application executable.
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:
Attaches to DB1.
Starts two new transactions, TX1 and TX2.
Prepares and executes a statement in each transaction at the same time in different threads (STMT1 in TX1, and STMT2 in TX2).
Commits TX1 and TX2.
Detaches from DB1.
User2 performs the following actions:
Attaches to DB2.
Starts two new transactions, TX3 and TX4.
Prepares and executes a statement in each transaction at the same time (STMT3 in TX3, and STMT4 in TX4).
Commits TX3 and TX4.
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.
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.
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.
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.
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.
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.
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.
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.
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().
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.
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.
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.
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:
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.
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.
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:
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.
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.
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.
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.
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:
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:
Pass a value of OCI_DEFAULT to OCIEnvInit(). In this case, the OCI library automatically gets a mutex on all relevant handles.
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.
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.
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.
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.
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.
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.
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.
Call OCIObjectNew() to create a persistent object with a LOB attribute.
Mark the object as dirty.
Flush the object, thereby inserting a row into the table.
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.
Call OCILobWrite() using the LOB locator in the object to write the data.
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.
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.
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.
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.
Open multiple connections to the same database, and parallelize the INSERTS, FETCHES, OCILobWrites(), and OCILobReads() on these connections.
Turn logging off when loading or unloading 300 to 400 GB of LOB data.
Use the maximum Oracle block size and chunk size (32 k) if the individual LOBs are large.
Use a different tablespace for TIFF BLOB DATA, TIFF BLOB INDEX, SGML CLOB DATA, and SGML CLOB INDEX.
Since LOBs are big, you can obtain the best performance by reading and writing large chunks of a LOB value at a time.
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.
Use LOB buffering if you need to read/write small pieces of LOB data on the client.
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.
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.
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.
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.
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.
Simple Local Transactions
Serializable or Read-Only Local Transactions
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.
To initiate a serializable or read-only transactions:
Call OCISessionBegin().
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.
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.
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.
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.
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.
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.
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.
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.
DB Links from one database to another may require callbacks. This requires both databases are able to act as clients and servers. Therefore if the database where the link is created is version A, and the target database is version B, connections must be supported from version A clients to version B servers, AND connections must be supported from version B clients to version A servers.
Click here for more information on client and server version compatibility.
No, Pro*C is not supported by Instant Client. We recommend using Oracle C++ Call Interface (OCCI) when you want to use object-oriented programming capabilities.
Instant Client can be bundled by anyone that can bundle the regular Client, including Embedded License holders. ISVs can redistribute Instant Client along with their own packaged applications for free.
No, Instant Client does not work with Oracle Names. While Instant Client is backwards compatible with older databases, it does not support now deprecated features, including Oracle Names. Please see the Metalink Note.
Yes. Instant Client can be redistributed by ISVs to their customers in their custom installations. Enterprises can redistribute Instant Client within their organizations. However, customers can only call Oracle Support for Instant Client if they have a standard support contract.
Instant Client is FREE for anyone to use in a development or production environment. However, customers can only call Oracle Support if they already have a standard support contract.
Instant Client can be used to run your OCI, OCCI, JDBC, and ODBC applications without installing a full Oracle Client. In addition, Instant Client supports SQL*Plus.
All Oracle net naming methods that do not require use of
ORACLE_HOME or TNS_ADMIN (to locate configuration files such as tnsnames.ora or sqlnet.ora) work in the Instant Client mode. In particular, the connect string can be specified in the following formats:
Naming methods that require TNS_ADMIN to locate configuration files continue to work if the TNS_ADMIN environment variable is set.
If the TNS_ADMIN environment variable is not set, and TNSNAMES entries such as inst1, and so on, are used, then the ORACLE_HOME variable must be set, and the configuration files are expected to be in the $ORACLE_HOME/network/admin directory.
Please note that the ORACLE_HOME variable in this case is only used for locating Oracle Net configuration files, and no other component of Client Code Library (OCI, NLS, and so on) uses the value of ORACLE_HOME.
The bequeath adapter or the empty connect strings are not
supported. However, an alternate way to use the empty connect string is to set the TWO_TASK environment variable on UNIX, or the LOCAL variable on Windows, to either a tnsnames.ora entry or an Oracle Net keyword-value pair. If TWO_TASK or LOCAL is set to a tnsnames.ora entry, then the tnsnames.ora file must be able to be loaded by TNS_ADMIN or ORACLE_HOME setting.
As long as the library loading path has the directory containing Instant Client files (e.g. the instantclient directory) ahead of library directory in ORACLE_HOME, the application will operate in the Instant Client mode, and the libraries in the ORACLE_HOME will not be used.
Environment variables ORA_NLS33, ORA_NLS32, and ORA_NLS are ignored in the Instant Client mode. The ORA_TZFILE environment variable should be set to the name of the large timezone file as opposed to its full path.
All other environment variables (such as NLS_LANG) have no change in behavior.
The Instant Client libraries occupy a virtual address space that is equal to the size of the files. However, only frequently used error messages from the libraries occupy physical memory. Under most cases, the physical memory load is a few kilobytes despite the larger reserved virtual address space.
Special settings for Instant Client enabled applications can be set in the registry for all users or on a per-user basis.
1. Using a registry editor (regedit or regedt32), go to HKEY_LOCAL_MACHINE\SOFTWARE for a global setting (or HKEY_LOCAL_MACHINE\HKEY_CURRENT_USER\SOFTWARE for each specific user).
2. Add ORACLE group key.
3. Add a new string value within the ORACLE key for the different settings you are interested in (NLS_LANG, TNS_ADMIN, etc.). For keys that are directories, use a fully qualified path.
If you already have Oracle Client installed on your machine, then we recommend:
1. Do NOT unzip or copy the Instant Client libraries to either of ORACLE_HOME/lib or ORACLE_HOME/bin directories, to avoid overwriting files and leaving the installation in an inconsistent state. In general, we recommend putting the Instant Client libraries into a separate clean directory.
2. Do NOT put both the Instant Client directory AND the Oracle Client directory in the Library Path, regardless of ordering. Please use either one or the other in the Library Path (LD_LIBRARY_PATH or PATH, depending on platform), but not both, based on the version you wish to use.
Always set the TNS_ADMIN environment variable or registry setting to the full path of the tnsnames.ora file. This practice will ensure that you are using the appropriate tnsnames.ora for your application when running with Instant Client.
Bookmark
