Oracle Call Interface FAQ

This FAQ provides answers to the most frequently asked questions from Oracle Call Interface (OCI) application developers.

Specific topics discussed are:

What Is Oracle Call Interface?

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

Additional Information:

See Part 1, "Basic OCI Concepts" of Oracle Call Interface Programmer's Guide, Volume I for more information on the basics of OCI programming.

Why Use OCI Instead of Pro*C?

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

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

Why Use OCI Instead of ODBC?

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

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

Objects

This section provides answers to the most frequently asked questions about objects. It also includes an overview of objects, the object cache, and navigational access. The following topics are discussed:

Objects Overview

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.

What is the Object Cache?

Throughout the following sections, 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.

Navigational Access

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.

Additional Information:

See Oracle8 Concepts and Oracle8 Application Developer's Guide - Fundamentals for a more detailed explanation of object types and objects.

See "Part II OCI Object Concepts" of Oracle Call Interface Programmer's Guide, Volume II for detailed information on the use of Oracle8i objects with OCI.

Freeing an Object Copy

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

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

Object Cache Parameters

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

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

If the memory occupied by the objects currently in the object cache reaches or exceeds the high watermark, the object cache automatically begins to free unmarked objects that have a pin count of 0. The object cache continues freeing such objects until memory usage in the object cache reaches the optimal size, or until it runs out of objects eligible for freeing.

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

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.

Multi-threaded Client Applications

Basic Concepts of Multi-threaded Development

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.

Question. 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?

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

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

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

User Session Handles and Privileges

This section provides answers to the most frequently asked questions about user session handles. It also includes an overview of user session handles. The following topics are discussed:

User Session Handle Overview

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.

OCI_PRELIM_AUTH

Question. Should OCI_PRELIM_AUTH be used with SYSDBA or SYSOPER?

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

Threads

This section provides answers to the most frequently asked questions about threads. It also includes an overview. The following topics are discussed:

Threads Overview

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.

Serializing Access From Multiple Threads

Question. 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?

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

Implementing Thread Safety

Question. How can thread safety levels be set in OCI?

Answer. 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.
Pass a value of OCI_NO_MUTEX for the mode of OCIEnvInit(). In this case, the application must mutex OCI calls made on the same environment handle by itself.

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.

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

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

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

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

Question. 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?

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

Large Objects LOB and FILEs

This section provides answers to the most frequently asked questions about large objects (LOBs). It also includes an overview. The following topics are discussed:

LOB/FILE Datatype Locator Overview

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.

LOB Column Size

Question. What is the size of the Oracle8i LOB locator?

Answer.

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.

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

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

Binary FILEs (BFILE)

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

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

Deleting the Directory Object or Operating System File

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

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

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

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

Stipulating Storage Characteristics for Internal LOBs

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

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

Writing to a LOB Attribute of an Object

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

Answer. Yes. The application follows these steps:

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.

Checking if LOB Data is NULL

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

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

BLOBs

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

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

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

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

Question. 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?

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

LOB Performance and Optimization Tips for OCI

Use an array bind to perform INSERTs.
Use an array fetch to retrieve all LOB locators.
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.

Transactions

This section provides answers to the most frequently asked questions about transactions. It also includes an overview. The following topics are discussed:

Transactions Overview

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

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.

Implicit Transactions

Question. At what time does an implicit transaction start?

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

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

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

Note:

ACID is a mnemonic for the properties a transaction should have to satisfy the Object Management Group Transaction Service specifications. A transaction should be Atomic, its result should be Consistent, Isolated (independent of other transactions) and Durable (its effect should be permanent).

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

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

Serializable or Read-Only Local Transactions

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

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

Long Datatypes

Question. 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?

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

Handles and Descriptors

This section provides answers to the most frequently asked questions about handles and descriptors. It also includes an overview. The following topics are discussed:

Handles and Descriptors Overview

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.

Additional Information:

See "OCI Programming Basics" of Oracle Call Interface Programmer's Guide, Volume I and "Handle and Descriptor Attributes" of Oracle Call Interface Programmer's Guide, Volume II for detailed information on handles and descriptors.

Significance of Numbers in the CDEMO.c Example

Question. 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?

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

Example 1-1 CDEMO81.c

#ifdef RCSID
static char *RCSid = 
   "$Header: cdemo81.c 14-oct-98.17:04:30 dchatter Exp $ ";
#endif /* RCSID */

/* Copyright (c) Oracle Corporation 1996, 1997, 1998. All Rights Reserved. */ 

/*

   NAME
     cdemo81.c - Basic OCI V8 functionality

   DESCRIPTION

 *  An example program which adds new employee
 *  records to the personnel data base.  Checking
 *  is done to insure the integrity of the data base.
 *  The employee numbers are automatically selected using
 *  the current maximum employee number as the start.
 *  
 *  The program queries the user for data as follows:
 *   
 *  Enter employee name:
 *  Enter employee job:
 *  Enter employee salary:
 *  Enter employee dept:
 *   
 *  The program terminates if return key (CR) is entered
 *  when the employee name is requested.
 *   
 *  If the record is successfully inserted, the following
 *  is printed:
 *   
 *  "ename" added to department "dname" as employee # "empno"

   Demonstrates creating a connection, a session and executing some SQL.
   Also shows the usage of allocating memory for application use which has the
   life time of the handle.

   MODIFIED   (MM/DD/YY)
   dchatter    10/14/98 - add the usage of xtrmemsz and usrmempp
   azhao       06/23/97 - Use OCIBindByPos, OCIBindByName; clean up
   echen       12/17/96 - OCI beautification
   dchatter    07/18/96 - delete spurious header files
   dchatter    07/15/96 - hda is a ub4 array to prevent bus error
   mgianata    06/17/96 - change ociisc() to OCISessionBegin()
   aroy        04/26/96 - change OCITransCommitt -> OCITransCommit
   slari       04/24/96 - use OCITransCommitt
   aroy        02/21/96 - fix bug in get descriptor handle call
   lchidamb    02/20/96 - cdemo81.c converted for v8 OCI
   lchidamb    02/20/96 - Creation

*/


#include <stdio.h>

#include <stdlib.h>
#include <string.h>
#include <oci.h>

static text *username = (text *) "SCOTT";
static text *password = (text *) "TIGER";

/* Define SQL statements to be used in program. */
static text *insert = (text *) "INSERT INTO emp(empno, ename, job, sal, deptno)\
    VALUES (:empno, :ename, :job, :sal, :deptno)";
static text *seldept = (text *) "SELECT dname FROM dept WHERE deptno = :1";
static text *maxemp = (text *) "SELECT NVL(MAX(empno), 0) FROM emp";
static text *selemp = (text *) "SELECT ename, job FROM emp";

static OCIEnv *envhp;
static OCIError *errhp;

static void checkerr(/*_ OCIError *errhp, sword status _*/);
static void cleanup(/*_ void _*/);
static void myfflush(/*_ void _*/);
int main(/*_ int argc, char *argv[] _*/);

static sword status;

int main(argc, argv)
int argc;
char *argv[];
{

  sword    empno, sal, deptno;
  sword    len, len2, rv, dsize, dsize2;
  sb4      enamelen = 10;
  sb4      joblen = 9;          
  sb4      deptlen = 14;        
  sb2      sal_ind, job_ind;
  sb2      db_type, db2_type;
  sb1      name_buf[20], name2_buf[20];
  text     *cp, *ename, *job, *dept;

  sb2      ind[2];                                              /* indicator */
  ub2      alen[2];                                         /* actual length */
  ub2      rlen[2];                                         /* return length */

  OCIDescribe  *dschndl1 = (OCIDescribe *) 0, 
               *dschndl2 = (OCIDescribe *) 0,
               *dschndl3 = (OCIDescribe *) 0;

  OCISession *authp = (OCISession *) 0;
  OCIServer *srvhp;
  OCISvcCtx *svchp;
  OCIStmt   *inserthp,
            *stmthp, 
            *stmthp1;
  OCIDefine *defnp = (OCIDefine *) 0;

  OCIBind  *bnd1p = (OCIBind *) 0;             /* the first bind handle */
  OCIBind  *bnd2p = (OCIBind *) 0;             /* the second bind handle */
  OCIBind  *bnd3p = (OCIBind *) 0;             /* the third bind handle */
  OCIBind  *bnd4p = (OCIBind *) 0;             /* the fourth bind handle */
  OCIBind  *bnd5p = (OCIBind *) 0;             /* the fifth bind handle */

  OCIBind  *bnd6p = (OCIBind *) 0;             /* the sixth bind handle */

  

  (void) OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0,
                       (dvoid * (*)(dvoid *, size_t)) 0,
                       (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                       (void (*)(dvoid *, dvoid *)) 0 );

  (void) OCIEnvInit( (OCIEnv **) &envhp, OCI_DEFAULT, (size_t) 0, 
                    (dvoid **) 0 );

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

  /* server contexts */
  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, OCI_HTYPE_SERVER,
                   (size_t) 0, (dvoid **) 0);

  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, OCI_HTYPE_SVCCTX,
                   (size_t) 0, (dvoid **) 0);

  (void) OCIServerAttach( srvhp, errhp, (text *)"", strlen(""), 0);

  /* set attribute server context in the service context */
  (void) OCIAttrSet( (dvoid *) svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp, 
                    (ub4) 0, OCI_ATTR_SERVER, (OCIError *) errhp);

  (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **)&authp, 
                        (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0);
 
  (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) username, (ub4) strlen((char *)username),
                 (ub4) OCI_ATTR_USERNAME, errhp);
 
  (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) password, (ub4) strlen((char *)password),
                 (ub4) OCI_ATTR_PASSWORD, errhp);

  checkerr(errhp, OCISessionBegin ( svchp,  errhp, authp, OCI_CRED_RDBMS, 
                          (ub4) OCI_DEFAULT));

  (void) OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
                   (dvoid *) authp, (ub4) 0,
                   (ub4) OCI_ATTR_SESSION, errhp);

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

  checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp1,
           OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  /* Retrieve the current maximum employee number. */
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, maxemp, 
                                (ub4) strlen((char *) maxemp),
                                (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));

  /* bind the input variable */
  checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp, 1, (dvoid *) &empno,
                   (sword) sizeof(sword), SQLT_INT, (dvoid *) 0, (ub2 *)0,
                   (ub2 *)0, OCI_DEFAULT));


  /* execute and fetch */
  if (status = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
               (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT))
  {
    if (status == OCI_NO_DATA)
      empno = 10;
    else
    {
      checkerr(errhp, status);
      cleanup();
      return OCI_ERROR;
    }
  }


  /* 
   * When we bind the insert statement we also need to allocate the storage 
   * of the employee name and the job description.
   * Since the lifetime of these buffers are the same as the statement, we 
   * will allocate it at the time when the statement handle is allocated; this
   * will get freed when the statement disappears and there is less 
   * fragmentation.
   *
   * sizes required are enamelen+2 and joblen+2 to allow for \n and \0
   *    
   */


  checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &inserthp,
           OCI_HTYPE_STMT, (size_t) enamelen + 2 + joblen + 2, 
           (dvoid **) &ename));
  job   = (text *) (ename+enamelen+2);


  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, insert, 
                                (ub4) strlen((char *) insert),
                                (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));

  checkerr(errhp, OCIStmtPrepare(stmthp1, errhp, seldept, 
                                (ub4) strlen((char *) seldept),
                                (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));


  /*  Bind the placeholders in the INSERT statement. */
  if ((status = OCIBindByName(stmthp, &bnd1p, errhp, (text *) ":ENAME",
             -1, (dvoid *) ename,
             enamelen+1, SQLT_STR, (dvoid *) 0,
             (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)) ||
      (status = OCIBindByName(stmthp, &bnd2p, errhp, (text *) ":JOB",
             -1, (dvoid *) job,
             joblen+1, SQLT_STR, (dvoid *) &job_ind,
             (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)) ||
      (status = OCIBindByName(stmthp, &bnd3p, errhp, (text *) ":SAL",
             -1, (dvoid *) &sal,
             (sword) sizeof(sal), SQLT_INT, (dvoid *) &sal_ind,
             (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)) ||
      (status = OCIBindByName(stmthp, &bnd4p, errhp, (text *) ":DEPTNO",
             -1, (dvoid *) &deptno,
             (sword) sizeof(deptno), SQLT_INT, (dvoid *) 0,
             (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)) ||
      (status = OCIBindByName(stmthp, &bnd5p, errhp, (text *) ":EMPNO",

             -1, (dvoid *) &empno,
             (sword) sizeof(empno), SQLT_INT, (dvoid *) 0,
             (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)))
  {
    checkerr(errhp, status);
    cleanup();
    return OCI_ERROR;
  }

  /*  Bind the placeholder in the "seldept" statement. */
  if (status = OCIBindByPos(stmthp1, &bnd6p, errhp, 1,
           (dvoid *) &deptno, (sword) sizeof(deptno),SQLT_INT,
           (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))
  {
    checkerr(errhp, status);
    cleanup();
    return OCI_ERROR;
  }  

  /*  Allocate the dept buffer now that you have length. */
  /* the deptlen should eventually get from dschndl3.    */
  deptlen = 14;
  dept = (text *) malloc((size_t) deptlen + 1);

  /*  Define the output variable for the select-list. */
  if (status = OCIDefineByPos(stmthp1, &defnp, errhp, 1,
                             (dvoid *) dept, deptlen+1, SQLT_STR,
                             (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT))
  {
    checkerr(errhp, status);
    cleanup();
    return OCI_ERROR;
  }

  for (;;)
  {
    /* Prompt for employee name.  Break on no name. */
    printf("\nEnter employee name (or CR to EXIT): ");
    fgets((char *) ename, (int) enamelen+1, stdin);
    cp = (text *) strchr((char *) ename, '\n');
    if (cp == ename)
    {
      printf("Exiting... ");
      cleanup();
      return OCI_SUCCESS;
    }
    if (cp)
      *cp = '\0';
    else
    {
      printf("Employee name may be truncated.\n");
      myfflush();
    }
    /*  Prompt for the employee's job and salary. */
    printf("Enter employee job: ");
    job_ind = 0;
    fgets((char *) job, (int) joblen + 1, stdin);
    cp = (text *) strchr((char *) job, '\n');
    if (cp == job)
    {
      job_ind = -1;            /* make it NULL in table */    
      printf("Job is NULL.\n");/* using indicator variable */
    }

    else if (cp == 0)
    {
      printf("Job description may be truncated.\n");
      myfflush();
    }
    else
      *cp = '\0';

    printf("Enter employee salary: ");
    scanf("%d", &sal);
    myfflush();
    sal_ind = (sal <= 0) ? -2 : 0;  /* set indicator variable */

    /*
     *  Prompt for the employee's department number, and verify
     *  that the entered department number is valid
     *  by executing and fetching.
     */
    do
    {
      printf("Enter employee dept: ");
      scanf("%d", &deptno);
      myfflush();
      if ((status = OCIStmtExecute(svchp, stmthp1, errhp, (ub4) 1, (ub4) 0,
               (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT))
          && (status != OCI_NO_DATA))
      {
        checkerr(errhp, status);
        cleanup();
        return OCI_ERROR;
      }  
      if (status == OCI_NO_DATA)
        printf("The dept you entered doesn't exist.\n");
      } while (status == OCI_NO_DATA);

      /*
       *  Increment empno by 10, and execute the INSERT
       *  statement. If the return code is 1 (duplicate
       *  value in index), then generate the next
       *  employee number.
       */
      empno += 10;
      if ((status = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
               (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT)) 
               && status != 1)
      {
        checkerr(errhp, status);
        cleanup();
        return OCI_ERROR;
      }
      while (status == 1)
      {
        empno += 10;
        if ((status = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
               (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT)) 
                && status != 1)
        {
          checkerr(errhp, status);
          cleanup();
          return OCI_ERROR;
        }
      }  /* end for (;;) */

      /* Commit the change. */

      if (status = OCITransCommit(svchp, errhp, 0))
      {
        checkerr(errhp, status);
        cleanup();
        return OCI_ERROR;
      }
      printf("\n\n%s added to the %s department as employee number %d\n",
                 ename, dept, empno);
  }
}


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;
  }
}


/*
 *  Exit program with an exit code.
 */
void cleanup()
{
  if (envhp)
    (void) OCIHandleFree((dvoid *) envhp, OCI_HTYPE_ENV);
  return;

}


void myfflush()

{
  eb1 buf[50];

  fgets((char *) buf, 50, stdin);
}


/* end of file cdemo81.c */

Understanding Multiple Connections and Handles

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:

Question. How many server handles are required?

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

Question. How many service context handles are required?

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

Question. How many user session handles are required?

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

Question. How many transaction handles are required?

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

Question. Could the example use multiple environment handles?

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

Question. 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?

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

Prefetching

Question. What is the default value of OCI_ATTR_TOP_RCNT?

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

Building a ProC or ProC++ Application

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

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

Additional Information:

See "Using the Object Type Translator" of Oracle Call Interface Programmer's Guide, Volume I.

OCI_UCS2ID Support and Oracle8i Character Sets

Question. 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?

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

The 27 Most Frequently Used Oracle8i OCI Relational Functions

This table provides a list of the most frequently used OCI relational functions. See "OCI Relational Functions" in Oracle Call Interface Programmer's Guide, Volume II to view the complete list of OCI functions.

Function Description

OCIAttrSet()

Sets a particular attribute of a handle or a descriptor

OCIAttrGet()

Gets a particular attribute of a handle

OCIBindByName()

Creates an association between a program variable and a placeholder in a SQL statement or PL/SQL block

OCIBindByPos()

Creates an association between a program variable and a placeholder in a SQL statement or PL/SQL block

OCIDefineByPos()

Associates an item in a select-list with the type and output data buffer

OCIDescribeAny()

Describes existing schema and sub-schema objects

OCIDescriptorAlloc()

Allocates storage to hold descriptors or LOB locators

OCIDescriptorFree()

Deallocates a previously allocated descriptor

OCIEnvInit()

Allocates and initializes an OCI environment handle

OCIErrorGet()

Returns an error message in the buffer provided and an ORACLE error

OCIHandleAlloc()

Returns a pointer to an allocated and initialized handle

OCIHandleFree()

Explicitly deallocates a handle

OCIInitialize()

Initializes the OCI process environment

OCILobRead()

Reads a portion of a LOB/FILE, as specified by the call, into a buffer

OCILobWrite()

Writes a buffer into a LOB

OCILogoff()

Terminates a connection and session created with OCILogon()

OCILogon()

Creates a simple logon session

OCIParamGet()

Returns a descriptor of a parameter specified by position in the describe handle or statement handle

OCIParamSet()

Sets a complex object retrieval descriptor into a complex object retrieval handle

OCIServerAttach()

Creates an access path to a data source for OCI operations

OCIServerDetach()

Deletes an access to a data source for OCI operations

OCISessionBegin()

Creates a user session and begins a user session for a given server

OCISessionEnd()

Terminates a user session context created by OCISessionBegin()

OCIStmtExecute()

Associates an application request with a server

OCIStmtFetch()

Fetches rows from a query

OCIStmtPrepare()

Prepares a SQL or PL/SQL statement for execution

OCITransCommit()

Commits the transaction associated with a specified service context

Interoperability Between Different OCI Versions

Question. Can an Oracle7 OCI client connect with an Oracle8i database?

Answer. Yes.

Question. Can an Oracle8i OCI client connect with an Oracle7 database?

Answer. Yes. A user can communicate with an Oracle7 database using Oracle8i OCI. You do not have to change any application code to perform this operation as long you do not use SQL types or features that are not supported by Oracle7.

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

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

Oracle Call Interface FAQ

What Is Oracle Call Interface?

Why Use OCI Instead of Pro*C?

Why Use OCI Instead of ODBC?

Objects

Objects Overview

What is the Object Cache?

Navigational Access

Freeing an Object Copy

Object Cache Parameters

Multi-threaded Client Applications

Basic Concepts of Multi-threaded Development

User Session Handles and Privileges

User Session Handle Overview

OCI_PRELIM_AUTH

Threads

Threads Overview

Serializing Access From Multiple Threads

Implementing Thread Safety

Large Objects LOB and FILEs

LOB/FILE Datatype Locator Overview

LOB Column Size

Binary FILEs (BFILE)

Deleting the Directory Object or Operating System File

Stipulating Storage Characteristics for Internal LOBs

Writing to a LOB Attribute of an Object

Checking if LOB Data is NULL

BLOBs

LOB Performance and Optimization Tips for OCI

Transactions

Transactions Overview

Levels of Transactional Complexity

Simple Local Transactions

Serializable or Read-Only Local Transactions

Global Transactions

Implicit Transactions

Serializable or Read-Only Local Transactions

Long Datatypes

Handles and Descriptors

Handles and Descriptors Overview

Significance of Numbers in the CDEMO.c Example

Example 1-1 CDEMO81.c

Understanding Multiple Connections and Handles

A Connection Example

Prefetching

Building a Pro*C or Pro*C++ Application

OCI_UCS2ID Support and Oracle8i Character Sets

The 27 Most Frequently Used Oracle8i OCI Relational Functions

Interoperability Between Different OCI Versions

Building a ProC or ProC++ Application