Oracle Call Interface Programmer's Guide
Release 8.1.6

Part Number A76975-01





Go to previous page Go to next page

OCI Programming Advanced Topics

This chapter introduces advanced programming topics, including the following:

The OCIThread Package

The OCIThread package provides a number of commonly used threading primitives for use by Oracle customers. It offers a portable interface to threading capabilities native to various platforms. It does not implement threading on platforms which do not have native threading capability.

OCIThread does not provide a portable implementation of multi-threaded facilities. It only serves as a set of portable covers for native multi-threaded facilities. Therefore, platforms that do not have native support for multi-threading will only be able to support a limited implementation of OCIThread. As a result, products that rely on all of OCIThread's functionality will not port to all platforms. Products that must port to all platforms must use only a subset of OCIThread's functionality. This issue is discussed further in later sections of this document.

The OCIThread API is split into three main parts. Each part is described briefly here. The following subsections describe each in greater detail. See "Using the OCIThread Package" for important additional information.

Initialization and Termination

The types and functions described in this section are associated with the initialization and termination of the OCIThread package. OCIThread must be properly initialized before any of its functionality can be used. OCIThread's process initialization function, OCIThreadProcessInit(), must be called with care, as described below.

The observed behavior of the initialization and termination functions is the same regardless of whether OCIThread is in single-threaded or multi-threaded environment. You can call the initialization functions from both generic and operating system specific (OSD) code.

OCIThread Context

Most calls to OCIThread functions take the OCI environment or user session handle as a parameter. The OCIThread context is part of the OCI environment or user session handle and it must be initialized by calling OCIThreadInit(). Termination of the OCIThread context occurs by calling OCIThreadTerm().

The following functions are used to implement thread initialization and termination. Detailed descriptions of each function can be found in Chapter 15, "OCI Relational Functions".

Function  Purpose 


Performs OCIThread process initialization. 


Initializes OCIThread context. 


Terminates the OCIThread layer and frees context memory. 


Tells the caller whether the application is running in a multi-threaded environment or a single-threaded environment. 

Passive Threading Primitives

The passive threading primitives deal with the manipulation of mutex, thread ID's, and thread-specific data. Since the specifications of these primitives do not require the existence of multiple threads, they can be used both on multi-threaded and single-threaded platforms.


The type OCIThreadMutex is used to represent a mutual exclusion lock (mutex). A mutex is typically used for one of two purposes:

Mutex pointers can be declared as parts of client structures or as stand-alone variables. Before they can be used, they must be initialized using OCIThreadMutexInit(). Once they are no longer needed, they must be destroyed using OCIThreadMutexDestroy(). A mutex pointer must not be used after it is destroyed.

A thread can acquire a mutex by using OCIThreadMutexAcquire(). This ensures that only one thread at a time is allowed to hold a given mutex. A thread that holds a mutex can release it by calling OCIThreadMutexRelease().


The type OCIThreadKey can be thought of as a process-wide variable that has a thread-specific value. What this means is that all the threads in a process can use any given key. However, each thread can examine or modify that key independently of the other threads. The value that a thread sees when it examines the key will always be the same as the value that it last set for the key. It will not see any values set for the key by the other threads.

The type of the value held by a key is a dvoid * generic pointer.

Keys can be created using OCIThreadKeyInit(). When a key is created, its value is initialized to NULL for all threads.

A thread can set a key's value using OCIThreadKeySet(). A thread can get a key's value using OCIThreadKeyGet().

The OCIThread key functions will save and retrieve data specific to the thread. When clients maintain a pool of threads and assign the threads to different tasks, it may not be appropriate for a task to use OCIThread key functions to save data associated with it. Here is a scenario of how things can fail: A thread is assigned to execute the initialization of a task. During the initialization, the task stored some data related to it in the thread using OCIThread key functions. After the initialization, the thread is returned back to the threads pool. Later, the threads pool manager assigned another thread to perform some operations on the task, and the task needs to retrieve the data it stored earlier in initialization. Since the task is running in another thread, it will not be able to retrieve the same data. Applications that use thread pools should be aware of this and be cautious when using OCIThread key functions.


OCIThreadKeyDestFunc is the type of a pointer to a key's destructor routine. Keys can be associated with a destructor routine when they are created (see OCIThreadKeyInit()).

A key's destructor routine will be called whenever a thread that has a non-NULL value for the key terminates.

The destructor routine returns nothing and takes one parameter. The parameter will be the value that was set for key when the thread terminated.

The destructor routine is guaranteed to be called on a thread's value in the key after the termination of the thread and before process termination. No more precise guarantee can be made about the timing of the destructor routine call; thus no code in the process may assume any post-condition of the destructor routine. In particular, the destructor is not guaranteed to execute before a join call on the terminated thread returns.


OCIThreadId is the type that will be used to identify a thread. At any given time, no two threads will ever have the same OCIThreadId. However, OCIThreadId values can be recycled; i.e., once a thread dies, a new thread may be created that has the same OCIThreadId as the one that died. In particular, the thread ID must uniquely identify a thread T within a process, and it must be consistent and valid in all threads U of the process for which it can be guaranteed that T is running concurrently with U. The thread ID for a thread T must be retrievable within thread T. This will be done via OCIThreadIdGet().

The OCIThreadId type supports the concept of a NULL thread ID: the NULL thread ID will never be the same as the ID of an actual thread.

Passive Threading Functions

The following functions are used to manipulate mutexes, thread keys and thread IDs. Complete descriptions of each function can be found in Chapter 15, "OCI Relational Functions".

Function  Purpose 


Allocates and initializes a mutex. 


Destroys and deallocates a mutex. 


Acquires a mutex for the thread in which it is called. 


Releases a mutex. 


Allocates and initializes a key. 


Destroys and deallocates a key. 


Gets the calling thread's current value for a key. 


Sets the calling thread's value for a key. 


Allocates and initializes a thread ID. 


Destroys and deallocates a thread ID. 


Sets on thread ID to another. 


Nulls a thread ID. 


Retrieves a thread ID for the thread in which it is called. 


Determines if two thread IDs represent the same thread. 


Determines if a thread ID is NULL. 

Active Threading Primitives

The active threading primitives deal with the manipulation of actual threads. Because the specifications of most of these primitives require that it be possible to have multiple threads, they work correctly only in the enabled OCIThread; In the disabled OCIThread, they always return failure. The exception is OCIThreadHandleGet(); it may be called in a single-threaded environment, in which case it has no effect.

Active primitives should only be called by code running in a multi-threaded environment. You can call OCIThreadIsMulti() to determine whether the environment is multi-threaded or single-threaded.


Type OCIThreadHandle is used to manipulate a thread in the active primitives: OCIThreadJoin() and OCIThreadClose(). A thread handle opened by OCIThreadCreate() must be closed in a matching call to OCIThreadClose(). A thread handle is invalid after the call to OCIThreadClose().

The distinction between a thread ID and a thread handle in OCIThread usage follows the distinction between the thread ID and the thread handle on Windows NT. On many platforms, the underlying native types are the same.

Active Threading Functions

The following functions are used to implement active threading. Complete descriptions of the functions are available in Chapter 15, "OCI Relational Functions".

Function  Purpose 


Allocates and initializes a thread handle. 


Destroys and deallocates a thread handle. 


Creates a new thread. 


Allows the calling thread to join with another. 


Closes a thread handle. 


Retrieves a thread handle. 

Using the OCIThread Package

This section summarizes some of the more important details relating to the use of OCIThread.

Process initialization

OCIThread only requires that the process initialization function (OCIThreadProcessInit()) be called when OCIThread is being used in a multi-threaded application. Failing to call OCIThreadProcessInit() in a single-threaded application is not an error.

OCIThread initialization

Separate calls to OCIThreadInit() will all return the same OCIThread context.

Also, remember that each call to OCIThreadInit() must eventually be matched by a call to OCIThreadTerm().

Active versus Passive Threading primitives

OCIThread client code written without using any active primitives can be compiled and used without modifications on both single-threaded and multi-threaded platforms.

OCIThread client code written using active primitives will only work correctly on multi-threaded platforms. In order to write a version of the same application to run on single-threaded platform, it is necessary to branch the your code, whether by branching versions of the source file or by branching at runtime with the OCIThreadIsMulti() call.

Example using OCIThread

The following code sample illustrates the use of OCIThread. For a listing of the complete demonstration programs, see Appendix B, "OCI Demonstration Programs".

static OCIEnv *envhp; 
static OCIError *errhp; 
void parent(argc, argv) 
sb4 argc; 
text **argv;
  OCIThreadId *tidArr[5];  
  OCIThreadHandle *tHndArr[5]; 
  ub4 i;  
  OCIThreadKey *key;  
  (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); 
  OCIThreadInit(envhp, errhp); 
  OCIThreadKeyInit(envhp, errhp, &key, (OCIThreadKeyDestFunc) NULL); 
  for (i=0; i<5; i++)   
      OCIThreadIdInit(envhp, errhp, &(tidArr[i])); 
      OCIThreadHndInit(envhp, errhp, &(tHndArr[i])); 
  for (i=0; i<5; i++) 
    OCIThreadCreate(envhp, errhp, child, (dvoid *)key,  
                            tidArr[i], tHndArr[i]); 
  for (i=0; i<5; i++) 
      OCIThreadJoin(envhp, errhp, tHndArr[i]);  
      OCIThreadClose(envhp, errhp, tHndArr[i]); 
  for (i=0; i<5; i++)   
       OCIThreadIdDestroy(envhp, errhp, &(tidArr[i])); 
       OCIThreadHndDestroy(envhp, errhp, &(tHndArr[i])); 
  OCIThreadKeyDestroy(envhp, errhp, &key); 
  OCIThreadTerm(envhp, errhp);  
void child(arg) 
dvoid *arg; 
  OCIThreadKey *key = (OCIThreadKey *)arg; 
  OCIThreadId *tid; 
  dvoid *keyval; 
  OCIThreadIdInit(envhp, errhp, &tid); 
  OCIThreadIdGet(envhp, errhp, tid); 
   if (OCIThreadKeySet(envhp, errhp, key, (dvoid *)tid) != OCI_SUCCESS) 
      printf("Could not set value for key\n"); 
   if (OCIThreadKeyGet(envhp, errhp, key, &keyval) !=OCI_SUCCESS) 
      printf("Could not retrieve value for key\n"); 
   if (keyval != (dvoid *)tid) 
     printf("Incorrect value from key after setting it\n"); 
  /* we must destroy thread id */ 
  OCIThreadIdDestroy(envhp, errhp, &tid); 

User-defined Callback Functions

The Oracle Call Interface has the capability to execute user-specific code in addition to OCI calls. This functionality can be used for:

The OCI callback feature has been added by providing support for calling user code before or after executing the OCI calls. Functionality has also been provided to allow the user-defined code to be executed instead of executing the OCI code.

The user callback code can also be registered dynamically without modifying the source code of the application. The dynamic registration is implemented by loading up to five user-created dynamically linked libraries, such as dynamic link libraries (DLLs) on NT, or shared libraries on Solaris, after the initialization of the environment handle during the OCIEnvInit() or OCIEnvCreate() calls. These user-created libraries register the user callbacks for the selected OCI calls transparently to the application.

Sample Application

For a listing of the complete demonstration programs that illustrate the OCI user callback feature, see Appendix B, "OCI Demonstration Programs".

Registering User Callbacks

An application can register user callback libraries with the OCIUserCallbackRegister() function. Callbacks are registered in the context of the environment handle. An application can retrieve information about callbacks registered with a handle with the OCIUserCallbackGet() function. For detailed descriptions of these functions and their parameters, refer to the descriptions of OCIUserCallbackGet() and OCIUserCallbackRegister() in Chapter 15, "OCI Relational Functions".

A user-defined callback is a subroutine that is registered against an OCI call and an environment handle. It can be specified to be either an entry callback, a replacement callback, or an exit callback.

If a replacement or exit callback returns anything other than OCI_CONTINUE, then the return code from the callback is returned from the associated OCI call.

A user callback can return OCI_INVALID_HANDLE when either an invalid handle or an invalid context is passed to it.


A user callback is registered using the OCIUserCallbackRegister() call. See OCIUserCallbackRegister() for the syntax of this call. Currently, OCIUserCallbackRegister() is only registered on the environment handle. The user's callback function of typedef OCIUserCallback is registered along with its context for the OCI call identified by the OCI function code, fcode. The type of the callback, whether entry, replacement, or exit, is specified by the when parameter.

For example, the stmtprep_entry_dyncbk_fn entry callback function and its context dynamic_context, are registered against the environment handle hndlp for the OCIStmtPrepare() call by calling the OCIUserCallbackRegister() function with the following parameters.

OCIUserCallbackRegister( hndlp, 
                         (OCIUcb*) NULL);

User Callback Function

The user callback function has to follow the following syntax:

typedef sword (*OCIUserCallback)
     (dvoid *ctxp,      /* context for the user callback*/
      dvoid *hndlp,     /* handle for the callback, env handle for now */
      ub4 type,         /* type of handlp, OCI_HTYPE_ENV for this release */
      ub4 fcode,        /* function code of the OCI call */
      ub1 when,         /* type of the callback, entry or exit */
      sword returnCode, /* OCI return code */
      ub4 *errnop,      /* Oracle error number */
      va_list arglist); /* parameters of the oci call */

In addition to the parameters described in the OCIUserCallbackRegister() call, the callback is called with the return code, errnop, and all the parameters of the original OCI as declared by the prototype definition.

The return code is always passed in as OCI_SUCCESS and *errnop is always passed in as 0 for the first entry callback. Note that *errnop refers to the content of errnop because errnop is an IN/OUT parameter.

If the callback does not want to change the OCI return code, then it must return OCI_CONTINUE, and the value returned in *errnop is ignored. If on the other hand, the callback returns any other return code than OCI_CONTINUE, the last returned return code becomes the return code for the call. At the this point, the value of *errnop returned is set in the error handle, or in the environment handle if the error information is returned in the environment handle because of the absence of the error handle for certain OCI calls such as OCIHandleAlloc().

For replacement callbacks, the returnCode is the non-OCI_CONTINUE return code from the previous callback or OCI call and *errnop is the value of the error number being returned in the error handle. This allows the subsequent callback to change the return code or error information if needed.

The processing of replacement callbacks is different in that if it returns anything other than OCI_CONTINUE, then subsequent replacement callbacks and OCI code is bypassed and processing jumps to the exit callbacks.

Note that if the replacement callbacks return OCI_CONTINUE to allow processing of OCI code, then the return code from entry callbacks is ignored.

All the original parameters of the OCI call are passed to the callback as variable parameters and the callback must retrieve them using the va_arg macros. The callback demonstration programs provide examples. See Appendix B, "OCI Demonstration Programs" for a list of available demos.

A null value can be registered to de-register a callback. That is, if the value of the callback (OCIUserCallback) is NULL in the OCIUserCallbackRegister() call, then the user callback is de-registered.

When using the thread-safe mode, the OCI program acquires all mutexes before calling the user callbacks.

UserCallback Control Flow

This pseudocode describes the overall processing of a typical OCI call:

 Acquire mutexes on handles;
 retCode = OCI_SUCCESS;
 errno = 0;
 for all ENTRY callbacks do
     EntryretCode = (*entryCallback)(..., retcode, &errno, ...);
     if (retCode != OCI_CONTINUE)
         set errno in error handle or environment handle;
         retCode = EntryretCode;
  for all REPLACEMENT callbacks do
   retCode = (*replacementCallback) (..., retcode, &errno, ...);
   if (retCode != OCI_CONTINUE)
       set errno in error handle or environment handle
       goto executeEXITCallback;

   retCode = return code for XyzCall; /* normal processing of OCI call */

   errno = error number from error handle or env handle;

   for all EXIT callbacks do
       exitRetCode = (*exitCallback)(..., retCode, &errno,...);
       if (exitRetCode != OCI_CONTINUE)
           set errno in error handle or environment handle;
           retCode = exitRetCode;
    release mutexes;
    return retCode

UserCallback for OCIErrorGet()

If the callbacks are a total replacement of the OCI code, then they would usually maintain their own error information in the call context and would use that to return error information in bufp and errnop parameters of the replacement callback of the OCIErrorGet() call.

If on the other hand, the callbacks are either partially overriding OCI code, or just doing some other post processing, then they can use the exit callback to modify the error text and errnop parameters of the OCIErrorGet() by their own error message and error number. Note that the *errnop passed into the exit callback is the error number in the error or the environment handle.

Errors from Entry Callbacks

If an entry callback wants to return an error to the caller of the OCI call, then it must register a replacement or exit callback. This is because if the OCI code is executed, then the error code from the entry callback is ignored. Therefore the entry callback should pass the error to the replacement or exit callback through its own context.

Dynamic Callback Registrations

Because user callbacks are expected to be used for monitoring OCI behavior or to access other data sources, it is desirable that the registration of the callbacks be done transparently and non-intrusively. This is accomplished by loading user-created dynamically linked libraries at OCI initialization time. These dynamically linked libraries are called packages. The user-created packages register the user callbacks for the selected OCI calls. These callbacks can further register or de-register user callbacks as needed when receiving control at runtime.

A makefile ( on Solaris) is provided with the OCI demonstration programs to create the package. The exact naming and location of this package is operating system dependent. The source code for the package must provide code for special callbacks that are called at OCI initialization and environment creation times.

The loading of the package is controlled by setting an operating system environment variable, ORA_OCI_UCBPKG. This variable names the packages in a generic way. The packages must be located in the $ORACLE_HOME/lib directory.

Loading Multiple Packages

The ORA_OCI_UCBPKG variable can contain a semicolon separated list of package names. The packages are loaded in the order they are specified in the list.

For example, previously one would specify the package as:

setenv ORA_OCI_UCBPKG mypkg

Now, you can still specify the package as above, but in addition multiple packages can be specified as:

setenv ORA_OCI_UCBPKG "mypkg;yourpkg;oraclepkg;sunpkg;msoftpkg"

All these packages are loaded in order. That is, mypkg is loaded first and msoftpkg is loaded last.

A maximum of five packages can be specified.

Package Format

Previously a package had to specify the source code for the OCIEnvCallback() function. Now the OCIEnvCallback() function is obsolete. Instead, the package source must provide two functions. The first function has to be named as packagename suffixed with the word Init. For example, if the package is named foo, then the source file (e.g., but not necessarily foo.c) should contain a fooInit() function with a call to OCISharedLibInit() function specified exactly as:

sword fooInit(metaCtx, libCtx, argfmt, argc, argv)
      dvoid *         metaCtx;         /* The metacontext */
      dvoid *         libCtx;          /* The context for this package. */
      ub4             argfmt;          /* package argument format */
      sword           argc;            /* package arg count*/
      dvoid *         argv[];          /* package arguments */
  return  (OCISharedLibInit(metaCtx, libCtx, argfmt, argc, argv,

The last parameter of the OCISharedLibInit() function, fooEnvCallback(), in this case, is the name of the second function. It can be named anything, but by convention it can be named packagename suffixed with the word EnvCallback.

This function is a replacement for OCIEnvCallback(). Now all the dynamic user callbacks must be registered in this function. The function must be of type OCIEnvCallbackType, which is specified as:

typedef sword (*OCIEnvCallbackType)(OCIEnv *env, ub4 mode,
                                    size_t xtramem_sz, dvoid *usrmemp,
                                    OCIUcb *ucbDesc);

When an environment handle is created, then this callback function is called at the very end. The env parameter is the newly created environment handle.

The mode, xtramem_sz, and usrmemp are the parameters passed to the OCIEnvInit() or OCIEnvCreate() call. The last parameter, ucbDesc, is a descriptor that is passed to the package. The package uses this descriptor to register the user callbacks as described later.

A sample ociucb.c file is provided in the demo directory. The makefile is also provided (on Solaris) in the demo directory to create the package. Please note that this may be different on other platforms. The demo directory also contains full user callback demo programs (cdemoucb.c, cdemoucbl.c,) illustrating this.

User Callback Chaining

User callbacks can both be registered statically in the application itself or dynamically at runtime in the DLLs. A mechanism is needed to allow the application to override a previously registered callback and then later invoke the overridden one in the newly registered callback to preserve the behavior intended by the dynamic registrations. This can result in chaining of user callbacks.

For this purpose, the OCIUserCallbackGet() function is provided to find out which function and context is registered for an OCI call. See OCIUserCallbackGet() for the syntax of this call.

Accessing Other Data Sources Through OCI

Because Oracle is the predominant database accessed, applications can take advantage of the OCI interface to access non-Oracle data by using the user callbacks to access them. This allows an application written in OCI to access Oracle data without any performance penalty. To access non-Oracle data sources, drivers can be written that would access the non-Oracle data in user callbacks. Because OCI provides a very rich interface, there is usually a straightforward mapping of OCI calls to most data sources. This solution is better than writing applications for other middle layers such as ODBC which introduce performance penalty for all data sources. Using OCI would not incur any penalty for the common case of accessing Oracle data sources, and would incur the same penalty that ODBC does for non-Oracle data sources.

Restrictions on Callback Functions

There are certain restrictions on the usage of callback functions, including OCIEnvCallback():

Example of OCI Callbacks

For example, lets suppose that there are five packages each registering entry, replacement, and exit callbacks for OCIStmtPrepare call. That is, the ORA_OCI_UCBPKG variable is set as:

setenv ORA_OCI_UCBPKG "pkg1;pkg2;pkg3;pkg4;pkg5" 

In each package pkgN (where N can be 1 through 5), the pkgNInit() and PkgNEnvCallback() functions are specified as:

pkgNInit(metaCtx, libCtx, argfmt, argc, argv) 
  return OCISharedLibInit(metaCtx, libCtx, argfmt, argc, argv, pkgNEnvCallback); 

The pkgNEnvCallback() function registers the entry, replacement, and exit callbacks as:

 pkgNEnvCallback(env, mode, xtramemsz, usrmemp, ucbDesc) 
  OCIHandleAlloc((dvoid *)env, (dvoid **)&errh, OCI_HTYPE_ERROR, (size_t) 0,
        (dvoid **)NULL); 
  OCIUserCallbackRegister(env, OCI_HTYPE_ENV, errh, pkgN_entry_callback_fn,
  OCIUserCallbackRegister(env, OCI_HTYPE_ENV, errh, pkgN_replace_callback_fn,
  OCIUserCallbackRegister(env, OCI_HTYPE_ENV, errh, pkgN_exit_callback_fn,
  return OCI_CONTINUE; 

Finally, in the source code for the application, user callbacks can be registered with the NULL ucbDesc as:

  OCIUserCallbackRegister(env, OCI_HTYPE_ENV, errh, static_entry_callback_fn,
  OCIUserCallbackRegister(env, OCI_HTYPE_ENV, errh, static_replace_callback_fn,
  OCIUserCallbackRegister(env, OCI_HTYPE_ENV, errh, static_exit_callback_fn,

When the OCIStmtPrepare() call is executed, the callbacks are called in the following order:

      OCI code for OCIStmtPrepare call 
pkg1_exit_callback_fn() 9


Note: The exit callbacks are called in the reverse order of the entry and replacement callbacks.

The entry and exit callbacks can return any return code and the processing would continue to the next callback. However, if the replacement callback returns anything other than OCI_CONTINUE, then the next callback (or OCI code if it is the last replacement callback) in the chain is bypassed and processing jumps to the exit callback. For example, if pkg3_replace_callback_fn() returned OCI_SUCCESS, then pkg4_replace_callback_fn(), pkg5_replace_callback_fn(), and the OCI processing for the OCIStmtPrepare call would be bypassed. Instead pkg5_exit_callback_fn() would be executed next.

OCI Callbacks From External Procedures

There are several OCI functions that can be used as callbacks from external procedures. These functions are listed in Chapter 18, "OCI Cartridge Functions". For information about writing C subroutines that can be called from PL/SQL code, including a list of which OCI calls can be used, and some example code, refer to the Oracle8i Application Developer's Guide - Fundamentals.

Application Failover Callbacks

Application failover callbacks can be used in the event of the failure of one database instance, and failover to another instance. Because of the delay which can occur during failover, the application developer may want to inform the user that failover is in progress, and request that the user stand by. Additionally, the session on the initial instance may have received some ALTER SESSION commands. These will not be automatically replayed on the second instance. Consequently, the developer may wish to replay these ALTER SESSION commands on the second instance.

Failover Callback Overview

To address the problems described above, the application developer can register a failover callback function. In the event of failover, the callback function is invoked several times during the course of reestablishing the user's session.

The first call to the callback function occurs when Oracle first detects an instance connection loss. This callback is intended to allow the application to inform the user of an upcoming delay. If failover is successful, a second call to the callback function occurs when the connection is reestablished and usable. At this time the client may wish to replay ALTER SESSION commands and inform the user that failover has happened. If failover is unsuccessful, then the callback is called to inform the application that failover will not take place. Additionally, the callback is called each time a user handle besides the primary handle is reauthenticated on the new connection. Since each user handle represents a server-side session, the client may wish to replay ALTER SESSION commands for that session.

An initial attempt at failover may not always successful. The OCI provides a mechanism for retrying failover after an unsuccessful attempt. See "Handling OCI_FO_ERROR" for more information about this scenario.

Failover Callback Structure and Parameters

The basic structure of a user-defined application failover callback function is as follows:

sb4 appfocallback_fn ( dvoid      * svchp, 
                       dvoid      * envhp, 
                       dvoid      * fo_ctx, 
                       ub4        fo_type, 
                       ub4        fo_event );

An example is provided in the section "Failover Callback Example" for the following parameters:


The first parameter, svchp, is the service context handle. It is of type dvoid *.


The second parameter, envhp, is the OCI environment handle. It is of type dvoid *.


The third parameter, fo_ctx, is a client context. It is a pointer to memory specified by the client. In this area the client can keep any necessary state or context. It is passed as a dvoid *.


The fourth parameter, fo_type, is the failover type. This lets the callback know what type of failover the client has requested. The usual values are:


The last parameter is the failover event. This indicates to the callback why it is being called. It has several possible values:

Failover Callback Registration

For the failover callback to be used, it must be registered on the server context handle. This registration is done by creating a callback definition structure and setting the OCI_ATTR_FOCBK attribute of the server handle to this structure.

The callback definition structure must be of type OCIFocbkStruct. It has two fields: callback_function, which contains the address of the function to call, and fo_ctx which contains the address of the client context.

An example of callback registration is included as part of the example in the next section.

Failover Callback Example

The following code shows an example of a simple user-defined callback function definition and registration.

Part 1, Failover Callback Definition

sb4  callback_fn(svchp, envhp, fo_ctx, fo_type, fo_event )
dvoid * svchp;
dvoid * envhp;
dvoid *fo_ctx;
ub4 fo_type;
ub4 fo_event;
switch (fo_event) 
   case OCI_FO_BEGIN:
     printf(" Failing Over ... Please stand by \n");
     printf(" Failover type was found to be %s \n",
                     ((fo_type==OCI_FO_SESSION) ? "SESSION" 
                     :(fo_type==OCI_FO_SELECT) ? "SELECT"
                     : "UNKNOWN!")); 
     printf(" Failover Context is :%s\n", 
                    (fo_ctx?(char *)fo_ctx:"NULL POINTER!"));
   case OCI_FO_ABORT:
     printf(" Failover aborted. Failover will not take place.\n");
   case    OCI_FO_END:
       printf(" Failover ended ...resuming services\n");
   case OCI_FO_REAUTH:
       printf(" Failed over user. Resuming services\n");
     printf("Bad Failover Event: %d.\n",  fo_event);
   return 0;

Part 2, Failover Callback Registration

int register_callback(svrh, errh)
dvoid *svrh; /* the server handle */
OCIError *errh; /* the error handle */
  OCIFocbkStruct failover;                 /*  failover callback structure */
  /* allocate memory for context */
  if (!(failover.fo_ctx = (dvoid *)malloc(strlen("my context."))))
  /* initialize the context. */
  strcpy((char *)failover.context_function, "my context.");
  failover.callback_function = &callback_fn;
  /* do the registration */
  if (OCIAttrSet(srvh, (ub4) OCI_HTYPE_SERVER,
                (dvoid *) &failover, (ub4) 0,
                (ub4) OCI_ATTR_FOCBK, errh)  != OCI_SUCCESS)
  /* successful conclusion */
  return (0);


A failover attempt is not always successful. If the attempt fails, the callback function receives a value of OCI_FO_ABORT or OCI_FO_ERROR in the fo_event parameter. A value of OCI_FO_ABORT indicates that failover was unsuccessful, and no further failover attempts are possible. OCI_FO_ERROR, on the other hand, provides the callback function with the opportunity to handle the error in some way. For example, the callback may choose to wait a specified period of time and then indicate to the OCI library that it should reattempt failover.

Consider the following timeline of events:

Time  Event 


Database crashes (crash lasts until T5). 


Failover triggered by user activity. 


User attempts to reconnect; attempt fails. 


Failover callback invoked with OCI_FO_ERROR. 


Failover callback enters predetermined sleep period. 


Database comes back up again. 


Failover callback triggers new failover attempt; it is successful. 


User successfully reconnects 

The callback function triggers the new failover attempt by returning a value of OCI_FO_RETRY from the function.

The following example code shows a callback function which might be used to implement the failover strategy similar to the scenario described above. In this case the failover callback enters a loop in which it sleeps and then reattempts failover until it is successful:

/* the user defined failover callback  */
sb4  callback_fn(svchp, envhp, fo_ctx, fo_type, fo_event )
dvoid * svchp;
dvoid * envhp;
dvoid *fo_ctx;
ub4 fo_type;
ub4 fo_event;
   OCIError *errhp;
   OCIHandleAlloc(envhp, (dvoid **)&errhp, (ub4) OCI_HTYPE_ERROR,
              (size_t) 0, (dvoid **) 0);
   switch (fo_event) 
   case OCI_FO_BEGIN:
     printf(" Failing Over ... Please stand by \n");
     printf(" Failover type was found to be %s \n",
            ((fo_type==OCI_FO_NONE) ? "NONE"
             :(fo_type==OCI_FO_SESSION) ? "SESSION" 
             :(fo_type==OCI_FO_SELECT) ? "SELECT"
             :(fo_type==OCI_FO_TXNAL) ? "TRANSACTION"
             : "UNKNOWN!")); 
     printf(" Failover Context is :%s\n", 
            (fo_ctx?(char *)fo_ctx:"NULL POINTER!"));
   case OCI_FO_ABORT:
     printf(" Failover aborted. Failover will not take place.\n");
   case    OCI_FO_END:
       printf("\n Failover ended ...resuming services\n");
   case OCI_FO_REAUTH:
       printf(" Failed over user. Resuming services\n");
   case OCI_FO_ERROR:
     /* all invocations of this can only generate one line. The newline
      * will be put at fo_end time.
     printf(" Failover error gotten. Sleeping...");
     printf("Retrying. ");
     return (OCI_FO_RETRY);
     printf("Bad Failover Event: %d.\n",  fo_event);
   return 0;

The following is sample output from a program containing this failover callback function:

executing select...
7369    SMITH    CLERK
 Failing Over ... Please stand by 
 Failover type was found to be SELECT 
 Failover Context is :My context.
 Failover error gotten. Sleeping...Retrying.  Failover error gotten. 
Sleeping...Retrying.  Failover error gotten. Sleeping...Retrying.  Failover 
error gotten. Sleeping...Retrying.  Failover error gotten. Sleeping...Retrying.  
Failover error gotten. Sleeping...Retrying.  Failover error gotten. 
Sleeping...Retrying.  Failover error gotten. Sleeping...Retrying.  Failover 
error gotten. Sleeping...Retrying.  Failover error gotten. Sleeping...Retrying. 
 Failover ended ...resuming services
7521    WARD    SALESMAN
7566    JONES    MANAGER
7698    BLAKE    MANAGER
7782    CLARK    MANAGER
7788    SCOTT    ANALYST
7876    ADAMS    CLERK
7900    JAMES    CLERK
7902    FORD    ANALYST

OCI and Advanced Queuing

The OCI provides an interface to Oracle's Advanced Queuing feature. Oracle AQ provides message queuing as an integrated part of the Oracle server. Oracle AQ provides this functionality by integrating the queuing system with the database, thereby creating a message-enabled database. By providing an integrated solution Oracle AQ frees application developers to devote their efforts to their specific business logic rather than having to construct a messaging infrastructure.

OCI Advanced Queuing Functions

The OCI library includes several functions related to Advanced Queuing:

Chapter 15, "OCI Relational Functions", contains complete descriptions of these functions and their parameters.

OCI Advanced Queuing Descriptors

The following descriptors are used by OCI AQ operations:

You can allocate these descriptors with respect to the service handle using the standard OCIDescriptorAlloc() call. The following code shows examples of this:

OCIDescriptorAlloc(svch, &enqueue_options, OCI_DTYPE_AQENQ_OPTIONS, 0, 0 ); 
OCIDescriptorAlloc(svch, &dequeue_options, OCI_DTYPE_AQDEQ_OPTIONS, 0, 0 ); 
OCIDescriptorAlloc(svch, &message_properties, OCI_DTYPE_AQMSG_PROPERTIES, 0, 0);
OCIDescriptorAlloc(svch, &agent, OCI_DTYPE_AQAGENT, 0, 0 ); 

Each descriptor has a variety of attributes which can be set and/or read. These attributes are described in more detail in "Advanced Queueing Descriptor Attributes".

Advanced Queuing in OCI vs. PL/SQL

The following tables compare functions, parameters, and options for OCI AQ functions and descriptors, and PL/SQL AQ functions in the dbms_aq package.

PL/SQL Function  OCI Function 







DBMS_AQ.ENQUEUE Parameter  OCIAQEnq() Parameter 











Note: OCIAQEnq() also requires the following additional parameters: svch, errh, payload_tdo, payload_ind, and flags 

DBMS_AQ.DEQUEUE Parameter  OCIAQDeq() Parameter 











Note: OCIAQDeq() also requires the following additional parameters: svch, errh, queue_name, dequeue_options, message_properties, payload_tdo, payload, payload_ind, and flags 

DBMS_AQ.LISTEN Parameter  OCIAQListen() Parameter 







Note: OCIAQListen() also requires the following additional parameters: svchp, errhp, agent_list, num_agents, wait, agent, and flags 

PL/SQL Agent Parameter  OCIAQAgent Attribute 







PL/SQL Message Property  OCIAQMsgProperties Attribute 























PL/SQL Enqueue Option  OCIAQEnqOptions Attribute 







PL/SQL Dequeue Option  OCIAQDeqOptions Attribute 















Publish-Subscribe Notification

The publish-subscribe notification feature allows an OCI application to receive client notifications. Figure 9-1, "Publish-Subscribe Model" illustrates the process. An OCI application can:

Registered clients are notified asynchronously when events are triggered or on an explicit AQ enqueue. Clients do not need to be connected to a database.

For information on Advanced Queuing, see "OCI and Advanced Queuing".

Figure 9-1 Publish-Subscribe Model

Publish-Subscribe Functions

The following steps are required in an OCI application to register and receive notifications for events. It is assumed that the appropriate event trigger or AQ queue has been set up. Also, the initialization parameter COMPATIBLE must be set to 8.1 or higher.

Detailed descriptions of the functions noted can be found in Chapter 15, "OCI Relational Functions". For examples of the use of these functions in an application, see "Publish-Subscribe Example".

  1. Execute OCIInitialize() with OCI_EVENTS mode to specify that the application is interested in registering for and receiving notifications. This starts a dedicated listening thread for notifications on the client.

  2. Execute OCIHandleAlloc() with handle type OCI_HTYPE_SUBSCRIPTION to allocate a subscription handle.

  3. Execute OCIAttrSet() to set the subscription handle attributes for:

    • OCI_ATTR_SUBSCR_NAME - subscription name

    • OCI_ATTR_SUBSCR_NAMESPACE - subscription namespace

    • OCI_ATTR_SUBSCR_CALLBACK - notification callback

    • OCI_ATTR_SUBSCR_CTX - callback context

    • OCI_ATTR_SUBSCR_PAYLOAD - payload buffer for posting

    All these attributes, except OCI_ATTR_SUBSCR_PAYLOAD, must be set before registering a subscription. OCI_ATTR_SUBSCR_PAYLOAD is required before posting to a subscription. For information on these attributes, see "Subscription Handle Attributes".

  4. Define the callback routine to be used with the subscription handle. For information, see "Notification Callback".

  5. Execute OCISubscriptionRegister() to register with the subscription(s). This call can register interest in several subscriptions at the same time.

The following functions are used to manage publish-subscribe notification. Detailed descriptions of each function can be found in Chapter 15, "OCI Relational Functions".

Table 9-1 Publish-Subscribe Functions  
Function  Purpose 


Disables a subscription. 


Enables a subscription. 


Posts a subscription. 


Registers a subscription. 


Unregisters a subscription. 

Notification Callback

The client needs to register a notification callback that gets invoked when there is some activity on the subscription for which interest has been registered. In the AQ namespace, for instance, this occurs when a message of interest is enqueued.

This callback is typically set via the OCI_ATTR_SUBSCR_CALLBACK attribute of the subscription handle. For information, see "Subscription Handle Attributes" .

The callback must return a value of OCI_CONTINUE and adhere to the following specification:

typedef ub4 (*OCISubscriptionNotify) ( dvoid *pCtx,
                                       OCISubscription *pSubscrHp,
                                       dvoid *pPayload,
                                       ub4 *iPayloadLen,
                                       dvoid *pDescriptor,
                                       ub4 iMode);

The parameters are described as follows:

pCtx (IN)

A user-defined context specified when the callback was registered.

pSubscrHp (IN)

The subscription handle specified when the callback was registered.

pPayload (IN)

The payload for this notification. For this release, only ub1 * (a sequence of bytes) for the payload is supported.

iPayloadLen (IN)

The length of the payload for this notification.

pDescriptor (IN)

The namespace-specific descriptor. Namespace-specific parameters can be extracted from this descriptor. The structure of this descriptor is opaque to the user and its type is dependent on the namespace.

The attributes of the descriptor are namespace-specific. For Advanced Queuing, the descriptor is OCI_DTYPE_AQNFY. The attributes of this descriptor are:

For more information about OCI and Advanced Queueing, refer to "OCI and Advanced Queuing".

iMode (IN)

Call-specific mode. Valid value:

Publish-Subscribe Example

This example shows how system events, client notification, and Advanced Queuing work together to implement publish/subscription notification.

The following PL/SQL code creates all objects necessary to support a publish-subscribe mechanism under the user schema, pubsub. In this code, the Agent snoop subscribes to messages that are published at logon events. Note that the user pubsub needs AQ_ADMINISTRATOR_ROLE and AQ_USER_ROLE privileges to use Advance Queuing functionality. Also, the initialization parameter _SYSTEM_TRIG_ENABLED must be set to TRUE (default) to enable triggers for system events.

Rem ------------------------------------------------------
REM create queue table for persistent multiple consumers
Rem ------------------------------------------------------
connect pubsub/pubsub;
Rem  Create or replace a queue table
  COMPATIBLE => '8.1.5');
Rem ------------------------------------------------------
Rem  Create a persistent queue for publishing messages
Rem ------------------------------------------------------
Rem  Create a queue for logon events
  COMMENT=>'Q for error triggers');
Rem ------------------------------------------------------
Rem  Start the queue
Rem ------------------------------------------------------
Rem ------------------------------------------------------
Rem  define new_enqueue for convenience
Rem ------------------------------------------------------
create or replace procedure new_enqueue(queue_name  in varchar2,
                                        payload  in raw ,
correlation in varchar2 := NULL,
exception_queue in varchar2 := NULL)
  enq_ct     dbms_aq.enqueue_options_t;
  msg_prop   dbms_aq.message_properties_t;
  enq_msgid  raw(16);
  userdata   raw(1000);
  msg_prop.exception_queue := exception_queue;
  msg_prop.correlation := correlation;
  userdata := payload;
  DBMS_AQ.ENQUEUE(queue_name,enq_ct, msg_prop,userdata,enq_msgid);
Rem ------------------------------------------------------
Rem  add subscriber with rule based on current user name, 
Rem  using correlation_id
Rem ------------------------------------------------------
  subscriber :=$_agent('SNOOP', null, null);
  dbms_aqadm.add_subscriber(queue_name => 'pubsub.logon',
                            subscriber => subscriber,
                            rule => 'CORRID = ''SCOTT'' ');
Rem ------------------------------------------------------
Rem  create a trigger on logon on database
Rem ------------------------------------------------------
Rem  create trigger on after logon
create or replace trigger systrig2
     new_enqueue('pubsub.logon', hextoraw('9999'), dbms_standard.login_user);

After the subscriptions are created, the client needs to register for notification using callback functions. The following sample code performs necessary steps for registration. The initial steps of allocating and initializing session handles are omitted here for sake of clarity.

ub4 namespace = OCI_SUBSCR_NAMESPACE_AQ;
/* callback function for notification of logon of user 'scott' on database */
ub4 notifySnoop(ctx, subscrhp, pay, payl, desc, mode)
    dvoid *ctx;
    OCISubscription *subscrhp;
    dvoid *pay;
    ub4 payl;
    dvoid *desc;
    ub4 mode;
    printf("Notification : User Scott Logged on\n");
int main()
    OCISession *authp = (OCISession *) 0;
    OCISubscription *subscrhpSnoop = (OCISubscription *)0;

Initialize OCI Process/Environment
Initialize Server Contexts
Connect to Server
Set Service Context
    /* Registration Code Begins */
/* Each call to initSubscriptionHn allocates 
           and Initialises a Registration Handle */
    initSubscriptionHn(    &subscrhpSnoop,    /* subscription handle */
        "PUBSUB.SNOOP:ADMIN", /* subscription name */ 
/* <queue_name>:<agent_name> */
        (dvoid*)notifySnoop); /* callback function */

The Client Process does not need a live Session for Callbacks
End Session and Detach from Server
    OCISessionEnd ( svchp,  errhp, authp, (ub4) OCI_DEFAULT);
    /* detach from server */
    OCIServerDetach( srvhp, errhp, OCI_DEFAULT);
    while (1)     /* wait for callback */
void initSubscriptionHn (subscrhp,
OCISubscription **subscrhp;
  char* subscriptionName;
  dvoid * func;
    /* allocate subscription handle */
    (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **)subscrhp, 
        (size_t) 0, (dvoid **) 0);

    /* set subscription name in handle */
    (void) OCIAttrSet((dvoid *) *subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
        (dvoid *) subscriptionName, 
        (ub4) strlen((char *)subscriptionName),
        (ub4) OCI_ATTR_SUBSCR_NAME, errhp);

    /* set callback function in handle */
    (void) OCIAttrSet((dvoid *) *subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
        (dvoid *) func, (ub4) 0,
        (ub4) OCI_ATTR_SUBSCR_CALLBACK, errhp);
    (void) OCIAttrSet((dvoid *) *subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
        (dvoid *) 0, (ub4) 0,
        (ub4) OCI_ATTR_SUBSCR_CTX, errhp);

    /* set namespace in handle */
    (void) OCIAttrSet((dvoid *) *subscrhp, (ub4) OCI_HTYPE_SUBSCRIPTION,
        (dvoid *) &namespace, (ub4) 0,
        (ub4) OCI_ATTR_SUBSCR_NAMESPACE, errhp);
    checkerr(errhp, OCISubscriptionRegister(svchp, subscrhp, 1, errhp,

If user SCOTT logs on to the database, the client is notified and the call back function notifySnoop is called.

Direct Path Loading

The direct path load interface allows an OCI application to access the direct path load engine of the Oracle database server to perform the functions of the Oracle SQL*Loader utility. This functionality provides the ability to load data from external files into Oracle database objects, either a table or a partition of a partitioned table.

Figure 9-2 Direct Path Loading

The OCI direct path load interface has the ability to load multiple rows by loading a direct path stream which contains data for multiple rows.

To use the direct path API, the client application performs the following steps:

  1. Perform the OCI initialization.

  2. Allocate a direct path context handle and set the attributes.

  3. Supply the name of the object (table, partition, or sub-partition) to be loaded.

  4. Describe the external data types of the columns of the object(s).

  5. Prepare the direct path interface.

  6. Allocate one or more column arrays.

  7. Allocate one or more direct path streams.

  8. Set entries in the column array to point to the input data value for each column.

  9. Convert a column array to a direct path stream format.

  10. Either load the direct path stream, or save the direct path stream to a file to be loaded at a later time.

  11. Retrieve any errors which may have occurred.

  12. Invoke the direct path finishing function.

  13. Free handles and data structures.

  14. Disconnect from the server.

A direct load operation requires that the object being loaded is locked to prevent DML on the object. Note that queries are lock free and are allowed while the object is being loaded. The mode of the DML lock, and which DML locks are obtained depend upon the specification of the OCI_DIRPATH_PARALLEL_LOAD option, and if a partition or sub-partition load is being done as opposed to an entire table load. For more information on OCI_DIRPATH_PARALLEL_LOAD, see OCIDirPathPrepare().

Limitations and Restrictions

The direct path load interface has the following limitations which are the same as SQL*Loader:

Datatypes Supported

The following external datatypes are valid for columns in a direct path load operation: SQLT_CHR, SQLT_DAT, SQLT_INT, SQLT_UIN, SQLT_FLT, SQLT_PDN, SQLT_BIN, or SQLT_NUM. For information on setting or retrieving the datatype of a column, see OCI_ATTR_DATA_TYPE. For information on datatypes, see Chapter 3, "Datatypes".

Direct Path Handles

A direct path load corresponds to a direct path array insert operation. The direct path load interface uses the following handles to keep track of the objects loaded and the specification of the data operated on:

For information about the attributes of direct path load handles, refer to "Direct Path Loading Handle Attributes". For information about column parameter attributes, see "Direct Path Column Parameter Attributes".

Direct Path Context

This handle needs to be allocated for each object, either a table or a partition of a partitioned table, being loaded. Because a OCIDirPathCtx handle is the parent handle of the OCIDirPathColArray and OCIDirPathStream handles, freeing a OCIDirPathCtx handle frees its child handles also. A direct path context is allocated with OCIHandleAlloc().

OCIEnv *envp;
OCIDirPathCtx *dpctx;
sword error;
error = OCIHandleAlloc((dvoid *)envp, (dvoid **)&dpctx,
                    OCI_HTYPE_DIRPATH_CTX, 0,(dvoid **)0);

Note that the parent handle of a direct path context is always the environment handle. A direct path context is freed with OCIHandleFree().

error = OCIHandleFree(dpctx, OCI_HTYPE_DIRPATH_CTX);

Direct Path Column Array

This handle is used to present an array of rows to the direct path interface. A row is represented by three arrays: column values, column lengths, and column flags. Methods on a column array include: allocate the array handle and set/get values corresponding to an array entry.

A direct path column array handle is allocated with OCIHandleAlloc(). The following code fragment shows explicit allocation of the direct path column array handle:

OCIDirPathCtx *dpctx;
OCIDirPathColArray *dpca;
sword error;
error = OCIHandleAlloc((dvoid *)dpctx, (dvoid **)&dpca,
               OCI_HTYPE_DIRPATH_COLUMN_ARRAY, 0, (dvoid **)0);

A direct path column array is freed with OCIHandleFree().


Freeing a OCIDirPathColArray handle also frees the column array associated with the handle.

Direct Path Stream

This handle is used by the conversion operation, OCIDirPathColArrayToStream(), and by the load operation, OCIDirPathLoadStream().

Direct path stream handles is allocated by the client with OCIHandleAlloc(). The structure of a OCIDirPathStream handle can be thought of as a pair in the form (buffer, buffer length).

A direct path stream is a linear representation of Oracle table data. The conversion operations always append to the end of the stream. Load operations always start from the beginning of the stream. After a stream is completely loaded, the stream must be reset by calling OCIDirPathStreamReset().

The following example shows a direct path stream handle allocated with OCIHandleAlloc(). The parent handle is always a OCIDirPathCtx handle:

OCIDirPathCtx *dpctx;
OCIDirPathStream *dpstr;
sword error;
error = OCIHandleAlloc((dvoid *)dpctx, (dvoid **)&dpstr,
               OCI_HTYPE_DIRPATH_STREAM, 0,(dvoid **)0);

A direct path stream handle is freed via OCIHandleFree().

error = OCIHandleFree(dpstr, OCI_HTYPE_DIRPATH_STREAM);

Note that freeing the direct path stream handle will also free any stream buffer allocated by OCIDirPathStreamAlloc().

Direct Path Interface Functions

The functions listed in this section are used with the direct path load interface. Detailed descriptions of each function can be found in Chapter 15, "OCI Relational Functions".

Operations on the direct path context are performed by the functions in Table 9-2, "Direct Path Context Functions".

Table 9-2 Direct Path Context Functions

Function  Purpose 


Aborts a direct path operation 


Commits the loaded data 


Prepares direct path interface to convert or load rows 


Loads data that has been converted to direct path stream format 

Operations on the direct path column array are performed by the functions in Table 9-3, "Direct Path Column Array Functions".

Table 9-3 Direct Path Column Array Functions

Function  Purpose 


Gets a specified entry in a column array 


Sets a specified entry in a column array to a specific value 


Gets the base row pointers for a specified row number 


Resets the row array state 


Converts from a column array format to a direct path stream format 

Operations on the direct path stream are performed by the functions inTable 9-4, "Direct Path Stream Functions".

Table 9-4 Direct Path Stream Functions

Function  Purpose 


Resets the direct stream state 

Direct Path Load Example

The following sample code illustrates the use of several of the OCI direct path interfaces. It is not a complete code example.

The following data structure is used in the example.

/* load control structure */
struct loadctl
  ub4                 nrow_ctl;            /* number of rows in column array */
  ub2                 ncol_ctl;         /* number of columns in column array */
  OCIEnv             *envhp_ctl;                       /* environment handle */
  OCIServer          *srvhp_ctl;                            /* server handle */
  OCIError           *errhp_ctl;                             /* error handle */
  OCIError           *errhp2_ctl;                    /* another error handle */
  OCISvcCtx          *svchp_ctl;                          /* service context */
  OCISession         *authp_ctl;                   /* authentication context */
  OCIParam           *colLstDesc_ctl;        /* column list parameter handle */
  OCIDirPathCtx      *dpctx_ctl;                      /* direct path context */
  OCIDirPathColArray *dpca_ctl;           /* direct path column array handle */
  OCIDirPathStream   *dpstr_ctl;                /* direct path stream handle */
  ub1                *buf_ctl;    /* pre-alloc'd buffer for out-of-line data */
  ub4                 bufsz_ctl;                 /* size of buf_ctl in bytes */
  ub4                 bufoff_ctl; /* offset into buf_ctl which is not in use */
  ub4                *otor_ctl;                  /* Offset to Recnum mapping */
  ub1                *inbuf_ctl;                 /* buffer for input records */
  struct pctx         pctx_ctl;                     /* partial field context */

The header file cdemodp.h from the demo directory defines several structs:

#ifndef cdemodp_ORACLE
# define cdemodp_ORACLE

# include <oratypes.h>

# ifndef externdef
#  define externdef
# endif

/* External column attributes */
struct col
  text *name_col;                                             /* column name */
  ub2   id_col;                                            /* column load id */
  ub2   exttyp_col;                                         /* external type */
  text *datemask_col;                             /* datemask, if applicable */
  ub1   prec_col;                                /* precision, if applicable */
  sb1   scale_col;                                   /* scale, if applicable */
  ub2   csid_col;                                        /* character set id */
  ub1   date_col;            /* is column a chrdate or date? 1=TRUE. 0=FALSE */

/* Input field descriptor
 * For this example (and simplicity),
 * fields are strictly positional.
struct fld
  ub4  begpos_fld;                             /* 1-based beginning position */
  ub4  endpos_fld;                             /* 1-based ending    position */
  ub4  maxlen_fld;                       /* max length for out of line field */
  ub4    flag_fld;
#define FLD_INLINE            0x1
#define FLD_OUTOFLINE         0x2

struct tbl
  text        *owner_tbl;                                     /* table owner */
  text        *name_tbl;                                       /* table name */
  text        *subname_tbl;                        /* subname, if applicable */
  ub2          ncol_tbl;                     /* number of columns in col_tbl */
  text        *dfltdatemask_tbl;            /* table level default date mask */
  struct col  *col_tbl;                                 /* column attributes */
  struct fld  *fld_tbl;                                  /* field descriptor */
  ub1          parallel_tbl;                         /* parallel: 1 for true */
  ub1          nolog_tbl;                          /* no logging: 1 for true */
  ub4          xfrsz_tbl;                   /* transfer buffer size in bytes */

struct sess                        /* options for a direct path load session */
  text        *username_sess;                                        /* user */
  text        *password_sess;                                    /* password */
  text        *inst_sess;                            /* remote instance name */
  text        *outfn_sess;                                /* output filename */
  ub4          maxreclen_sess;          /* max size of input record in bytes */

#endif                                              /* cdemodp_ORACLE */

The init_load function performs a direct path load using the direct path API on the table described by tblp. The loadctl structure given by ctlp has an appropriately initialized environment and service context. A connection has been made to the server.

init_load(ctlp, tblp)
struct loadctl *ctlp;
struct tbl     *tblp;
  struct  col   *colp;
  struct  fld   *fldp;
  sword          ociret;                       /* return code from OCI calls */
  OCIDirPathCtx *dpctx;                               /* direct path context */
  OCIParam      *colDesc;                     /* column parameter descriptor */
  ub1            parmtyp;
  ub1           *timestamp = (ub1 *)0;
  ub4            size;
  ub4            i;
  ub4            pos;

  /* allocate and initialize a direct path context */
  OCI_CHECK(ctlp->envhp_ctl, OCI_HTYPE_ENV, ociret, ctlp,
            OCIHandleAlloc((dvoid *)ctlp->envhp_ctl,
                           (dvoid **)&ctlp->dpctx_ctl,
                           (size_t)0, (dvoid **)0));

  dpctx = ctlp->dpctx_ctl;                                      /* shorthand */

  OCI_CHECK(ctlp->errhp_ctl, OCI_HTYPE_ERROR, ociret, ctlp,
            OCIAttrSet((dvoid *)dpctx, (ub4)OCI_HTYPE_DIRPATH_CTX,
                       (dvoid *)tblp->name_tbl,
                       (ub4)strlen((const char *)tblp->name_tbl),
                       (ub4)OCI_ATTR_NAME, ctlp->errhp_ctl));

Additional attributes, such as OCI_ATTR_SUB_NAME and OCI_ATTR_SCHEMA_NAME, are also set here. After the attributes have been set, prepare the load.

  OCI_CHECK(ctlp->errhp_ctl, OCI_HTYPE_ERROR, ociret, ctlp,
            OCIDirPathPrepare(dpctx, ctlp->svchp_ctl, ctlp->errhp_ctl));

Allocate the column array and stream handles. Note that the direct path context handle is the parent handle for the column array and stream handles. Also note that Oracle errors are returned with the environment handle associated with the direct path context.

   OCI_CHECK(ctlp->envhp_ctl, OCI_HTYPE_ENV, ociret, ctlp,
            OCIHandleAlloc((dvoid *)ctlp->dpctx_ctl, (dvoid **)&ctlp->dpca_ctl,
                           (size_t)0, (dvoid **)0));

  OCI_CHECK(ctlp->envhp_ctl, OCI_HTYPE_ENV, ociret, ctlp,
            OCIHandleAlloc((dvoid *)ctlp->dpctx_ctl,(dvoid **)&ctlp->dpstr_ctl,
                           (size_t)0, (dvoid **)0));

Get number of rows and columns in the column array just allocated.

  OCI_CHECK(ctlp->errhp_ctl, OCI_HTYPE_ERROR, ociret, ctlp,
            OCIAttrGet(ctlp->dpca_ctl, (ub4)OCI_HTYPE_DIRPATH_COLUMN_ARRAY,
                       &ctlp->nrow_ctl, 0, OCI_ATTR_NUM_ROWS,

   OCI_CHECK(ctlp->errhp_ctl, OCI_HTYPE_ERROR, ociret, ctlp,
            OCIAttrGet(ctlp->dpca_ctl, (ub4)OCI_HTYPE_DIRPATH_COLUMN_ARRAY,
                       &ctlp->ncol_ctl, 0, OCI_ATTR_NUM_COLS,

Set the input data fields to their corresponding data columns.

OCIDirPathColArrayEntrySet(ctlp->dpca_ctl, ctlp->errhp_ctl,
                                         rowoff, colp->id_col,
                                         cval, clen, cflg));

Reset column array state in case a previous conversion needed to be continued or a row is expecting more data.

(void) OCIDirPathColArrayReset(ctlp->dpca_ctl, ctlp->errhp_ctl);

Reset the stream state to start a new stream. Otherwise, data in the stream is appended to existing data.

(void) OCIDirPathStreamReset(ctlp->dpstr_ctl, ctlp->errhp_ctl);

After inputting the data, convert the data in the column array to stream format and filter out any bad records.

ocierr = OCIDirPathColArrayToStream(ctlp->dpca_ctl, ctlp->dpctx_ctl,
                                        ctlp->dpstr_ctl, ctlp->errhp_ctl,
                                        rowcnt, startoff);

Load the stream. Note that the position in the stream is maintained internally to the stream handle, along with offset information for the column array which produced the stream. When the conversion to stream format is done, the data is appended to the stream. It is the responsibility of the caller to reset the stream when appropriate. On errors, the position is moved to the next row, or the end of the stream if the error occurs on the last row. The next OCIDirPathLoadStream() call starts on the next row, if any. If a OCIDirPathLoadStream() call is made, and the end of a stream has been reached, OCI_NO_DATA is returned.

ocierr = OCIDirPathLoadStream(ctlp->dpctx_ctl, ctlp->dpstr_ctl,

Finish the direct path load.

OCIDirPathFinish(ctlp->dpctx_ctl, ctlp->errhp_ctl);

Free all the direct path handles allocated. Note that direct path column array and stream handles are freed when the parent direct path context handle is freed. The following code statements that free the direct path column array and stream handles are not necessary but included here as examples.

ociret = OCIHandleFree((dvoid *)ctlp->dpca_ctl,
ociret = OCIHandleFree((dvoid *)ctlp->dpstr_ctl,
ociret = OCIHandleFree((dvoid *)ctlp->dpctx_ctl, 

Go to previous page Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.